Re: Improving Performance of Query ~ Filter by A, Sort by B

Поиск
Список
Период
Сортировка
От Lincoln Swaine-Moore
Тема Re: Improving Performance of Query ~ Filter by A, Sort by B
Дата
Msg-id CABcidkLEZ=WifDQJLtPFaTeFLYTFkEbXDhbFLGVk5FzTVDSbbw@mail.gmail.com
обсуждение исходный текст
Ответ на Re: Improving Performance of Query ~ Filter by A, Sort by B  (legrand legrand <legrand_legrand@hotmail.com>)
Ответы Re: Improving Performance of Query ~ Filter by A, Sort by B  (Tom Lane <tgl@sss.pgh.pa.us>)
Список pgsql-performance
Дерево обсуждения
Thanks for looking into this!

Here's the result (I turned off the timeout and got it to finish):

EXPLAIN ANALYZE
SELECT "a"."id"
FROM  a_partition1 "a"
WHERE "a"."parent_id" IN (
    49188,14816,14758,8402
)
ORDER BY "a"."tmstmp" DESC
LIMIT 20;

                                        QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Limit  (cost=0.43..5710.03 rows=20 width=12) (actual time=1141878.105..1142350.296 rows=20 loops=1)
   ->  Index Scan Backward using a_tmstmp_idx1 on a_partition1 a  (cost=0.43..1662350.21 rows=5823 width=12) (actual time=1141878.103..1142350.274 rows=20 loops=1)
         Filter: (parent_id = ANY ('{49188,14816,14758,8402}'::integer[]))
         Rows Removed by Filter: 7931478
 Planning time: 0.122 ms
 Execution time: 1142350.336 ms
(6 rows)
(Note: I've chosen parent_ids that I know are associated with the part_key 1, but the query plan was the same with the 4 parent_ids in your query.)

Looks like it's using the filter in the same way as the query on the parent table, so seems be a problem beyond the partitioning.

And as soon as I cut it back to 3 parent_ids, jumps to a query plan using a_parent_id_idx1 again:

EXPLAIN ANALYZE
SELECT "a"."id"
FROM  a_partition1 "a"
WHERE "a"."parent_id" IN (
    19948,21436,41220
)
ORDER BY "a"."tmstmp" DESC
LIMIT 20;

                             QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Limit  (cost=5004.57..5004.62 rows=20 width=12) (actual time=36.329..36.341 rows=20 loops=1)
   ->  Sort  (cost=5004.57..5015.49 rows=4367 width=12) (actual time=36.328..36.332 rows=20 loops=1)
         Sort Key: tmstmp DESC
         Sort Method: top-N heapsort  Memory: 26kB
         ->  Index Scan using a_parent_id_idx1 on a_partition1 a  (cost=0.43..4888.37 rows=4367 width=12) (actual time=5.581..36.270 rows=50 loops=1)
               Index Cond: (parent_id = ANY ('{19948,21436,41220}'::integer[]))
 Planning time: 0.117 ms
 Execution time: 36.379 ms
(8 rows)


Thanks again for your help!




On Wed, Jul 11, 2018 at 5:41 PM, legrand legrand <legrand_legrand@hotmail.com> wrote:
Hello,

I have tested it with release 11 and limit 20 is pushed to each partition
when using index on tmstmp.

Could you tell us what is the result of your query applyed to one partition

EXPLAIN ANALYZE
SELECT "a"."id"
FROM  a_partition1 "a"
WHERE "a"."parent_id" IN (
    34226,24506,40987,27162
)
ORDER BY "a"."tmstmp" DESC
LIMIT 20;

May be that limit 20 is not pushed to partitions in your version ?
Regards
PAscal





--
Sent from: http://www.postgresql-archive.org/PostgreSQL-performance-f2050081.html




--
Lincoln Swaine-Moore

В списке pgsql-performance по дате отправления:

Предыдущее
От: legrand legrand
Дата:
Сообщение: Re: Improving Performance of Query ~ Filter by A, Sort by B
Следующее
От: Tom Lane
Дата:
Сообщение: Re: Improving Performance of Query ~ Filter by A, Sort by B