Re: Query planner wants to use seq scan

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(dot)ignatov(at)postgrespro(dot)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(dot)ignatov(at)postgrespro(dot)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
>
>
>