Hi all, We have a slow query. After analyzing, the planner decision seems to be discutable : the query is faster when disabling seqscan. See below the two query plan, and an extract from pg_stats.
Any idea about what to change to help the planner ? An information which can be useful : the number on distinct value on organization_id is very very low, may be the planner does not known that, and take the wrong decision. Regards, Bertrand # explain analyze SELECT 1 AS one FROM "external_sync_messages" WHERE "external_sync_messages"."organization_id" = 1612 AND ("external_sync_messages"."status" NOT IN ('sent_to_proxy', 'in_progress', 'ok')) AND "external_sync_messages"."handled_by" IS NULL LIMIT 1; QUERY PLAN -------------------------------------------------------------------------------------------------------------------------------------------- Limit (cost=0.00..12.39 rows=1 width=0) (actual time=232.212..232.213 rows=1 loops=1) -> Seq Scan on external_sync_messages (cost=0.00..79104.69 rows=6385 width=0) (actual time=232.209..232.209 rows=1 loops=1) Filter: ((handled_by IS NULL) AND (organization_id = 1612) AND ((status)::text <> ALL ('{sent_to_proxy,in_progress,ok}'::text[]))) Rows Removed by Filter: 600140 Planning time: 0.490 ms Execution time: 232.246 ms (6 rows) # set enable_seqscan = off; SET # explain analyze SELECT 1 AS one FROM "external_sync_messages" WHERE "external_sync_messages"."organization_id" = 1612 AND ("external_sync_messages"."status" NOT IN ('sent_to_proxy', 'in_progress', 'ok')) AND "external_sync_messages"."handled_by" IS NULL LIMIT 1; QUERY PLAN -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- Limit (cost=0.42..39.88 rows=1 width=0) (actual time=0.030..0.030 rows=1 loops=1) -> Index Scan using index_external_sync_messages_on_organization_id on external_sync_messages (cost=0.42..251934.05 rows=6385 width=0) (actual time=0.028..0.028 rows=1 loops=1) Index Cond: (organization_id = 1612) Filter: ((handled_by IS NULL) AND ((status)::text <> ALL ('{sent_to_proxy,in_progress,ok}'::text[]))) Planning time: 0.103 ms Execution time: 0.052 ms (6 rows) # SELECT attname, inherited, n_distinct, array_to_string(most_common_vals, E'\n') as most_common_vals FROM pg_stats WHERE tablename = 'external_sync_messages' and attname IN ('status', 'organization_id', 'handled_by'); attname | inherited | n_distinct | most_common_vals -----------------+-----------+------------+------------------ handled_by | f | 3 | 3 + | | | 236140 + | | | 54413 organization_id | f | 22 | 1612 + | | | 287 + | | | 967 + | | | 1223 + | | | 1123 + | | | 1930 + | | | 841 + | | | 1814 + | | | 711 + | | | 1513 + | | | 1794 + | | | 1246 + | | | 1673 + | | | 1552 + | | | 1747 + | | | 2611 + | | | 2217 + | | | 2448 + | | | 2133 + | | | 1861 + | | | 2616 + | | | 2796 status | f | 6 | ok + | | | ignored + | | | channel_error + | | | in_progress + | | | error + | | | sent_to_proxy (3 rows) # select count(*) from external_sync_messages; count -------- 992912 (1 row)