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

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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%';
              relname              | relpages |  reltuples  | relallvisible | relkind | relnatts | relhassubclass | reloptions | pg_table_size
-----------------------------------+----------+-------------+---------------+---------+----------+----------------+------------+---------------
 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)
               Filter: (parent_id = ANY ('{34226,24506,40987,27162}'::integer[]))
         ->  Index Scan Backward using a_tmstmp_idx2 on a_partition2  (cost=0.43..880517.20 rows=1274 width=12)
               Filter: (parent_id = ANY ('{34226,24506,40987,27162}'::integer[]))
         ->  Index Scan Backward using a_tmstmp_idx3 on a_partition3  (cost=0.43..639224.73 rows=1199 width=12)
               Filter: (parent_id = ANY ('{34226,24506,40987,27162}'::integer[]))
         ->  Index Scan Backward using a_tmstmp_idx4 on a_partition4  (cost=0.43..852881.68 rows=1278 width=12)
               Filter: (parent_id = ANY ('{34226,24506,40987,27162}'::integer[]))
         ->  Index Scan Backward using a_tmstmp_idx5 on a_partition5  (cost=0.43..1017137.75 rows=1982 width=12)
               Filter: (parent_id = ANY ('{34226,24506,40987,27162}'::integer[]))
(13 rows)


Something about the estimated row counts (this problem persisted after I tried ANALYZEing) forces usage of the tmstmp index and Merge Append (which seems wise) but also a filter condition on parent_id over an index condition, which is apparently prohibitively slow.

I tried creating a multicolumn index like:

CREATE INDEX "a_partition1_parent_and_tmstmp_idx" on "a_partition2" USING btree ("parent_id", "tmstmp" DESC);

But this didn't help (it wasn't used).

I also found that removing the LIMIT makes an acceptably fast (though different) query plan which uses the parent_id instead instead of the tmstmp one:

EXPLAIN (ANALYZE, BUFFERS)
SELECT "a"."id"
FROM "a"
WHERE "a"."parent_id" IN (
    17942,32006,36733,9698,27948
)
ORDER BY "a"."tmstmp" DESC;

 Sort  (cost=17004.82..17041.29 rows=14591 width=12) (actual time=109.650..109.677 rows=127 loops=1)
   Sort Key: a_partition1.tmstmp DESC
   Sort Method: quicksort  Memory: 30kB
   Buffers: shared hit=60 read=142 written=12
   ->  Append  (cost=0.43..15995.64 rows=14591 width=12) (actual time=9.206..109.504 rows=127 loops=1)
         Buffers: shared hit=60 read=142 written=12
         ->  Index Scan using a_parent_id_idx1 on a_partition1  (cost=0.43..8301.03 rows=7425 width=12) (actual time=9.205..11.116 rows=1 loops=1)
               Index Cond: (parent_id = ANY ('{17942,32006,36733,9698,27948}'::integer[]))
               Buffers: shared hit=10 read=6
         ->  Index Scan using a_parent_id_idx2 on a_partition2  (cost=0.43..1786.52 rows=1593 width=12) (actual time=7.116..76.000 rows=98 loops=1)
               Index Cond: (parent_id = ANY ('{17942,32006,36733,9698,27948}'::integer[]))
               Buffers: shared hit=15 read=97 written=10
         ->  Index Scan using a_parent_id_idx3 on a_partition3  (cost=0.43..1397.74 rows=1498 width=12) (actual time=3.160..3.160 rows=0 loops=1)
               Index Cond: (parent_id = ANY ('{17942,32006,36733,9698,27948}'::integer[]))
               Buffers: shared hit=10 read=5
         ->  Index Scan using a_parent_id_idx4 on a_partition4  (cost=0.43..1733.77 rows=1598 width=12) (actual time=1.975..16.960 rows=28 loops=1)
               Index Cond: (parent_id = ANY ('{17942,32006,36733,9698,27948}'::integer[]))
               Buffers: shared hit=14 read=30 written=2
         ->  Index Scan using a_parent_id_idx5 on a_partition5  (cost=0.43..2776.58 rows=2477 width=12) (actual time=2.155..2.155 rows=0 loops=1)
               Index Cond: (parent_id = ANY ('{17942,32006,36733,9698,27948}'::integer[]))
               Buffers: shared hit=11 read=4
 Planning time: 0.764 ms
 Execution time: 109.748 ms
(23 rows)


Eventually, I stumbled upon these links:

And from these, was able to partially resolve my issue by attaching an extra ORDER BY:

EXPLAIN (ANALYZE, BUFFERS)
SELECT "a"."id"
FROM "a" WHERE "a"."parent_id" IN (3909,26840,32181,22998,9632)
ORDER BY "a"."tmstmp" DESC,
         "a"."id" DESC
LIMIT 20;

 Limit  (cost=16383.91..16383.96 rows=20 width=12) (actual time=29.804..29.808 rows=6 loops=1)
   Buffers: shared hit=39 read=42
   ->  Sort  (cost=16383.91..16420.38 rows=14591 width=12) (actual time=29.803..29.804 rows=6 loops=1)
         Sort Key: a_partition1.tmstmp DESC, a_partition1.id DESC
         Sort Method: quicksort  Memory: 25kB
         Buffers: shared hit=39 read=42
         ->  Append  (cost=0.43..15995.64 rows=14591 width=12) (actual time=12.509..29.789 rows=6 loops=1)
               Buffers: shared hit=39 read=42
               ->  Index Scan using a_parent_id_idx1 on a_partition1  (cost=0.43..8301.03 rows=7425 width=12) (actual time=4.046..4.046 rows=0 loops=1)
                     Index Cond: (parent_id = ANY ('{3909,26840,32181,22998,9632}'::integer[]))
                     Buffers: shared hit=7 read=8
               ->  Index Scan using a_parent_id_idx2 on a_partition2  (cost=0.43..1786.52 rows=1593 width=12) (actual time=5.447..5.447 rows=0 loops=1)
                     Index Cond: (parent_id = ANY ('{3909,26840,32181,22998,9632}'::integer[]))
                     Buffers: shared hit=7 read=8
               ->  Index Scan using a_parent_id_idx3 on a_partition3  (cost=0.43..1397.74 rows=1498 width=12) (actual time=3.013..3.618 rows=1 loops=1)
                     Index Cond: (parent_id = ANY ('{3909,26840,32181,22998,9632}'::integer[]))
                     Buffers: shared hit=9 read=7
               ->  Index Scan using a_parent_id_idx4 on a_partition4  (cost=0.43..1733.77 rows=1598 width=12) (actual time=7.337..7.337 rows=0 loops=1)
                     Index Cond: (parent_id = ANY ('{3909,26840,32181,22998,9632}'::integer[]))
                     Buffers: shared hit=8 read=7
               ->  Index Scan using a_parent_id_idx5 on a_partition5  (cost=0.43..2776.58 rows=2477 width=12) (actual time=3.401..9.332 rows=5 loops=1)
                     Index Cond: (parent_id = ANY ('{3909,26840,32181,22998,9632}'::integer[]))
                     Buffers: shared hit=8 read=12
 Planning time: 0.601 ms
 Execution time: 29.851 ms
(25 rows)

This query plan (which is the same as when LIMIT is removed) has been a good short term solution when the number of "parent_id"s I'm using is still relatively small, but unfortunately queries grow untenably slow as the number of "parent_id"s involved increases:

EXPLAIN (ANALYZE, BUFFERS)
SELECT "a"."id"
FROM "a"
WHERE "a"."parent_id" IN (
    49694,35150,48216,45743,49529,47239,40752,46735,7692,33855,25062,47821,6947,9735,12611,33190,9523,39200,50056,14912,9624,35552,35058,35365,47363,30358,853,21719,20568,16211,49372,6087,16111,38337,38891,10792,4556,27171,37731,38587,40402,26109,1477,36932,12191,5459,49307,21132,8697,4131,47869,49246,30447,23795,14389,19743,1369,15689,1820,7826,50623,2179,28090,46430,40117,32603,6886,35318,1026,6991,21360,50370,21721,33558,39162,49753,17974,45599,23256,8483,20864,33426,990,10068,38,13186,19338,43727,12319,50658,19243,6267,12498,12214,542,43339,5933,11376,49748,9335,22248,14763,13375,48554,50595,27006,43481,2805,49012,20000,849,27214,38576,36449,39854,13708,26841,3837,39971,5090,35680,49468,24738,27145,15380,40463,1250,22007,20962,8747,1383,45856,20025,35346,17121,3387,42172,5340,13004,11554,4607,16991,45034,20212,48020,19356,41234,30633,33657,1508,30660,35022,41408,19213,32748,48274,7335,50376,36496,1217,5701,37507,23706,25798,15126,663,7699,36665,18675,32723,17437,24179,15565,26280,50598,44490,44275,24679,34843,50,11995,50661,5002,47661,14505,32048,46696,10699,13889,8921,4567,7012,832,2401,5055,33306,9385,50169,49990,42236
)
ORDER BY "a"."tmstmp" DESC,
         "a"."id" DESC
LIMIT 20;

 Limit  (cost=641000.51..641000.56 rows=20 width=12) (actual time=80813.649..80813.661 rows=20 loops=1)
   Buffers: shared hit=11926 read=93553 dirtied=2063 written=27769
   ->  Sort  (cost=641000.51..642534.60 rows=613634 width=12) (actual time=80813.647..80813.652 rows=20 loops=1)
         Sort Key: a_partition1.tmstmp DESC, a_partition1.id DESC
         Sort Method: top-N heapsort  Memory: 25kB
         Buffers: shared hit=11926 read=93553 dirtied=2063 written=27769
         ->  Append  (cost=0.43..624671.93 rows=613634 width=12) (actual time=2.244..80715.314 rows=104279 loops=1)
               Buffers: shared hit=11926 read=93553 dirtied=2063 written=27769
               ->  Index Scan using a_parent_id_idx1 on a_partition1  (cost=0.43..304861.89 rows=300407 width=12) (actual time=2.243..34766.236 rows=46309 loops=1)
                     Index Cond: (parent_id = ANY ('{49694,35150,48216,45743,49529,47239,40752,46735,7692,33855,25062,47821,6947,9735,12611,33190,9523,39200,50056,14912,9624,35552,35058,35365,47363,30358,853,21719,20568,16211,49372,6087,16111,38337,38891,10792,4556,27171,37731,38587,40402,26109,1477,36932,12191,5459,49307,21132,8697,4131,47869,49246,30447,23795,14389,19743,1369,15689,1820,7826,50623,2179,28090,46430,40117,32603,6886,35318,1026,6991,21360,50370,21721,33558,39162,49753,17974,45599,23256,8483,20864,33426,990,10068,38,13186,19338,43727,12319,50658,19243,6267,12498,12214,542,43339,5933,11376,49748,9335,22248,14763,13375,48554,50595,27006,43481,2805,49012,20000,849,27214,38576,36449,39854,13708,26841,3837,39971,5090,35680,49468,24738,27145,15380,40463,1250,22007,20962,8747,1383,45856,20025,35346,17121,3387,42172,5340,13004,11554,4607,16991,45034,20212,48020,19356,41234,30633,33657,1508,30660,35022,41408,19213,32748,48274,7335,50376,36496,1217,5701,37507,23706,25798,15126,663,7699,36665,18675,32723,17437,24179,15565,26280,50598,44490,44275,24679,34843,50,11995,50661,5002,47661,14505,32048,46696,10699,13889,8921,4567,7012,832,2401,5055,33306,9385,50169,49990,42236}'::integer[]))
                     Buffers: shared hit=9485 read=35740 dirtied=2033 written=12713
               ->  Index Scan using a_parent_id_idx2 on a_partition2  (cost=0.43..80641.75 rows=75349 width=12) (actual time=8.280..12640.675 rows=16628 loops=1)
                     Index Cond: (parent_id = ANY ('{49694,35150,48216,45743,49529,47239,40752,46735,7692,33855,25062,47821,6947,9735,12611,33190,9523,39200,50056,14912,9624,35552,35058,35365,47363,30358,853,21719,20568,16211,49372,6087,16111,38337,38891,10792,4556,27171,37731,38587,40402,26109,1477,36932,12191,5459,49307,21132,8697,4131,47869,49246,30447,23795,14389,19743,1369,15689,1820,7826,50623,2179,28090,46430,40117,32603,6886,35318,1026,6991,21360,50370,21721,33558,39162,49753,17974,45599,23256,8483,20864,33426,990,10068,38,13186,19338,43727,12319,50658,19243,6267,12498,12214,542,43339,5933,11376,49748,9335,22248,14763,13375,48554,50595,27006,43481,2805,49012,20000,849,27214,38576,36449,39854,13708,26841,3837,39971,5090,35680,49468,24738,27145,15380,40463,1250,22007,20962,8747,1383,45856,20025,35346,17121,3387,42172,5340,13004,11554,4607,16991,45034,20212,48020,19356,41234,30633,33657,1508,30660,35022,41408,19213,32748,48274,7335,50376,36496,1217,5701,37507,23706,25798,15126,663,7699,36665,18675,32723,17437,24179,15565,26280,50598,44490,44275,24679,34843,50,11995,50661,5002,47661,14505,32048,46696,10699,13889,8921,4567,7012,832,2401,5055,33306,9385,50169,49990,42236}'::integer[]))
                     Buffers: shared hit=558 read=16625 dirtied=8 written=6334
               ->  Index Scan using a_parent_id_idx3 on a_partition3  (cost=0.43..57551.91 rows=65008 width=12) (actual time=3.721..13759.664 rows=12973 loops=1)
                     Index Cond: (parent_id = ANY ('{49694,35150,48216,45743,49529,47239,40752,46735,7692,33855,25062,47821,6947,9735,12611,33190,9523,39200,50056,14912,9624,35552,35058,35365,47363,30358,853,21719,20568,16211,49372,6087,16111,38337,38891,10792,4556,27171,37731,38587,40402,26109,1477,36932,12191,5459,49307,21132,8697,4131,47869,49246,30447,23795,14389,19743,1369,15689,1820,7826,50623,2179,28090,46430,40117,32603,6886,35318,1026,6991,21360,50370,21721,33558,39162,49753,17974,45599,23256,8483,20864,33426,990,10068,38,13186,19338,43727,12319,50658,19243,6267,12498,12214,542,43339,5933,11376,49748,9335,22248,14763,13375,48554,50595,27006,43481,2805,49012,20000,849,27214,38576,36449,39854,13708,26841,3837,39971,5090,35680,49468,24738,27145,15380,40463,1250,22007,20962,8747,1383,45856,20025,35346,17121,3387,42172,5340,13004,11554,4607,16991,45034,20212,48020,19356,41234,30633,33657,1508,30660,35022,41408,19213,32748,48274,7335,50376,36496,1217,5701,37507,23706,25798,15126,663,7699,36665,18675,32723,17437,24179,15565,26280,50598,44490,44275,24679,34843,50,11995,50661,5002,47661,14505,32048,46696,10699,13889,8921,4567,7012,832,2401,5055,33306,9385,50169,49990,42236}'::integer[]))
                     Buffers: shared hit=592 read=12943 dirtied=2 written=3136
               ->  Index Scan using a_parent_id_idx4 on a_partition4  (cost=0.43..70062.42 rows=67402 width=12) (actual time=5.999..5949.033 rows=7476 loops=1)
                     Index Cond: (parent_id = ANY ('{49694,35150,48216,45743,49529,47239,40752,46735,7692,33855,25062,47821,6947,9735,12611,33190,9523,39200,50056,14912,9624,35552,35058,35365,47363,30358,853,21719,20568,16211,49372,6087,16111,38337,38891,10792,4556,27171,37731,38587,40402,26109,1477,36932,12191,5459,49307,21132,8697,4131,47869,49246,30447,23795,14389,19743,1369,15689,1820,7826,50623,2179,28090,46430,40117,32603,6886,35318,1026,6991,21360,50370,21721,33558,39162,49753,17974,45599,23256,8483,20864,33426,990,10068,38,13186,19338,43727,12319,50658,19243,6267,12498,12214,542,43339,5933,11376,49748,9335,22248,14763,13375,48554,50595,27006,43481,2805,49012,20000,849,27214,38576,36449,39854,13708,26841,3837,39971,5090,35680,49468,24738,27145,15380,40463,1250,22007,20962,8747,1383,45856,20025,35346,17121,3387,42172,5340,13004,11554,4607,16991,45034,20212,48020,19356,41234,30633,33657,1508,30660,35022,41408,19213,32748,48274,7335,50376,36496,1217,5701,37507,23706,25798,15126,663,7699,36665,18675,32723,17437,24179,15565,26280,50598,44490,44275,24679,34843,50,11995,50661,5002,47661,14505,32048,46696,10699,13889,8921,4567,7012,832,2401,5055,33306,9385,50169,49990,42236}'::integer[]))
                     Buffers: shared hit=449 read=7665 dirtied=10 written=1242
               ->  Index Scan using a_parent_id_idx5 on a_partition5  (cost=0.43..111553.96 rows=105468 width=12) (actual time=7.806..13519.564 rows=20893 loops=1)
                     Index Cond: (parent_id = ANY ('{49694,35150,48216,45743,49529,47239,40752,46735,7692,33855,25062,47821,6947,9735,12611,33190,9523,39200,50056,14912,9624,35552,35058,35365,47363,30358,853,21719,20568,16211,49372,6087,16111,38337,38891,10792,4556,27171,37731,38587,40402,26109,1477,36932,12191,5459,49307,21132,8697,4131,47869,49246,30447,23795,14389,19743,1369,15689,1820,7826,50623,2179,28090,46430,40117,32603,6886,35318,1026,6991,21360,50370,21721,33558,39162,49753,17974,45599,23256,8483,20864,33426,990,10068,38,13186,19338,43727,12319,50658,19243,6267,12498,12214,542,43339,5933,11376,49748,9335,22248,14763,13375,48554,50595,27006,43481,2805,49012,20000,849,27214,38576,36449,39854,13708,26841,3837,39971,5090,35680,49468,24738,27145,15380,40463,1250,22007,20962,8747,1383,45856,20025,35346,17121,3387,42172,5340,13004,11554,4607,16991,45034,20212,48020,19356,41234,30633,33657,1508,30660,35022,41408,19213,32748,48274,7335,50376,36496,1217,5701,37507,23706,25798,15126,663,7699,36665,18675,32723,17437,24179,15565,26280,50598,44490,44275,24679,34843,50,11995,50661,5002,47661,14505,32048,46696,10699,13889,8921,4567,7012,832,2401,5055,33306,9385,50169,49990,42236}'::integer[]))
                     Buffers: shared hit=842 read=20580 dirtied=10 written=4344
 Planning time: 8.366 ms
 Execution time: 80813.714 ms
(25 rows)


Though it still finishes, I'd really like to speed this up, especially because there are many situations where I will be using many more than the 200 "parent_id"s above.


I'd be very grateful for help with one or both of these questions:
1) Why is adding an unnecessary (from the perspective of result correctness) ORDER BY valuable for forcing the parent_id index usage, and does that indicate that there is something wrong with my table/indexes/statistics/etc.?
2) Is there any way I can improve my query time when there are many "parent_id"s involved? I seem to only be able to get the query plan to use at most one of the parent_id index and the tmstmp index at a time. Perhaps the correct multicolumn index would help?

A couple of notes (happy to be scolded for either of these things if they're problematic):
1) I've masked these queries somewhat for privacy reasons and cleaned up the table "a" of some extra fields and indexes.
2) I'm using different sets of "parent_id"s to try to overcome caching so the BUFFERS aspect can be as accurate as possible.

As for other maybe relevant information:

- version: 10.3
- hardware is AWS so probably not huge issue.
- work_mem is quite high (order of GBs)


Thanks in advance for your help!


[Postgresql General]     [Postgresql PHP]     [PHP Users]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Yosemite]

  Powered by Linux