Subject: Re: Query planner wants to use seq scan
To: Bertrand Paquet <bertrand.paquet@doctolib.fr>,
 pgsql-performance@postgresql.org
References: 
 <CAN1xZsc3LG3gV4j+x0Uhsh4e2Xjf1HEuAT7CnBNNPUCTXwYZhg@mail.gmail.com>
From: Alex Ignatov <a.ignatov@postgrespro.ru>
Message-ID: <562F5B41.7060603@postgrespro.ru>
Date: Tue, 27 Oct 2015 14:08:49 +0300
User-Agent: Mozilla/5.0 (Windows NT 10.0; WOW64; rv:38.0) Gecko/20100101
 Thunderbird/38.3.0
MIME-Version: 1.0
In-Reply-To: 
 <CAN1xZsc3LG3gV4j+x0Uhsh4e2Xjf1HEuAT7CnBNNPUCTXwYZhg@mail.gmail.com>
Content-Type: text/plain; charset=utf-8; format=flowed
Content-Transfer-Encoding: 7bit
Precedence: bulk
Sender: pgsql-performance-owner@postgresql.org

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


-- 
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance