Yes, I have run VACUUM ANALYZE, no effect. Bertrand
2015-10-27 12:08 GMT+01:00 Alex Ignatov <a.igna...@postgrespro.ru>: > On 27.10.2015 12:35, Bertrand Paquet wrote: > >> 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) >> >> >> Hello, Bertrand! > May be statistics on external_sync_messages is wrong? i.e planner give us > rows=6385 but seq scan give us Rows Removed by Filter: 600140 > Maybe you should recalc it by VACUUM ANALYZE it? > > -- > Alex Ignatov > Postgres Professional: http://www.postgrespro.com > The Russian Postgres Company > >