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

Reply via email to