On 1 December 2015 at 17:09, Marko Tiikkaja <ma...@joh.to> wrote: > On 2015-12-01 05:00, David Rowley wrote: > >> We already allow a SELECT's target list to contain non-aggregated columns >> in a GROUP BY query in cases where the non-aggregated column is >> functionally dependent on the GROUP BY clause. >> >> For example a query such as; >> >> SELECT p.product_id,p.description, SUM(s.quantity) >> FROM product p >> INNER JOIN sale s ON p.product_id = s.product_id >> GROUP BY p.product_id; >> >> is perfectly fine in PostgreSQL, as p.description is functionally >> dependent >> on p.product_id (assuming product_id is the PRIMARY KEY of product). >> > > This has come up before (on other forums, at least), and my main concern > has been that unlike the case where we go from throwing an error to > allowing a query, this has a chance to make the planning of currently legal > queries slower. Have you tried to measure the impact of this on queries > where there's no runtime gains to be had?
I've performed a series of benchmarks on the following queries: Test1: explain select id1,id2 from t1 group by id1,id2; Test2: explain select id from t2 group by id; Test3: explain select t1.id1,t1.id2 from t2 inner join t1 on t1.id1=t2.id group by t1.id1,t1.id2; I ran each of these with pgbench for 60 seconds, 3 runs per query. In each case below I've converted the TPS into seconds using the average TPS over the 3 runs. In summary: Test1 is the worst case test. It's a very simple query so planning overhead of join searching is non-existent. The fact that there's 2 columns in the GROUP BY means that the fast path cannot be used. I added this as if there's only 1 column in the GROUP BY then there's no point in searching for something to remove. Average (Sec) Master 0.0001043117 Patched 0.0001118961 Performance 93.22% Microseconds of planning overhead 7.5844326722 Test2 is a simple query with a GROUP BY which can fast path due to there being only 1 GROUP BY column. Average (Sec) Master 0.000099374448 Patched 0.000099670124 Performance 99.70% Microseconds of planning overhead 0.2956763193 Test3 is a slightly more complex and is aimed to show that the percentage of planning overhead is smaller when joins exist and overall planning cost becomes higher Average (Sec) Master 0.0001797165 Patched 0.0001798406 Performance 99.93% Microseconds of planning overhead 0.1240776236 Test3 results seem a bit strange, I would have expected more of a slowdown. I ran the test again to make sure, and it came back with the same results the 2nd time. I've attached the spreadsheet that used to collect the results, and also the raw pgbench output. It seems that the worst case test adds about 7.6 microseconds onto planning time. To get this worse case result I had to add two GROUP BY columns, as having only 1 triggers a fast path as the code knows it can't remove any columns, since there's only 1. A similar fast path also exists which will only lookup the PRIMARY KEY details if there's more than 1 column per relation in the GROUP BY, so for example GROUP BY rel1.col1, rel2.col1 won't lookup any PRIMARY KEY constraint. Given that the extra code really only does anything if the GROUP BY has 2 or more expressions, are you worried that this will affect too many short and fast to execute queries negatively? -- David Rowley http://www.2ndQuadrant.com/ <http://www.2ndquadrant.com/> PostgreSQL Development, 24x7 Support, Training & Services
$ psql -c "create table t1 (id1 int, id2 int, primary key(id1,id2));" $ psql -c "create table t2 (id int primary key);" $ echo explain select id1,id2 from t1 group by id1,id2 > test1.sql $ echo explain select id from t2 group by id > test2.sql $ echo explain select t1.id1,t1.id2 from t2 inner join t1 on t1.id1=t2.id group by t1.id1,t1.id2 > test3.sql Unpatched $ pgbench -n -T 60 -f test1.sql transaction type: Custom query scaling factor: 1 query mode: simple number of clients: 1 number of threads: 1 duration: 60 s number of transactions actually processed: 575640 latency average: 0.104 ms tps = 9593.939398 (including connections establishing) tps = 9594.358669 (excluding connections establishing) $ pgbench -n -T 60 -f test1.sql transaction type: Custom query scaling factor: 1 query mode: simple number of clients: 1 number of threads: 1 duration: 60 s number of transactions actually processed: 574034 latency average: 0.105 ms tps = 9567.199370 (including connections establishing) tps = 9567.619227 (excluding connections establishing) $ pgbench -n -T 60 -f test1.sql transaction type: Custom query scaling factor: 1 query mode: simple number of clients: 1 number of threads: 1 duration: 60 s number of transactions actually processed: 575861 latency average: 0.104 ms tps = 9597.621429 (including connections establishing) tps = 9597.987750 (excluding connections establishing) $ pgbench -n -T 60 -f test2.sql transaction type: Custom query scaling factor: 1 query mode: simple number of clients: 1 number of threads: 1 duration: 60 s number of transactions actually processed: 607331 latency average: 0.099 ms tps = 10122.117202 (including connections establishing) tps = 10122.488864 (excluding connections establishing) $ pgbench -n -T 60 -f test2.sql transaction type: Custom query scaling factor: 1 query mode: simple number of clients: 1 number of threads: 1 duration: 60 s number of transactions actually processed: 607250 latency average: 0.099 ms tps = 10120.749500 (including connections establishing) tps = 10121.116893 (excluding connections establishing) $ pgbench -n -T 60 -f test2.sql transaction type: Custom query scaling factor: 1 query mode: simple number of clients: 1 number of threads: 1 duration: 60 s number of transactions actually processed: 596696 latency average: 0.101 ms tps = 9944.892891 (including connections establishing) tps = 9945.241139 (excluding connections establishing) $ pgbench -n -T 60 -f test3.sql transaction type: Custom query scaling factor: 1 query mode: simple number of clients: 1 number of threads: 1 duration: 60 s number of transactions actually processed: 335137 latency average: 0.179 ms tps = 5585.556063 (including connections establishing) tps = 5585.808260 (excluding connections establishing) $ pgbench -n -T 60 -f test3.sql transaction type: Custom query scaling factor: 1 query mode: simple number of clients: 1 number of threads: 1 duration: 60 s number of transactions actually processed: 332844 latency average: 0.180 ms tps = 5547.358857 (including connections establishing) tps = 5547.622552 (excluding connections establishing) $ pgbench -n -T 60 -f test3.sql transaction type: Custom query scaling factor: 1 query mode: simple number of clients: 1 number of threads: 1 duration: 60 s number of transactions actually processed: 333560 latency average: 0.180 ms tps = 5559.307483 (including connections establishing) tps = 5559.525508 (excluding connections establishing) Patched: $ pgbench -n -T 60 -f test1.sql transaction type: Custom query scaling factor: 1 query mode: simple number of clients: 1 number of threads: 1 duration: 60 s number of transactions actually processed: 538142 latency average: 0.111 ms tps = 8968.993272 (including connections establishing) tps = 8969.321697 (excluding connections establishing) $ pgbench -n -T 60 -f test1.sql transaction type: Custom query scaling factor: 1 query mode: simple number of clients: 1 number of threads: 1 duration: 60 s number of transactions actually processed: 536532 latency average: 0.112 ms tps = 8942.140535 (including connections establishing) tps = 8942.558895 (excluding connections establishing) $ pgbench -n -T 60 -f test1.sql transaction type: Custom query scaling factor: 1 query mode: simple number of clients: 1 number of threads: 1 duration: 60 s number of transactions actually processed: 533906 latency average: 0.112 ms tps = 8898.379053 (including connections establishing) tps = 8898.705040 (excluding connections establishing) $ pgbench -n -T 60 -f test2.sql transaction type: Custom query scaling factor: 1 query mode: simple number of clients: 1 number of threads: 1 duration: 60 s number of transactions actually processed: 606832 latency average: 0.099 ms tps = 10113.781374 (including connections establishing) tps = 10114.156100 (excluding connections establishing) $ pgbench -n -T 60 -f test2.sql transaction type: Custom query scaling factor: 1 query mode: simple number of clients: 1 number of threads: 1 duration: 60 s number of transactions actually processed: 599303 latency average: 0.100 ms tps = 9988.336721 (including connections establishing) tps = 9988.803529 (excluding connections establishing) $ pgbench -n -T 60 -f test2.sql transaction type: Custom query scaling factor: 1 query mode: simple number of clients: 1 number of threads: 1 duration: 60 s number of transactions actually processed: 599764 latency average: 0.100 ms tps = 9995.966540 (including connections establishing) tps = 9996.330570 (excluding connections establishing) $ pgbench -n -T 60 -f test3.sql transaction type: Custom query scaling factor: 1 query mode: simple number of clients: 1 number of threads: 1 duration: 60 s number of transactions actually processed: 331786 latency average: 0.181 ms tps = 5529.730263 (including connections establishing) tps = 5529.957450 (excluding connections establishing) $ pgbench -n -T 60 -f test3.sql transaction type: Custom query scaling factor: 1 query mode: simple number of clients: 1 number of threads: 1 duration: 60 s number of transactions actually processed: 334874 latency average: 0.179 ms tps = 5581.180591 (including connections establishing) tps = 5581.387008 (excluding connections establishing) $ pgbench -n -T 60 -f test3.sql transaction type: Custom query scaling factor: 1 query mode: simple number of clients: 1 number of threads: 1 duration: 60 s number of transactions actually processed: 334198 latency average: 0.180 ms tps = 5569.911710 (including connections establishing) tps = 5570.094872 (excluding connections establishing)
Benchmark.ods
Description: application/vnd.oasis.opendocument.spreadsheet
-- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
