sql - 为什么 Postgres 忽略我在条件连接上的所有索引？

Question

我有两个表:campaign 和 stats。 stats 表包含我尝试为每个事件汇总的每日统计信息，没什么特别的。

我索引了所有我能想到的字段，但据我所知没有一个索引被使用。我知道 Postgres 可能会选择不使用索引，但它看起来仍然很可疑，而且查询速度也不是快如闪电。我该如何帮助它？

EXPLAIN ANALYZE SELECT "campaign"."id", "campaign"."name", "campaign"."status", SUM("stats"."impressions") AS "impressions" 
    FROM "campaign" 
    LEFT OUTER JOIN "stats" ON 
        ("stats"."date" >= '2016-03-27'::date) 
        AND ("stats"."date" <= '2016-04-25'::date) 
        AND ("campaign"."id" = "stats"."campaign_id") 
    GROUP BY "campaign"."id" 
    ORDER BY "campaign"."status" ASC, "campaign"."created" DESC 
    LIMIT 25;

查询计划:

Limit  (cost=6445.26..6445.32 rows=25 width=53) (actual time=642.134..642.422 rows=25 loops=1)
   ->  Sort  (cost=6445.26..6446.80 rows=617 width=53) (actual time=642.113..642.209 rows=25 loops=1)
         Sort Key: campaign.status, campaign.created
         Sort Method: top-N heapsort  Memory: 28kB
         ->  HashAggregate  (cost=6421.68..6427.85 rows=617 width=53) (actual time=634.619..637.342 rows=617 loops=1)
               Group Key: campaign.id
               ->  Hash Right Join  (cost=58.88..6269.08 rows=30519 width=53) (actual time=9.986..481.628 rows=31142 loops=1)
                     Hash Cond: (stats.campaign_id = campaign.id)
                     ->  Seq Scan on stats  (cost=0.00..5790.56 rows=30519 width=8) (actual time=0.044..172.346 rows=31027 loops=1)
                           Filter: ((date >= '2016-03-27'::date) AND (date <= '2016-04-25'::date))
                           Rows Removed by Filter: 22299
                     ->  Hash  (cost=51.17..51.17 rows=617 width=49) (actual time=9.325..9.325 rows=617 loops=1)
                           Buckets: 1024  Batches: 1  Memory Usage: 52kB
                           ->  Seq Scan on campaign  (cost=0.00..51.17 rows=617 width=49) (actual time=0.043..4.490 rows=617 loops=1)
 Planning time: 1.778 ms
 Execution time: 643.217 ms

表格:

                                         Table "public.campaign"
        Column        |           Type           |                           Modifiers                           
----------------------+--------------------------+---------------------------------------------------------------
 id                   | integer                  | not null default nextval('campaign_id_seq'::regclass)
 name                 | character varying(255)   | not null
 created              | timestamp with time zone | not null
 status               | character varying(32)    | not null
Indexes:
    "campaign_pkey" PRIMARY KEY, btree (id)
    "campaign_9acb4454" btree (status)
    "campaign_9bea82de" btree (product_id)
    "campaign_created_7aea656cce4d74c_uniq" btree (created)
Foreign-key constraints:
    TABLE "stats" CONSTRAINT "stats_campaign_id_dabb6227_fk_campaign_id" FOREIGN KEY (campaign_id) REFERENCES campaign(id) DEFERRABLE INITIALLY DEFERRED


                                      Table "public.stats"
     Column      |          Type           |                         Modifiers                          
-----------------+-------------------------+------------------------------------------------------------
 id              | integer                 | not null default nextval('stats_id_seq'::regclass)
 date            | date                    | not null
 impressions     | integer                 | not null
 campaign_id     | integer                 | not null
Indexes:
    "stats_pkey" PRIMARY KEY, btree (id)
    "stats_date_1de4ab17_uniq" btree (date)
    "stats_f14acec3" btree (campaign_id)
Foreign-key constraints:
    "stats_campaign_id_dabb6227_fk_campaign_id" FOREIGN KEY (campaign_id) REFERENCES campaign(id) DEFERRABLE INITIALLY DEFERRED

===============

编辑:

如果条件从 JOIN 移到 WHERE 中，则查询计划:

Limit  (cost=10252.48..10252.55 rows=25 width=252) (actual time=921.152..921.423 rows=25 loops=1)
   ->  Sort  (cost=10252.48..10254.03 rows=617 width=252) (actual time=921.142..921.230 rows=25 loops=1)
         Sort Key: campaign.status, campaign.created
         Sort Method: top-N heapsort  Memory: 37kB
         ->  HashAggregate  (cost=10161.03..10235.07 rows=617 width=252) (actual time=910.690..916.553 rows=550 loops=1)
               Group Key: campaign.id
               ->  Hash Right Join  (cost=58.88..6575.05 rows=30519 width=252) (actual time=7.655..708.881 rows=31075 loops=1)
                     Hash Cond: (stats.campaign_id = campaign.id)
                     Filter: ((stats.date IS NULL) OR ((stats.date >= '2016-03-27'::date) AND (stats.date <= '2016-04-25'::date)))
                     Rows Removed by Filter: 22299
                     ->  Seq Scan on stats  (cost=0.00..5526.71 rows=52771 width=56) (actual time=0.009..249.230 rows=53326 loops=1)
                     ->  Hash  (cost=51.17..51.17 rows=617 width=204) (actual time=7.588..7.588 rows=617 loops=1)
                           Buckets: 1024  Batches: 1  Memory Usage: 128kB
                           ->  Seq Scan on campaign  (cost=0.00..51.17 rows=617 width=204) (actual time=0.009..3.124 rows=617 loops=1)
 Planning time: 0.604 ms
 Execution time: 922.323 ms

Answer 1

您可以考虑这样编写查询:

SELECT c."id", c."name", c."status",
       (SELECT SUM(s."impressions")
        FROM "stats" s
        WHERE c."id" = s."campaign_id" AND
              s."date" >= '2016-03-27'::date AND
              s."date" <= '2016-04-25'::date
       ) as "impressions" 
FROM "campaign" c
ORDER BY c."status" ASC, c."created" DESC ;

那么最好的索引是campaign(status, created desc, name, id) 和stats(campaign_id, date, impressions)。注意:这些都是完全覆盖查询的多列索引(意味着所有访问的列都在索引中)。

Postgres 优化器很好。但是，不要认为优化掉查询形式中的外部聚合就足够了。因为它可以为 ORDER BY 使用索引，所以使用相关子查询的这个版本可能比使用显式 GROUP BY 的版本更快。