On 14 October 2017 at 09:04, Robert Haas <robertmh...@gmail.com> wrote: > On Mon, Oct 9, 2017 at 11:03 PM, David Rowley > <david.row...@2ndquadrant.com> wrote: >> -- Unpatched >> Planning time: 0.184 ms >> Execution time: 105.878 ms >> >> -- Patched >> Planning time: 2.175 ms >> Execution time: 106.326 ms > > This might not be the best example to show the advantages of the > patch, honestly.
The focus was on the row estimate. I try to highlight that by mentioning "Note rows=1 vs rows=98 in the Gather node.". I can't imagine the test I added would have made the planner about 12 times slower, but just for the record: create table ab (a varchar, b varchar); insert into ab select (x%1000)::varchar, (x%10000)::Varchar from generate_Series(1,1000000)x; create statistics ab_a_b_stats (dependencies) on a,b from ab; vacuum analyze ab; $ cat a.sql explain select * from ab where a = '1' and b = '1'; e9ef11ac8bb2acc2d2462fc17ec3291a959589e7 (Patched) $ pgbench -f a.sql -T 60 -n transaction type: a.sql scaling factor: 1 query mode: simple number of clients: 1 number of threads: 1 duration: 60 s number of transactions actually processed: 496950 latency average = 0.121 ms tps = 8282.481310 (including connections establishing) tps = 8282.750821 (excluding connections establishing) e9ef11ac8bb2acc2d2462fc17ec3291a959589e7~1 (Unpatched) $ pgbench -f a.sql -T 60 -n transaction type: a.sql scaling factor: 1 query mode: simple number of clients: 1 number of threads: 1 duration: 60 s number of transactions actually processed: 511250 latency average = 0.117 ms tps = 8520.822410 (including connections establishing) tps = 8521.132784 (excluding connections establishing) With the patch we are making use of the extended statistics, which we do expect to be more work for the planner. Although, we didn't add extended statistics to speed up the planner. -- David Rowley http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training & Services -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers