PostgreSQL "group by" with "order by" and "limit" works unexpectedly

Viewed 228

I have a PostgreSQL 13 DB and I'm investigating perfomance of a one big query with multiple joins and found that "group by" togeher with "order by" and "limit" works unexpectedly to me.

I've tracked it down to the most simple case so to run my queries you can create a table

create table test(id serial primary key, field text);
insert into test(field) select md5(random()::text) from generate_series(1, 10000);

And lets add an index

create index test_field_index on test(field);
  1. Test index
    explain analyze 
    select * from test
    order by field
    limit 25;
 Limit  (cost=0.29..2.24 rows=25 width=37) (actual time=0.048..0.082 rows=25 loops=1)
   ->  Index Scan using test_field_index on test  (cost=0.29..782.28 rows=10000 width=37) (actual time=0.047..0.076 rows=25 loops=1)
 Planning Time: 0.339 ms
 Execution Time: 0.099 ms

All is good.

  1. Add group by field
explain analyze 
select * from test
group by id
order by field
limit 25;
Limit  (cost=591.19..591.26 rows=25 width=37) (actual time=11.125..11.130 rows=25 loops=1)
   ->  Sort  (cost=591.19..616.19 rows=10000 width=37) (actual time=11.124..11.126 rows=25 loops=1)
         Sort Key: field
         Sort Method: top-N heapsort  Memory: 28kB
         ->  HashAggregate  (cost=209.00..309.00 rows=10000 width=37) (actual time=6.267..8.303 rows=10000 loops=1)
               Group Key: id
               Batches: 1  Memory Usage: 1425kB
               ->  Seq Scan on test  (cost=0.00..184.00 rows=10000 width=37) (actual time=0.008..1.685 rows=10000 loops=1)
 Planning Time: 0.120 ms
 Execution Time: 11.218 ms

See that "group by" ignores "limit" and "order by" doesn't use index.

  1. Try to order by id instead
explain analyze 
select * from test
group by id
order by id
limit 25;
Limit  (cost=0.29..1.24 rows=25 width=37) (actual time=0.018..0.040 rows=25 loops=1)
   ->  Group  (cost=0.29..382.29 rows=10000 width=37) (actual time=0.017..0.035 rows=25 loops=1)
         Group Key: id
         ->  Index Scan using test_pkey on test  (cost=0.29..357.29 rows=10000 width=37) (actual time=0.014..0.023 rows=25 loops=1)
 Planning Time: 0.099 ms
 Execution Time: 0.062 ms

Looks good again.

  1. Order by id and field together
explain analyze 
select * from test
group by id
order by id, field
limit 25;
 Limit  (cost=0.33..2.41 rows=25 width=37) (actual time=0.033..0.036 rows=25 loops=1)
   ->  Incremental Sort  (cost=0.33..832.29 rows=10000 width=37) (actual time=0.032..0.034 rows=25 loops=1)
         Sort Key: id, field
         Presorted Key: id
         Full-sort Groups: 1  Sort Method: quicksort  Average Memory: 26kB  Peak Memory: 26kB
         ->  Group  (cost=0.29..382.29 rows=10000 width=37) (actual time=0.011..0.021 rows=26 loops=1)
               Group Key: id
               ->  Index Scan using test_pkey on test  (cost=0.29..357.29 rows=10000 width=37) (actual time=0.010..0.014 rows=26 loops=1)
 Planning Time: 0.072 ms
 Execution Time: 0.052 ms

Much better than [2], but anyway "order by" still doesn't use index on field.

  1. Try to reverse fields in "order by"
explain analyze 
select * from test
group by id
order by field, id
limit 25;
 Limit  (cost=591.19..591.26 rows=25 width=37) (actual time=11.488..11.493 rows=25 loops=1)
   ->  Sort  (cost=591.19..616.19 rows=10000 width=37) (actual time=11.487..11.489 rows=25 loops=1)
         Sort Key: field, id
         Sort Method: top-N heapsort  Memory: 28kB
         ->  HashAggregate  (cost=209.00..309.00 rows=10000 width=37) (actual time=5.838..8.118 rows=10000 loops=1)
               Group Key: id
               Batches: 1  Memory Usage: 1425kB
               ->  Seq Scan on test  (cost=0.00..184.00 rows=10000 width=37) (actual time=0.008..1.349 rows=10000 loops=1)
 Planning Time: 0.105 ms
 Execution Time: 11.562 ms

The same as [2] - it ignores all indexes and limit.

  1. Try [5] with composite index
create index test_field_id_index on test(field, id);

Nothing has changed at all. Actually I added all 3 possible indexes and it doesn't change nothing.

With my real original query on the production DB the difference between [4] and [6] is ~11ms vs ~1200ms. So for now I just put id column as the first one in "order by" and got huge improvement but my first attempt was to fix it like [6] and I was surprised that it doesn't help.

So my questions are:

  1. Why doesn't "order by" use index for the columns different from id if "group by" is present?
  2. Why does "group by" ignore "limit" in cases [2] and [5]?
  3. Why wasn't new index used in [6]?
  4. Why does "group by" use 26 rows in [4]? I guess it needs to do n+1 but just wondering why, since in [3] it is not so?
1 Answers

See that "group by" ignores "limit" and "order by" doesn't use index.

This is how it should be. all the groups need to be created before they can be ordered and limited.

Much better than [2], but anyway "order by" still doesn't use index on field.

that wouldn't help performance, the id index is sufficient because it's a unique index. filed has no information that could change the order.

Related