show random_page_cost ; random_page_cost
------------------ 4 (1 row) 2015-10-27 12:30 GMT+01:00 Alex Ignatov <a.igna...@postgrespro.ru>: > > > On 27.10.2015 14:19, Bertrand Paquet wrote: > > relname | n_live_tup | n_dead_tup | last_vacuum > | last_autovacuum | last_analyze | > last_autoanalyze > > > ------------------------+------------+------------+-------------------------------+-------------------------------+-------------------------------+------------------------------- > > external_sync_messages | 998105 | 11750 | 2015-10-26 > 20:15:17.484771+00 | 2015-10-02 15:04:25.944479+00 | 2015-10-26 > 20:15:19.465308+00 | 2015-10-22 12:24:26.947616+00 > > (1 row) > > 2015-10-27 12:17 GMT+01:00 Alex Ignatov <a.igna...@postgrespro.ru>: > >> On 27.10.2015 14:10, Bertrand Paquet wrote: >> >> Yes, I have run VACUUM ANALYZE, no effect. >> >> Bertrand >> >> 2015-10-27 12:08 GMT+01:00 Alex Ignatov < <a.igna...@postgrespro.ru> >> 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> >>> http://www.postgrespro.com >>> The Russian Postgres Company >>> >>> >> What is the result of >> select relname,n_live_tup,n_dead_tup, last_vacuum, last_autovacuum, >> last_analyze, last_autoanalyze from pg_stat_user_tables where >> relname='external_sync_messages' ? >> >> -- >> Alex Ignatov >> Postgres Professional: http://www.postgrespro.com >> The Russian Postgres Company >> >> >> > What is yours random_page_cost parameter in postgres config? > > -- > Alex Ignatov > Postgres Professional: http://www.postgrespro.com > The Russian Postgres Company > > >