[ reviving an old multivariate statistics thread ] On Thu, Nov 13, 2014 at 6:31 AM, Simon Riggs <si...@2ndquadrant.com> wrote: > On 12 October 2014 23:00, Tomas Vondra <t...@fuzzy.cz> wrote: > >> It however seems to be working sufficiently well at this point, enough >> to get some useful feedback. So here we go. > > This looks interesting and useful. > > What I'd like to check before a detailed review is that this has > sufficient applicability to be useful. > > My understanding is that Q9 and Q18 of TPC-H have poor plans as a > result of multi-column stats errors. > > Could you look at those queries and confirm that this patch can > produce better plans for them?
Tomas, did you ever do any testing in this area? One of my colleagues, Rafia Sabih, recently did some testing of TPC-H queries @ 20 GB. Q18 actually doesn't complete at all right now because of an issue with the new simplehash implementation. I reported it to Andres and he tracked it down, but hasn't posted the patch yet - see http://archives.postgresql.org/message-id/20161115192802.jfbec5s6ougxw...@alap3.anarazel.de Of the remaining queries, the slowest are Q9 and Q20, and both of them have serious estimation errors. On Q9, things go wrong here: -> Merge Join (cost=5225092.04..6595105.57 rows=154 width=47) (actual time=103592.821..149335.010 rows=6503988 loops=1) Merge Cond: (partsupp.ps_partkey = lineitem.l_partkey) Join Filter: (lineitem.l_suppkey = partsupp.ps_suppkey) Rows Removed by Join Filter: 19511964 -> Index Scan using idx_partsupp_partkey on partsupp (cost=0.43..781956.32 rows=15999792 width=22) (actual time=0.044..11825.481 rows=15999881 loops=1) -> Sort (cost=5224967.03..5245348.02 rows=8152396 width=45) (actual time=103592.505..112205.444 rows=26015949 loops=1) Sort Key: part.p_partkey Sort Method: quicksort Memory: 704733kB -> Hash Join (cost=127278.36..4289121.18 rows=8152396 width=45) (actual time=1084.370..94732.951 rows=6503988 loops=1) Hash Cond: (lineitem.l_partkey = part.p_partkey) -> Seq Scan on lineitem (cost=0.00..3630339.08 rows=119994608 width=41) (actual time=0.015..33355.637 rows=119994608 loops=1) -> Hash (cost=123743.07..123743.07 rows=282823 width=4) (actual time=1083.686..1083.686 rows=216867 loops=1) Buckets: 524288 Batches: 1 Memory Usage: 11721kB -> Gather (cost=1000.00..123743.07 rows=282823 width=4) (actual time=0.418..926.283 rows=216867 loops=1) Workers Planned: 4 Workers Launched: 4 -> Parallel Seq Scan on part (cost=0.00..94460.77 rows=70706 width=4) (actual time=0.063..962.909 rows=43373 loops=5) Filter: ((p_name)::text ~~ '%grey%'::text) Rows Removed by Filter: 756627 The estimate for the index scan on partsupp is essentially perfect, and the lineitem-part join is off by about 3x. However, the merge join is off by about 4000x, which is real bad. On Q20, things go wrong here: -> Merge Join (cost=5928271.92..6411281.44 rows=278 width=16) (actual time=77887.963..136614.284 rows=118124 loops=1) Merge Cond: ((lineitem.l_partkey = partsupp.ps_partkey) AND (lineitem.l_suppkey = partsupp.ps_suppkey)) Join Filter: ((partsupp.ps_availqty)::numeric > ((0.5 * sum(lineitem.l_quantity)))) Rows Removed by Join Filter: 242 -> GroupAggregate (cost=5363980.40..5691151.45 rows=9681876 width=48) (actual time=76672.726..131482.677 rows=10890067 loops=1) Group Key: lineitem.l_partkey, lineitem.l_suppkey -> Sort (cost=5363980.40..5409466.13 rows=18194291 width=21) (actual time=76672.661..86405.882 rows=18194084 loops=1) Sort Key: lineitem.l_partkey, lineitem.l_suppkey Sort Method: external merge Disk: 551376kB -> Bitmap Heap Scan on lineitem (cost=466716.05..3170023.42 rows=18194291 width=21) (actual time=13735.552..39289.995 rows=18195269 loops=1) Recheck Cond: ((l_shipdate >= '1994-01-01'::date) AND (l_shipdate < '1995-01-01 00:00:00'::timestamp without time zone)) Heap Blocks: exact=2230011 -> Bitmap Index Scan on idx_lineitem_shipdate (cost=0.00..462167.48 rows=18194291 width=0) (actual time=11771.173..11771.173 rows=18195269 loops=1) Index Cond: ((l_shipdate >= '1994-01-01'::date) AND (l_shipdate < '1995-01-01 00:00:00'::timestamp without time zone)) -> Sort (cost=564291.52..567827.56 rows=1414417 width=24) (actual time=1214.812..1264.356 rows=173936 loops=1) Sort Key: partsupp.ps_partkey, partsupp.ps_suppkey Sort Method: quicksort Memory: 19733kB -> Nested Loop (cost=1000.43..419796.26 rows=1414417 width=24) (actual time=0.447..985.562 rows=173936 loops=1) -> Gather (cost=1000.00..99501.07 rows=40403 width=4) (actual time=0.390..34.476 rows=43484 loops=1) Workers Planned: 4 Workers Launched: 4 -> Parallel Seq Scan on part (cost=0.00..94460.77 rows=10101 width=4) (actual time=0.143..527.665 rows=8697 loops=5) Filter: ((p_name)::text ~~ 'beige%'::text) Rows Removed by Filter: 791303 -> Index Scan using idx_partsupp_partkey on partsupp (cost=0.43..7.58 rows=35 width=20) (actual time=0.017..0.019 rows=4 loops=43484) Index Cond: (ps_partkey = part.p_partkey) The estimate for the GroupAggregate feeding one side of the merge join is quite accurate. The estimate for the part-partsupp join on the other side is off by 8x. Then things get much worse: the estimate for the merge join is off by 400x. I'm not really sure whether the multivariate statistics stuff will fix this kind of case or not, but if it did it would be awesome. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers