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

Поиск

Список

Период

Сортировка

От	Lincoln Swaine-Moore
Тема	Improving Performance of Query ~ Filter by A, Sort by B
Дата	10 июля 2018 г. 21:07:42
Msg-id	CABcidk+P9Ujq-qvXXjivGrYjm7z_zJUgpjJEM8hXhrxgraR8_Q@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 (Jeff Janes <jeff.janes@gmail.com>)
Список	pgsql-performance

Дерево обсуждения

Hi all,

I'm having a good bit of trouble making production-ready a query I wouldn't have thought would be too challenging.

Below is the relevant portions of my table, which is partitioned on values of part_key from 1-5. The number of rows is on the order of 10^7, and the number of unique "parent_id" values is on the order of 10^4. "tmstmp" is continuous (more or less). Neither parent_id nor tmstmp is nullable. The table receives a fair amount of INSERTS, and also a number of UPDATES.

db=> \d a

Table "public.a"

Column | Type | Collation | Nullable | Default

-------------------------+--------------------------+-----------+----------+----------------------------------------------

id | integer | | | nextval('a_id_seq'::regclass)

parent_id | integer | | not null |

tmstmp | timestamp with time zone | | not null |

part_key | integer | | |

Partition key: LIST (part_key)

Number of partitions: 5 (Use \d+ to list them.)

db=> \d a_partition1

Table "public.a_partition1"

Column | Type | Collation | Nullable | Default

-------------------------+--------------------------+-----------+----------+----------------------------------------------

id | integer | | | nextval('a_id_seq'::regclass)

parent_id | integer | | not null |

tmstmp | timestamp with time zone | | not null |

part_key | integer | | |

Partition of: a FOR VALUES IN (1)

Indexes:

"a_pk_idx1" UNIQUE, btree (id)

"a_tmstmp_idx1" btree (tmstmp)

"a_parent_id_idx1" btree (parent_id)

Check constraints:

"a_partition_check_part_key1" CHECK (part_key = 1)

Foreign-key constraints:

"a_partition_parent_id_fk_b_id1" FOREIGN KEY (parent_id) REFERENCES b(id) DEFERRABLE INITIALLY DEFERRED

db=> SELECT relname, relpages, reltuples, relallvisible, relkind, relnatts, relhassubclass, reloptions, pg_table_size(oid) FROM pg_class WHERE relname like 'a_partition%';

-----------------------------------+----------+-------------+---------------+---------+----------+----------------+------------+---------------

a_partition1 | 152146 | 873939 | 106863 | r | 26 | f | | 287197233152

a_partition2 | 669987 | 3.62268e+06 | 0 | r | 26 | f | | 161877745664

a_partition3 | 562069 | 2.94414e+06 | 213794 | r | 26 | f | | 132375994368

a_partition4 | 729880 | 3.95513e+06 | 69761 | r | 26 | f | | 188689047552

a_partition5 | 834132 | 4.9748e+06 | 52622 | r | 26 | f | | 218596630528

(5 rows)

I'm interested in filtering by parent_id (an indexed foreign-key relationship, though I think it shouldn't matter in this context), then sorting the result by a timestamp field (also indexed). I only need 20 at a time.

The naive query works well for a very small number of ids (up to 3):

EXPLAIN (ANALYZE, BUFFERS)

SELECT "a"."id"

FROM "a"

WHERE "a"."parent_id" IN (

37066,41174,28843

)

ORDER BY "a"."tmstmp" DESC

LIMIT 20;

Limit (cost=9838.23..9838.28 rows=20 width=12) (actual time=13.307..13.307 rows=0 loops=1)

Buffers: shared hit=29 read=16

-> Sort (cost=9838.23..9860.12 rows=8755 width=12) (actual time=13.306..13.306 rows=0 loops=1)

Sort Key: a_partition1.tmstmp DESC

Sort Method: quicksort Memory: 25kB

Buffers: shared hit=29 read=16

-> Append (cost=0.43..9605.26 rows=8755 width=12) (actual time=13.302..13.302 rows=0 loops=1)

Buffers: shared hit=29 read=16

-> Index Scan using a_parent_id_idx1 on a_partition1 (cost=0.43..4985.45 rows=4455 width=12) (actual time=4.007..4.007 rows=0 loops=1)

Index Cond: (parent_id = ANY ('{37066,41174,28843}'::integer[]))

Buffers: shared hit=6 read=3

-> Index Scan using a_parent_id_idx2 on a_partition2 (cost=0.43..1072.79 rows=956 width=12) (actual time=3.521..3.521 rows=0 loops=1)

Index Cond: (parent_id = ANY ('{37066,41174,28843}'::integer[]))

Buffers: shared hit=5 read=4

-> Index Scan using a_parent_id_idx3 on a_partition3 (cost=0.43..839.30 rows=899 width=12) (actual time=2.172..2.172 rows=0 loops=1)

Index Cond: (parent_id = ANY ('{37066,41174,28843}'::integer[]))

Buffers: shared hit=6 read=3

-> Index Scan using a_parent_id_idx4 on a_partition4 (cost=0.43..1041.11 rows=959 width=12) (actual time=1.822..1.822 rows=0 loops=1)

Index Cond: (parent_id = ANY ('{37066,41174,28843}'::integer[]))

Buffers: shared hit=6 read=3

-> Index Scan using a_parent_id_idx5 on a_partition5 (cost=0.43..1666.61 rows=1486 width=12) (actual time=1.777..1.777 rows=0 loops=1)

Index Cond: (parent_id = ANY ('{37066,41174,28843}'::integer[]))

Buffers: shared hit=6 read=3

Planning time: 0.559 ms

Execution time: 13.343 ms

(25 rows)

But as soon as the number included in the filter goes up slightly (at about 4), the query plan changes the indexes it uses in a way that makes it impossibly slow. The below is only an EXPLAIN because the query times out:

EXPLAIN

SELECT "a"."id"

FROM "a"

WHERE "a"."parent_id" IN (

34226,24506,40987,27162

)

ORDER BY "a"."tmstmp" DESC

LIMIT 20;

Limit (cost=2.22..8663.99 rows=20 width=12)

-> Merge Append (cost=2.22..5055447.67 rows=11673 width=12)

Sort Key: a_partition1.tmstmp DESC

-> Index Scan Backward using a_tmstmp_idx1 on a_partition1 (cost=0.43..1665521.55 rows=5940 width=12)