pg.ddx.io pgsql-performance@postgresql.org mailing list archive help / color / mirror / Atom feed From: Alex Ignatov <a.ignatov@postgrespro.ru>
To: Bertrand Paquet <bertrand.paquet@doctolib.fr>
Cc: pgsql-performance@postgresql.org
Subject: Re: Query planner wants to use seq scan
Date: Tue, 27 Oct 2015 14:17:58 +0300
Message-ID: <562F5D66.5010305@postgrespro.ru> (raw )
In-Reply-To: <CAN1xZsdC5xyXgTZKn1rkczFGqOnid2xZ4b1zq_VB99+2RmyH5Q@mail.gmail.com >
References: <CAN1xZsc3LG3gV4j+x0Uhsh4e2Xjf1HEuAT7CnBNNPUCTXwYZhg@mail.gmail.com >
<562F5B41.7060603@postgrespro.ru >
<CAN1xZsdC5xyXgTZKn1rkczFGqOnid2xZ4b1zq_VB99+2RmyH5Q@mail.gmail.com >
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-performance>
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.ignatov@postgrespro.ru
> <mailto:a.ignatov@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
view thread (14+ messages) latest in thread
Message-ID: <562F5D66.5010305@postgrespro.ru>
Permalink: ../562F5D66.5010305@postgrespro.ru/
Also on: postgresql.org/message-id/562F5D66.5010305@postgrespro.ru
copy link · copy postgr.es
reply Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: a.ignatov@postgrespro.ru, bertrand.paquet@doctolib.fr
Subject: Re: Query planner wants to use seq scan
In-Reply-To: <562F5D66.5010305@postgrespro.ru>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox