pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedQuery planner wants to use seq scan
14+ messages / 4 participants
[nested] [flat]
* Query planner wants to use seq scan
@ 2015-10-27 09:35 Bertrand Paquet <bertrand.paquet@doctolib.fr>
2015-10-27 11:08 ` Re: Query planner wants to use seq scan Alex Ignatov <a.ignatov@postgrespro.ru>
2015-10-27 12:03 ` Re: Query planner wants to use seq scan Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 2 replies; 14+ messages in thread
From: Bertrand Paquet @ 2015-10-27 09:35 UTC (permalink / raw)
To: pgsql-performance
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)
^ permalink raw reply [nested|flat] 14+ messages in thread
* Re: Query planner wants to use seq scan
2015-10-27 09:35 Query planner wants to use seq scan Bertrand Paquet <bertrand.paquet@doctolib.fr>
@ 2015-10-27 11:08 ` Alex Ignatov <a.ignatov@postgrespro.ru>
2015-10-27 11:10 ` Re: Query planner wants to use seq scan Bertrand Paquet <bertrand.paquet@doctolib.fr>
1 sibling, 1 reply; 14+ messages in thread
From: Alex Ignatov @ 2015-10-27 11:08 UTC (permalink / raw)
To: Bertrand Paquet <bertrand.paquet@doctolib.fr>; pgsql-performance
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
^ permalink raw reply [nested|flat] 14+ messages in thread
* Re: Query planner wants to use seq scan
2015-10-27 09:35 Query planner wants to use seq scan Bertrand Paquet <bertrand.paquet@doctolib.fr>
2015-10-27 11:08 ` Re: Query planner wants to use seq scan Alex Ignatov <a.ignatov@postgrespro.ru>
@ 2015-10-27 11:10 ` Bertrand Paquet <bertrand.paquet@doctolib.fr>
2015-10-27 11:17 ` Re: Query planner wants to use seq scan Alex Ignatov <a.ignatov@postgrespro.ru>
0 siblings, 1 reply; 14+ messages in thread
From: Bertrand Paquet @ 2015-10-27 11:10 UTC (permalink / raw)
To: Alex Ignatov <a.ignatov@postgrespro.ru>; +Cc: pgsql-performance
Yes, I have run VACUUM ANALYZE, no effect.
Bertrand
2015-10-27 12:08 GMT+01:00 Alex Ignatov <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
>
>
^ permalink raw reply [nested|flat] 14+ messages in thread
* Re: Query planner wants to use seq scan
2015-10-27 09:35 Query planner wants to use seq scan Bertrand Paquet <bertrand.paquet@doctolib.fr>
2015-10-27 11:08 ` Re: Query planner wants to use seq scan Alex Ignatov <a.ignatov@postgrespro.ru>
2015-10-27 11:10 ` Re: Query planner wants to use seq scan Bertrand Paquet <bertrand.paquet@doctolib.fr>
@ 2015-10-27 11:17 ` Alex Ignatov <a.ignatov@postgrespro.ru>
2015-10-27 11:19 ` Re: Query planner wants to use seq scan Bertrand Paquet <bertrand.paquet@doctolib.fr>
0 siblings, 1 reply; 14+ messages in thread
From: Alex Ignatov @ 2015-10-27 11:17 UTC (permalink / raw)
To: Bertrand Paquet <bertrand.paquet@doctolib.fr>; +Cc: pgsql-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
^ permalink raw reply [nested|flat] 14+ messages in thread
* Re: Query planner wants to use seq scan
2015-10-27 09:35 Query planner wants to use seq scan Bertrand Paquet <bertrand.paquet@doctolib.fr>
2015-10-27 11:08 ` Re: Query planner wants to use seq scan Alex Ignatov <a.ignatov@postgrespro.ru>
2015-10-27 11:10 ` Re: Query planner wants to use seq scan Bertrand Paquet <bertrand.paquet@doctolib.fr>
2015-10-27 11:17 ` Re: Query planner wants to use seq scan Alex Ignatov <a.ignatov@postgrespro.ru>
@ 2015-10-27 11:19 ` Bertrand Paquet <bertrand.paquet@doctolib.fr>
2015-10-27 11:30 ` Re: Query planner wants to use seq scan Alex Ignatov <a.ignatov@postgrespro.ru>
0 siblings, 1 reply; 14+ messages in thread
From: Bertrand Paquet @ 2015-10-27 11:19 UTC (permalink / raw)
To: Alex Ignatov <a.ignatov@postgrespro.ru>; +Cc: pgsql-performance
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.ignatov@postgrespro.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.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
>
>
>
^ permalink raw reply [nested|flat] 14+ messages in thread
* Re: Query planner wants to use seq scan
2015-10-27 09:35 Query planner wants to use seq scan Bertrand Paquet <bertrand.paquet@doctolib.fr>
2015-10-27 11:08 ` Re: Query planner wants to use seq scan Alex Ignatov <a.ignatov@postgrespro.ru>
2015-10-27 11:10 ` Re: Query planner wants to use seq scan Bertrand Paquet <bertrand.paquet@doctolib.fr>
2015-10-27 11:17 ` Re: Query planner wants to use seq scan Alex Ignatov <a.ignatov@postgrespro.ru>
2015-10-27 11:19 ` Re: Query planner wants to use seq scan Bertrand Paquet <bertrand.paquet@doctolib.fr>
@ 2015-10-27 11:30 ` Alex Ignatov <a.ignatov@postgrespro.ru>
2015-10-27 13:06 ` Re: Query planner wants to use seq scan Bertrand Paquet <bertrand.paquet@doctolib.fr>
0 siblings, 1 reply; 14+ messages in thread
From: Alex Ignatov @ 2015-10-27 11:30 UTC (permalink / raw)
To: Bertrand Paquet <bertrand.paquet@doctolib.fr>; +Cc: pgsql-performance
On 27.10.2015 14:19, Bertrand Paquet wrote:
>
> 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.ignatov@postgrespro.ru
> <mailto:a.ignatov@postgrespro.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.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
>
>
What is yours random_page_cost parameter in postgres config?
--
Alex Ignatov
Postgres Professional: http://www.postgrespro.com
The Russian Postgres Company
^ permalink raw reply [nested|flat] 14+ messages in thread
* Re: Query planner wants to use seq scan
2015-10-27 09:35 Query planner wants to use seq scan Bertrand Paquet <bertrand.paquet@doctolib.fr>
2015-10-27 11:08 ` Re: Query planner wants to use seq scan Alex Ignatov <a.ignatov@postgrespro.ru>
2015-10-27 11:10 ` Re: Query planner wants to use seq scan Bertrand Paquet <bertrand.paquet@doctolib.fr>
2015-10-27 11:17 ` Re: Query planner wants to use seq scan Alex Ignatov <a.ignatov@postgrespro.ru>
2015-10-27 11:19 ` Re: Query planner wants to use seq scan Bertrand Paquet <bertrand.paquet@doctolib.fr>
2015-10-27 11:30 ` Re: Query planner wants to use seq scan Alex Ignatov <a.ignatov@postgrespro.ru>
@ 2015-10-27 13:06 ` Bertrand Paquet <bertrand.paquet@doctolib.fr>
0 siblings, 0 replies; 14+ messages in thread
From: Bertrand Paquet @ 2015-10-27 13:06 UTC (permalink / raw)
To: Alex Ignatov <a.ignatov@postgrespro.ru>; +Cc: pgsql-performance
show random_page_cost ;
random_page_cost
------------------
4
(1 row)
2015-10-27 12:30 GMT+01:00 Alex Ignatov <a.ignatov@postgrespro.ru>:
>
>
> On 27.10.2015 14:19, Bertrand Paquet wrote:
>
> 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.ignatov@postgrespro.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.ignatov@postgrespro.ru>
>> 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;
>>> 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
>>
>>
>>
> What is yours random_page_cost parameter in postgres config?
>
> --
> Alex Ignatov
> Postgres Professional: http://www.postgrespro.com
> The Russian Postgres Company
>
>
>
^ permalink raw reply [nested|flat] 14+ messages in thread
* Re: Query planner wants to use seq scan
2015-10-27 09:35 Query planner wants to use seq scan Bertrand Paquet <bertrand.paquet@doctolib.fr>
@ 2015-10-27 12:03 ` Tom Lane <tgl@sss.pgh.pa.us>
2015-10-27 13:06 ` Re: Query planner wants to use seq scan Bertrand Paquet <bertrand.paquet@doctolib.fr>
2015-10-27 17:33 ` Re: Query planner wants to use seq scan Bertrand Paquet <bertrand.paquet@doctolib.fr>
1 sibling, 2 replies; 14+ messages in thread
From: Tom Lane @ 2015-10-27 12:03 UTC (permalink / raw)
To: Bertrand Paquet <bertrand.paquet@doctolib.fr>; +Cc: pgsql-performance
Bertrand Paquet <bertrand.paquet@doctolib.fr> writes:
> 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 ?
Neither one of those plans is very good: you're just hoping that the
Filter condition will let a tuple through sooner rather than later.
If you care about the performance of this type of query, I'd consider
creating an index on (organization_id, status, handled_by) so that all
the conditions can be checked in the index.
regards, tom lane
--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
^ permalink raw reply [nested|flat] 14+ messages in thread
* Re: Query planner wants to use seq scan
2015-10-27 09:35 Query planner wants to use seq scan Bertrand Paquet <bertrand.paquet@doctolib.fr>
2015-10-27 12:03 ` Re: Query planner wants to use seq scan Tom Lane <tgl@sss.pgh.pa.us>
@ 2015-10-27 13:06 ` Bertrand Paquet <bertrand.paquet@doctolib.fr>
1 sibling, 0 replies; 14+ messages in thread
From: Bertrand Paquet @ 2015-10-27 13:06 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: pgsql-performance
Hi tom,
I did the test yesterday with an index on the three fields, and with a
partial index on organization and status and where is null condition on
handled.
Le mardi 27 octobre 2015, Tom Lane <tgl@sss.pgh.pa.us> a écrit :
> Bertrand Paquet <bertrand.paquet@doctolib.fr> writes:
> > 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 ?
>
> Neither one of those plans is very good: you're just hoping that the
> Filter condition will let a tuple through sooner rather than later.
>
> If you care about the performance of this type of query, I'd consider
> creating an index on (organization_id, status, handled_by) so that all
> the conditions can be checked in the index.
>
> regards, tom lane
>
^ permalink raw reply [nested|flat] 14+ messages in thread
* Re: Query planner wants to use seq scan
2015-10-27 09:35 Query planner wants to use seq scan Bertrand Paquet <bertrand.paquet@doctolib.fr>
2015-10-27 12:03 ` Re: Query planner wants to use seq scan Tom Lane <tgl@sss.pgh.pa.us>
@ 2015-10-27 17:33 ` Bertrand Paquet <bertrand.paquet@doctolib.fr>
2015-10-27 20:56 ` Re: Query planner wants to use seq scan Bertrand Paquet <bertrand.paquet@doctolib.fr>
1 sibling, 1 reply; 14+ messages in thread
From: Bertrand Paquet @ 2015-10-27 17:33 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: pgsql-performance
Hi tom,
I did the test yesterday with an index on the three fields, and with a
partial index on organization and status and where is null condition on
handled. I saw no modification on query plan.
May be I forgot to analyze vacuum after. I will retry tonight.
I use a btree index. Is it the good solution, even with the In clause ?
Regards,
Bertrand
Le mardi 27 octobre 2015, Tom Lane <tgl@sss.pgh.pa.us> a écrit :
> Bertrand Paquet <bertrand.paquet@doctolib.fr <javascript:;>> writes:
> > 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 ?
>
> Neither one of those plans is very good: you're just hoping that the
> Filter condition will let a tuple through sooner rather than later.
>
> If you care about the performance of this type of query, I'd consider
> creating an index on (organization_id, status, handled_by) so that all
> the conditions can be checked in the index.
>
> regards, tom lane
>
^ permalink raw reply [nested|flat] 14+ messages in thread
* Re: Query planner wants to use seq scan
2015-10-27 09:35 Query planner wants to use seq scan Bertrand Paquet <bertrand.paquet@doctolib.fr>
2015-10-27 12:03 ` Re: Query planner wants to use seq scan Tom Lane <tgl@sss.pgh.pa.us>
2015-10-27 17:33 ` Re: Query planner wants to use seq scan Bertrand Paquet <bertrand.paquet@doctolib.fr>
@ 2015-10-27 20:56 ` Bertrand Paquet <bertrand.paquet@doctolib.fr>
2015-10-28 03:07 ` Re: Query planner wants to use seq scan Jim Nasby <Jim.Nasby@BlueTreble.com>
2015-10-29 12:27 ` Re: Query planner wants to use seq scan Alex Ignatov <a.ignatov@postgrespro.ru>
0 siblings, 2 replies; 14+ messages in thread
From: Bertrand Paquet @ 2015-10-27 20:56 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: pgsql-performance
So,
Tonight, the index on the three field is used, may be my yesterday vacuum
updated stats.
Thx you for your help.
Regards,
Bertrand
2015-10-27 18:33 GMT+01:00 Bertrand Paquet <bertrand.paquet@doctolib.fr>:
> Hi tom,
>
> I did the test yesterday with an index on the three fields, and with a
> partial index on organization and status and where is null condition on
> handled. I saw no modification on query plan.
> May be I forgot to analyze vacuum after. I will retry tonight.
>
> I use a btree index. Is it the good solution, even with the In clause ?
>
> Regards,
>
> Bertrand
>
> Le mardi 27 octobre 2015, Tom Lane <tgl@sss.pgh.pa.us> a écrit :
>
>> Bertrand Paquet <bertrand.paquet@doctolib.fr> writes:
>> > 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 ?
>>
>> Neither one of those plans is very good: you're just hoping that the
>> Filter condition will let a tuple through sooner rather than later.
>>
>> If you care about the performance of this type of query, I'd consider
>> creating an index on (organization_id, status, handled_by) so that all
>> the conditions can be checked in the index.
>>
>> regards, tom lane
>>
>
^ permalink raw reply [nested|flat] 14+ messages in thread
* Re: Query planner wants to use seq scan
2015-10-27 09:35 Query planner wants to use seq scan Bertrand Paquet <bertrand.paquet@doctolib.fr>
2015-10-27 12:03 ` Re: Query planner wants to use seq scan Tom Lane <tgl@sss.pgh.pa.us>
2015-10-27 17:33 ` Re: Query planner wants to use seq scan Bertrand Paquet <bertrand.paquet@doctolib.fr>
2015-10-27 20:56 ` Re: Query planner wants to use seq scan Bertrand Paquet <bertrand.paquet@doctolib.fr>
@ 2015-10-28 03:07 ` Jim Nasby <Jim.Nasby@BlueTreble.com>
1 sibling, 0 replies; 14+ messages in thread
From: Jim Nasby @ 2015-10-28 03:07 UTC (permalink / raw)
To: Bertrand Paquet <bertrand.paquet@doctolib.fr>; Tom Lane <tgl@sss.pgh.pa.us>; +Cc: pgsql-performance
On 10/27/15 3:56 PM, Bertrand Paquet wrote:
> Tonight, the index on the three field is used, may be my yesterday
> vacuum updated stats.
BTW, you can run just ANALYZE, which is *far* faster than a VACUUM on a
large table.
--
Jim Nasby, Data Architect, Blue Treble Consulting, Austin TX
Experts in Analytics, Data Architecture and PostgreSQL
Data in Trouble? Get it in Treble! http://BlueTreble.com
--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
^ permalink raw reply [nested|flat] 14+ messages in thread
* Re: Query planner wants to use seq scan
2015-10-27 09:35 Query planner wants to use seq scan Bertrand Paquet <bertrand.paquet@doctolib.fr>
2015-10-27 12:03 ` Re: Query planner wants to use seq scan Tom Lane <tgl@sss.pgh.pa.us>
2015-10-27 17:33 ` Re: Query planner wants to use seq scan Bertrand Paquet <bertrand.paquet@doctolib.fr>
2015-10-27 20:56 ` Re: Query planner wants to use seq scan Bertrand Paquet <bertrand.paquet@doctolib.fr>
@ 2015-10-29 12:27 ` Alex Ignatov <a.ignatov@postgrespro.ru>
2015-10-29 13:16 ` Re: Query planner wants to use seq scan Bertrand Paquet <bertrand.paquet@doctolib.fr>
1 sibling, 1 reply; 14+ messages in thread
From: Alex Ignatov @ 2015-10-29 12:27 UTC (permalink / raw)
To: Bertrand Paquet <bertrand.paquet@doctolib.fr>; Tom Lane <tgl@sss.pgh.pa.us>; +Cc: pgsql-performance
On 27.10.2015 23:56, Bertrand Paquet wrote:
> So,
>
> Tonight, the index on the three field is used, may be my yesterday
> vacuum updated stats.
>
> Thx you for your help.
>
> Regards,
>
> Bertrand
>
>
>
>
> 2015-10-27 18:33 GMT+01:00 Bertrand Paquet
> <bertrand.paquet@doctolib.fr <mailto:bertrand.paquet@doctolib.fr>>:
>
> Hi tom,
>
> I did the test yesterday with an index on the three fields, and
> with a partial index on organization and status and where is null
> condition on handled. I saw no modification on query plan.
> May be I forgot to analyze vacuum after. I will retry tonight.
>
> I use a btree index. Is it the good solution, even with the In
> clause ?
>
> Regards,
>
> Bertrand
>
> Le mardi 27 octobre 2015, Tom Lane <tgl@sss.pgh.pa.us
> <mailto:tgl@sss.pgh.pa.us>> a écrit :
>
> Bertrand Paquet <bertrand.paquet@doctolib.fr> writes:
> > 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 ?
>
> Neither one of those plans is very good: you're just hoping
> that the
> Filter condition will let a tuple through sooner rather than
> later.
>
> If you care about the performance of this type of query, I'd
> consider
> creating an index on (organization_id, status, handled_by) so
> that all
> the conditions can be checked in the index.
>
> regards, tom lane
>
>
Hello Bertrand once again!
What's your status? Does the plan changed after deploying three field
index ?
--
Alex Ignatov
Postgres Professional: http://www.postgrespro.com
The Russian Postgres Company
^ permalink raw reply [nested|flat] 14+ messages in thread
* Re: Query planner wants to use seq scan
2015-10-27 09:35 Query planner wants to use seq scan Bertrand Paquet <bertrand.paquet@doctolib.fr>
2015-10-27 12:03 ` Re: Query planner wants to use seq scan Tom Lane <tgl@sss.pgh.pa.us>
2015-10-27 17:33 ` Re: Query planner wants to use seq scan Bertrand Paquet <bertrand.paquet@doctolib.fr>
2015-10-27 20:56 ` Re: Query planner wants to use seq scan Bertrand Paquet <bertrand.paquet@doctolib.fr>
2015-10-29 12:27 ` Re: Query planner wants to use seq scan Alex Ignatov <a.ignatov@postgrespro.ru>
@ 2015-10-29 13:16 ` Bertrand Paquet <bertrand.paquet@doctolib.fr>
0 siblings, 0 replies; 14+ messages in thread
From: Bertrand Paquet @ 2015-10-29 13:16 UTC (permalink / raw)
To: Alex Ignatov <a.ignatov@postgrespro.ru>; +Cc: Tom Lane <tgl@sss.pgh.pa.us>; pgsql-performance
Yes, the three fields index AND vacuum solve the issue.
Regards,
Bertrand
2015-10-29 13:27 GMT+01:00 Alex Ignatov <a.ignatov@postgrespro.ru>:
>
>
> On 27.10.2015 23:56, Bertrand Paquet wrote:
>
> So,
>
> Tonight, the index on the three field is used, may be my yesterday vacuum
> updated stats.
>
> Thx you for your help.
>
> Regards,
>
> Bertrand
>
>
>
>
> 2015-10-27 18:33 GMT+01:00 Bertrand Paquet <bertrand.paquet@doctolib.fr>:
>
>> Hi tom,
>>
>> I did the test yesterday with an index on the three fields, and with a
>> partial index on organization and status and where is null condition on
>> handled. I saw no modification on query plan.
>> May be I forgot to analyze vacuum after. I will retry tonight.
>>
>> I use a btree index. Is it the good solution, even with the In clause ?
>>
>> Regards,
>>
>> Bertrand
>>
>> Le mardi 27 octobre 2015, Tom Lane < <tgl@sss.pgh.pa.us>tgl@sss.pgh.pa.us>
>> a écrit :
>>
>>> Bertrand Paquet <bertrand.paquet@doctolib.fr> writes:
>>> > 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 ?
>>>
>>> Neither one of those plans is very good: you're just hoping that the
>>> Filter condition will let a tuple through sooner rather than later.
>>>
>>> If you care about the performance of this type of query, I'd consider
>>> creating an index on (organization_id, status, handled_by) so that all
>>> the conditions can be checked in the index.
>>>
>>> regards, tom lane
>>>
>>
> Hello Bertrand once again!
> What's your status? Does the plan changed after deploying three field
> index ?
>
> --
> Alex Ignatov
> Postgres Professional: http://www.postgrespro.com
> The Russian Postgres Company
>
>
>
^ permalink raw reply [nested|flat] 14+ messages in thread
end of thread, other threads:[~2015-10-29 13:16 UTC | newest]
Thread overview: 14+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2015-10-27 09:35 Query planner wants to use seq scan Bertrand Paquet <bertrand.paquet@doctolib.fr>
2015-10-27 11:08 ` Alex Ignatov <a.ignatov@postgrespro.ru>
2015-10-27 11:10 ` Bertrand Paquet <bertrand.paquet@doctolib.fr>
2015-10-27 11:17 ` Alex Ignatov <a.ignatov@postgrespro.ru>
2015-10-27 11:19 ` Bertrand Paquet <bertrand.paquet@doctolib.fr>
2015-10-27 11:30 ` Alex Ignatov <a.ignatov@postgrespro.ru>
2015-10-27 13:06 ` Bertrand Paquet <bertrand.paquet@doctolib.fr>
2015-10-27 12:03 ` Tom Lane <tgl@sss.pgh.pa.us>
2015-10-27 13:06 ` Bertrand Paquet <bertrand.paquet@doctolib.fr>
2015-10-27 17:33 ` Bertrand Paquet <bertrand.paquet@doctolib.fr>
2015-10-27 20:56 ` Bertrand Paquet <bertrand.paquet@doctolib.fr>
2015-10-28 03:07 ` Jim Nasby <Jim.Nasby@BlueTreble.com>
2015-10-29 12:27 ` Alex Ignatov <a.ignatov@postgrespro.ru>
2015-10-29 13:16 ` Bertrand Paquet <bertrand.paquet@doctolib.fr>
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