Wrong plan for subSELECT with GROUP BY - Mailing list pgsql-performance

Hi!

See the next case, please! This is a theoretical CASE, which cause
problems to me. Please, help!

CREATE TABLE a (
    id SERIAL,    -- This is the PRIMARY KEY
   col TEXT
);

CREATE TABLE b (
    id SERIAL,  -- This is the PRIMARY KEY
    a_id INT4,   -- REFERENCE TO a.id
    value INT4,
    txt TEXT
);

CREATE TABLE c (
    id SERIAL,  -- This is the PRIMARY KEY
    a_id INT4,   -- REFERENCE TO a.id
    value INT4,
    txt TEXT
);

I examined the next query. In my opinion its query plan is not too optimal.
There are indexes on b.a_id, b.value, b.txt, c.a_id, c.value, c.txt.
I generated test datas: 10000 rows into the table 'a',
                                  100000 rows into the table 'b',
                                  100000 rows into the table 'c'.

SELECT  a.id, a.col,
               TABLE_B.minval, TABLE_B.b_txt,
               TABLE_C.maxval, TABLE_C.c_txt
FROM a
LEFT JOIN (
    SELECT a_id,
                   min(value) AS minval,
                   max(txt) AS b_txt
    FROM b
    GROUP BY a_id) TABLE_B ON (TABLE_B.a_id = a.id)
LEFT JOIN (
    SELECT a_id,
                   max(value) AS maxval,
                   min(txt) AS c_txt
    FROM c
    GROUP BY a_id) TABLE_C ON (TABLE_C.a_id = a.id)
LIMIT 10;

                                                                  QUERY PLAN

-----------------------------------------------------------------------------------------------------------------------------------------------
 Limit  (cost=6907.56..6932.86 rows=10 width=84) (actual
time=750.075..750.184 rows=10 loops=1)
   ->  Hash Left Join  (cost=6907.56..34114.19 rows=10753 width=84)
(actual time=750.071..750.163 rows=10 loops=1)
         Hash Cond: ("outer".id = "inner".a_id)
         ->  Merge Left Join  (cost=4171.85..4370.95 rows=10000
width=48) (actual time=400.712..400.775 rows=10 loops=1)
               Merge Cond: ("outer".id = "inner".a_id)
               ->  Sort  (cost=823.39..848.39 rows=10000 width=12)
(actual time=31.926..31.935 rows=10 loops=1)
                     Sort Key: a.id
                     ->  Seq Scan on a  (cost=0.00..159.00 rows=10000
width=12) (actual time=0.013..12.120 rows=10000 loops=1)
               ->  Sort  (cost=3348.47..3373.32 rows=9940 width=40)
(actual time=368.741..368.762 rows=22 loops=1)
                     Sort Key: table_b.a_id
                     ->  Subquery Scan table_b  (cost=2440.00..2688.50
rows=9940 width=40) (actual time=305.836..338.764 rows=10000 loops=1)
                           ->  HashAggregate  (cost=2440.00..2589.10
rows=9940 width=17) (actual time=305.832..320.891 rows=10000 loops=1)
                                 ->  Seq Scan on b  (cost=0.00..1690.00
rows=100000 width=17) (actual time=0.012..125.888 rows=100000 loops=1)
         ->  Hash  (cost=2708.83..2708.83 rows=10753 width=40) (actual
time=349.314..349.314 rows=10000 loops=1)
               ->  Subquery Scan table_c  (cost=2440.00..2708.83
rows=10753 width=40) (actual time=298.826..331.239 rows=10000 loops=1)
                     ->  HashAggregate  (cost=2440.00..2601.30
rows=10753 width=17) (actual time=298.821..313.603 rows=10000 loops=1)
                           ->  Seq Scan on c  (cost=0.00..1690.00
rows=100000 width=17) (actual time=0.015..124.996 rows=100000 loops=1)
 Total runtime: 757.818 ms

But I can optimize the previous query by hand:

SELECT  a.id, a.col,
               (SELECT min(value) FROM b WHERE b.a_id=a.id) AS minval,
               (SELECT max(txt) FROM b WHERE b.a_id=a.id) AS b_txt,
               (SELECT max(value) FROM c WHERE c.a_id=a.id) AS maxval,
               (SELECT min(txt) FROM c WHERE c.a_id=a.id) AS c_txt
FROM a
LIMIT 10;

                                                                  QUERY PLAN

-----------------------------------------------------------------------------------------------------------------------------------------------
  Limit  (cost=0.00..126.76 rows=10 width=12) (actual time=0.221..1.754
rows=10 loops=1)
   ->  Seq Scan on a  (cost=0.00..126764.21 rows=10000 width=12) (actual
time=0.218..1.734 rows=10 loops=1)
         SubPlan
           ->  Aggregate  (cost=3.15..3.16 rows=1 width=9) (actual
time=0.039..0.039 rows=1 loops=10)
                 ->  Index Scan using idx_c_aid on c  (cost=0.00..3.12
rows=9 width=9) (actual time=0.007..0.024 rows=10 loops=10)
                       Index Cond: (a_id = $0)
           ->  Aggregate  (cost=3.15..3.16 rows=1 width=4) (actual
time=0.038..0.039 rows=1 loops=10)
                 ->  Index Scan using idx_c_aid on c  (cost=0.00..3.12
rows=9 width=4) (actual time=0.008..0.025 rows=10 loops=10)
                       Index Cond: (a_id = $0)
           ->  Aggregate  (cost=3.16..3.17 rows=1 width=9) (actual
time=0.038..0.039 rows=1 loops=10)
                 ->  Index Scan using idx_b_aid on b  (cost=0.00..3.14
rows=10 width=9) (actual time=0.006..0.022 rows=10 loops=10)
                       Index Cond: (a_id = $0)
           ->  Aggregate  (cost=3.16..3.17 rows=1 width=4) (actual
time=0.039..0.040 rows=1 loops=10)
                 ->  Index Scan using idx_b_aid on b  (cost=0.00..3.14
rows=10 width=4) (actual time=0.008..0.025 rows=10 loops=10)
                       Index Cond: (a_id = $0)
 Total runtime: 1.933 ms

There is huge difference between the query performances. Why?
My problem is that in the first query use HashAggregation on the all
table 'b' and 'c' and cannot take notice of the 'LIMIT 10'.
In my special case I cannot use the second query formalization. I
simplified my problem to this theoretical CASE.
Do you know why cannot make the best of  the 'LIMIT' criteria in the
first query?
These tables are big, so in my opinion the planner could optimize better.

If this is a deficiency of the planner, I'd like to suggest this feature
into the planner.

Regards,
  Antal Attila