2015-02-22 13:22 GMT+01:00 Andres Freund <and...@2ndquadrant.com>: > On 2015-02-22 10:33:16 +0000, Andrew Gierth wrote: > > This is, if I'm understanding the planner logic right, physical-tlist > > optimization; it's faster for a table scan to simply return the whole > > row (copying nothing, just pointing to the on-disk tuple) and let > > hashagg pick out the columns it needs, rather than for the scan to run a > > projection step just to select specific columns. > > > > If there's a Sort step, this isn't done because Sort neither evaluates > > its input nor projects new tuples on its output, it simply accepts the > > tuples it receives and returns them with the same structure. So now it's > > important to have the node providing input to the Sort projecting out > > only the minimum required set of columns. > > > > Why it's slower on the wider table... that's less obvious. > > It's likely to just be tuple deforming. I've not tried it but I'd bet > you'll see slot_deform* very high in the profile. For the narrow table > only two attributes need to be extracted, for the wider one everything > up to a11 will get extracted. > > I've wondered before if we shouldn't use the caching via > slot->tts_values so freely - if you only use a couple values from a wide > tuple the current implementation really sucks if those few aren't at the > beginning of the tuple. >
the number of columns has strong effect, but it is not only one. I tested first two columns, and bigger tables is aggregated slowly - about 30% postgres=# explain analyze select count(*), a1, a2 from t1 group by 3,2 order by 3,2; QUERY PLAN --------------------------------------------------------------------------------------------------------------------------------- Sort (cost=2023263.19..2023263.25 rows=24 width=4) (actual time=84073.451..84073.452 rows=24 loops=1) Sort Key: a2, a1 Sort Method: quicksort Memory: 26kB -> HashAggregate (cost=2023262.40..2023262.64 rows=24 width=4) (actual time=84073.430..84073.433 rows=24 loops=1) -- 23700 Group Key: a2, a1 -> Seq Scan on t1 (cost=0.00..1497532.80 rows=70097280 width=4) (actual time=67.325..60152.052 rows=70097280 loops=1) Planning time: 0.107 ms Execution time: 84073.534 ms (8 rows) postgres=# explain analyze select count(*), a1, a2 from t2 group by 3,2 order by 3,2; QUERY PLAN ------------------------------------------------------------------------------------------------------------------------------- Sort (cost=1536868.33..1536868.39 rows=24 width=4) (actual time=21963.230..21963.231 rows=24 loops=1) Sort Key: a2, a1 Sort Method: quicksort Memory: 26kB -> HashAggregate (cost=1536867.54..1536867.78 rows=24 width=4) (actual time=21963.209..21963.213 rows=24 loops=1) -- 16000 Group Key: a2, a1 -> Seq Scan on t2 (cost=0.00..1011137.88 rows=70097288 width=4) (actual time=0.063..5647.404 rows=70097280 loops=1) Planning time: 0.069 ms Execution time: 21963.340 ms (8 rows) Profile when data are in first two columns 7.87% postgres [.] slot_deform_tuple 7.48% postgres [.] slot_getattr 7.10% postgres [.] hash_search_with_hash_value 3.74% postgres [.] execTuplesMatch 3.68% postgres [.] ExecAgg Profile when data are in first and 11 column 20.35% postgres [.] slot_deform_tuple 6.55% postgres [.] hash_search_with_hash_value 5.86% postgres [.] slot_getattr 4.15% postgres [.] ExecAgg So your hypothesis is valid Regards Pavel > > Greetings, > > Andres Freund > > -- > Andres Freund http://www.2ndQuadrant.com/ > PostgreSQL Development, 24x7 Support, Training & Services >