show random_page_cost ;

 random_page_cost

------------------

 4

(1 row)

2015-10-27 12:30 GMT+01:00 Alex Ignatov <a.igna...@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.igna...@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.igna...@postgrespro.ru>
>> 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>
>>> 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
>
>
>

Reply via email to