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
<mailto: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
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
