X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [64.117.224.130]) by svr1.postgresql.org (Postfix) with ESMTP id 2A933D1B515 for ; Wed, 8 Oct 2003 00:02:00 +0000 (GMT) Received: from svr1.postgresql.org ([64.117.224.193]) by localhost (neptune.hub.org [64.117.224.130]) (amavisd-new, port 10024) with ESMTP id 24912-06 for ; Tue, 7 Oct 2003 21:01:14 -0300 (ADT) Received: from davinci.ethosmedia.com (unknown [209.128.84.228]) by svr1.postgresql.org (Postfix) with ESMTP id 63117D1B527 for ; Tue, 7 Oct 2003 21:01:10 -0300 (ADT) Received: from [66.219.92.2] (HELO temoku) by davinci.ethosmedia.com (CommuniGate Pro SMTP 4.0.2) with ESMTP id 3738819; Tue, 07 Oct 2003 17:01:48 -0700 Content-Type: text/plain; charset="iso-8859-1" From: Josh Berkus Reply-To: josh@agliodbs.com Organization: Aglio Database Solutions To: Oleg Lebedev , Tom Lane Subject: Re: TPC-R benchmarks Date: Tue, 7 Oct 2003 16:59:17 -0700 User-Agent: KMail/1.4.3 References: <993DBE5B4D02194382EC8DF8554A52731D7661@postoffice.waterford.org> In-Reply-To: <993DBE5B4D02194382EC8DF8554A52731D7661@postoffice.waterford.org> Cc: pgsql-performance@postgresql.org MIME-Version: 1.0 Content-Transfer-Encoding: quoted-printable Message-Id: <200310071659.17863.josh@agliodbs.com> X-Virus-Scanned: by amavisd-new at postgresql.org X-Archive-Number: 200310/195 X-Sequence-Number: 3943 Tom, I've found the problem with TPC-R query #19. And it, unfortunately, appear= s=20 to be a problem in the PostgreSQL query planner. To sum up the below: it appears that whenever a set of WHERE conditions=20 exceeds a certain level of complexity, the planner just ignores all=20 applicable indexes and goes for a seq scan. While this may be unavoidable= =20 to some degree, it seems to me that we need to raise the threshold of=20 complexity at which it does this. tpcr=3D# select version(); version ---------------------------------------------------------------------------= ------------------------------ PostgreSQL 7.3.4 on i686-pc-linux-gnu, compiled by GCC gcc (GCC) 3.3 20030= 226=20 (prerelease) (SuSE Linux) (1 row) I've tested a number of indexes on the query, and found the two most effici= ent=20 on subsets of the query. Thus: explain analyze select sum(l_extendedprice* (1 - l_discount)) as revenue from lineitem, part where ( p_partkey =3D l_partkey and p_brand =3D 'Brand#33' and p_container in ('SM CASE', 'SM BOX', 'SM PACK', 'SM PKG') and l_quantity >=3D 8 and l_quantity <=3D 8 + 10 and p_size between 1 and 5 and l_shipmode in ('AIR', 'AIR REG') and l_shipinstruct =3D 'DELIVER IN PERSON' ); =20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20= =20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20= =20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20= =20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20 QUERY PLAN ---------------------------------------------------------------------------= ---------------------------------------------------------------------------= -------------------------------------------------------- Aggregate (cost=3D10380.70..10380.70 rows=3D1 width=3D30) (actual=20 time=3D161.61..161.61 rows=3D1 loops=3D1) -> Nested Loop (cost=3D0.00..10380.67 rows=3D13 width=3D30) (actual=20 time=3D81.54..161.47 rows=3D17 loops=3D1) -> Index Scan using idx_part_1 on part (cost=3D0.00..9466.33 row= s=3D62=20 width=3D4) (actual time=3D81.21..137.24 rows=3D98 loops=3D1) Index Cond: (p_brand =3D 'Brand#33'::bpchar) Filter: (((p_container =3D 'SM CASE'::bpchar) OR (p_containe= r =3D=20 'SM BOX'::bpchar) OR (p_container =3D 'SM PACK'::bpchar) OR (p_container = =3D 'SM=20 PKG'::bpchar)) AND (p_size >=3D 1) AND (p_size <=3D 5)) -> Index Scan using idx_lineitem_3 on lineitem (cost=3D0.00..14.= 84=20 rows=3D1 width=3D26) (actual time=3D0.22..0.24 rows=3D0 loops=3D98) Index Cond: (("outer".p_partkey =3D lineitem.l_partkey) AND= =20 (lineitem.l_quantity >=3D 8::numeric) AND (lineitem.l_quantity <=3D 18::num= eric)) Filter: (((l_shipmode =3D 'AIR'::bpchar) OR (l_shipmode =3D = 'AIR=20 REG'::bpchar)) AND (l_shipinstruct =3D 'DELIVER IN PERSON'::bpchar)) Total runtime: 161.71 msec This also works for a similar query: explain analyze select sum(l_extendedprice* (1 - l_discount)) as revenue from lineitem, part where ( p_partkey =3D l_partkey and p_brand =3D 'Brand#52' and p_container in ('MED BAG', 'MED BOX', 'MED PKG', 'MED PACK') and l_quantity >=3D 14 and l_quantity <=3D 14 + 10 and p_size between 1 and 10 and l_shipmode in ('AIR', 'AIR REG') and l_shipinstruct =3D 'DELIVER IN PERSON' ); Aggregate (cost=3D11449.36..11449.36 rows=3D1 width=3D30) (actual=20 time=3D195.72..195.72 rows=3D1 loops=3D1) -> Nested Loop (cost=3D0.00..11449.29 rows=3D28 width=3D30) (actual=20 time=3D56.42..195.39 rows=3D48 loops=3D1) -> Index Scan using idx_part_1 on part (cost=3D0.00..9466.33 row= s=3D139=20 width=3D4) (actual time=3D56.15..153.17 rows=3D166 loops=3D1) Index Cond: (p_brand =3D 'Brand#52'::bpchar) Filter: (((p_container =3D 'MED BAG'::bpchar) OR (p_containe= r =3D=20 'MED BOX'::bpchar) OR (p_container =3D 'MED PKG'::bpchar) OR (p_container = =3D=20 'MED PACK'::bpchar)) AND (p_size >=3D 1) AND (p_size <=3D 10)) -> Index Scan using idx_lineitem_3 on lineitem (cost=3D0.00..14.= 29=20 rows=3D1 width=3D26) (actual time=3D0.23..0.25 rows=3D0 loops=3D166) Index Cond: (("outer".p_partkey =3D lineitem.l_partkey) AND= =20 (lineitem.l_quantity >=3D 14::numeric) AND (lineitem.l_quantity <=3D=20 24::numeric)) Filter: (((l_shipmode =3D 'AIR'::bpchar) OR (l_shipmode =3D = 'AIR=20 REG'::bpchar)) AND (l_shipinstruct =3D 'DELIVER IN PERSON'::bpchar)) Total runtime: 195.82 msec (9 rows) If, however, I combine the two where clauses with an OR, the planner gets= =20 confused and insists on loading the entire tables into memory (even though = I=20 don't have that much memory): explain select sum(l_extendedprice* (1 - l_discount)) as revenue from lineitem, part where ( p_partkey =3D l_partkey and p_brand =3D 'Brand#33' and p_container in ('SM CASE', 'SM BOX', 'SM PACK', 'SM PKG') and l_quantity >=3D 8 and l_quantity <=3D 8 + 10 and p_size between 1 and 5 and l_shipmode in ('AIR', 'AIR REG') and l_shipinstruct =3D 'DELIVER IN PERSON' ) or ( p_partkey =3D l_partkey and p_brand =3D 'Brand#52' and p_container in ('MED BAG', 'MED BOX', 'MED PKG', 'MED PACK') and l_quantity >=3D 14 and l_quantity <=3D 14 + 10 and p_size between 1 and 10 and l_shipmode in ('AIR', 'AIR REG') and l_shipinstruct =3D 'DELIVER IN PERSON' ); Aggregate (cost=3D488301096525.25..488301096525.25 rows=3D1 width=3D146) -> Nested Loop (cost=3D0.00..488301096525.15 rows=3D42 width=3D146) Join Filter: ((("outer".l_shipmode =3D 'AIR'::bpchar) AND=20 ("inner".p_container =3D 'SM CASE'::bpchar) AND ("inner".p_partkey =3D=20 "outer".l_partkey) AND ("inner".p_brand =3D 'Brand#33'::bpchar) AND=20 ("outer".l_quantity >=3D 8::numeric) AND ("outer".l_quantity <=3D 18::numer= ic)=20 AND ("inner".p_size >=3D 1) AND ("inner".p_size <=3D 5) AND=20 ("outer".l_shipinstruct =3D 'DELIVER IN PERSON'::bpchar)) OR=20 (("outer".l_shipmode =3D 'AIR REG'::bpchar) AND ("inner".p_container =3D 'S= M=20 CASE'::bpchar) AND ("inner".p_partkey =3D "outer".l_partkey) AND=20 ("inner".p_brand =3D 'Brand#33'::bpchar) AND ("outer".l_quantity >=3D 8::nu= meric)=20 AND ("outer".l_quantity <=3D 18::numeric) AND ("inner".p_size >=3D 1) AND= =20 ("inner".p_size <=3D 5) AND ("outer".l_shipinstruct =3D 'DELIVER IN=20 PERSON'::bpchar)) OR (("outer".l_shipmode =3D 'AIR'::bpchar) AND=20 ("inner".p_container =3D 'SM BOX'::bpchar) AND ("inner".p_partkey =3D=20 "outer".l_partkey) AND ("inner".p_brand =3D 'Brand#33'::bpchar) AND=20 ("outer".l_quantity >=3D 8::numeric) AND ("outer".l_quantity <=3D 18::numer= ic)=20 AND ("inner".p_size >=3D 1) AND ("inner".p_size <=3D 5) AND=20 ("outer".l_shipinstruct =3D 'DELIVER IN PERSON'::bpchar)) OR=20 (("outer".l_shipmode =3D 'AIR REG'::bpchar) AND ("inner".p_container =3D 'S= M=20 BOX'::bpchar) AND ("inner".p_partkey =3D "outer".l_partkey) AND=20 ("inner".p_brand =3D 'Brand#33'::bpchar) AND ("outer".l_quantity >=3D 8::nu= meric)=20 AND ("outer".l_quantity <=3D 18::numeric) AND ("inner".p_size >=3D 1) AND= =20 ("inner".p_size <=3D 5) AND ("outer".l_shipinstruct =3D 'DELIVER IN=20 PERSON'::bpchar)) OR (("outer".l_shipmode =3D 'AIR'::bpchar) AND=20 ("inner".p_container =3D 'SM PACK'::bpchar) AND ("inner".p_partkey =3D=20 "outer".l_partkey) AND ("inner".p_brand =3D 'Brand#33'::bpchar) AND=20 ("outer".l_quantity >=3D 8::numeric) AND ("outer".l_quantity <=3D 18::numer= ic)=20 AND ("inner".p_size >=3D 1) AND ("inner".p_size <=3D 5) AND=20 ("outer".l_shipinstruct =3D 'DELIVER IN PERSON'::bpchar)) OR=20 (("outer".l_shipmode =3D 'AIR REG'::bpchar) AND ("inner".p_container =3D 'S= M=20 PACK'::bpchar) AND ("inner".p_partkey =3D "outer".l_partkey) AND=20 ("inner".p_brand =3D 'Brand#33'::bpchar) AND ("outer".l_quantity >=3D 8::nu= meric)=20 AND ("outer".l_quantity <=3D 18::numeric) AND ("inner".p_size >=3D 1) AND= =20 ("inner".p_size <=3D 5) AND ("outer".l_shipinstruct =3D 'DELIVER IN=20 PERSON'::bpchar)) OR (("outer".l_shipmode =3D 'AIR'::bpchar) AND=20 ("inner".p_container =3D 'SM PKG'::bpchar) AND ("inner".p_partkey =3D=20 "outer".l_partkey) AND ("inner".p_brand =3D 'Brand#33'::bpchar) AND=20 ("outer".l_quantity >=3D 8::numeric) AND ("outer".l_quantity <=3D 18::numer= ic)=20 AND ("inner".p_size >=3D 1) AND ("inner".p_size <=3D 5) AND=20 ("outer".l_shipinstruct =3D 'DELIVER IN PERSON'::bpchar)) OR=20 (("outer".l_shipmode =3D 'AIR REG'::bpchar) AND ("inner".p_container =3D 'S= M=20 PKG'::bpchar) AND ("inner".p_partkey =3D "outer".l_partkey) AND=20 ("inner".p_brand =3D 'Brand#33'::bpchar) AND ("outer".l_quantity >=3D 8::nu= meric)=20 AND ("outer".l_quantity <=3D 18::numeric) AND ("inner".p_size >=3D 1) AND= =20 ("inner".p_size <=3D 5) AND ("outer".l_shipinstruct =3D 'DELIVER IN=20 PERSON'::bpchar)) OR (("outer".l_shipmode =3D 'AIR'::bpchar) AND=20 ("inner".p_container =3D 'MED BAG'::bpchar) AND ("inner".p_partkey =3D=20 "outer".l_partkey) AND ("inner".p_brand =3D 'Brand#52'::bpchar) AND=20 ("outer".l_quantity >=3D 14::numeric) AND ("outer".l_quantity <=3D 24::nume= ric)=20 AND ("inner".p_size >=3D 1) AND ("inner".p_size <=3D 10) AND=20 ("outer".l_shipinstruct =3D 'DELIVER IN PERSON'::bpchar)) OR=20 (("outer".l_shipmode =3D 'AIR REG'::bpchar) AND ("inner".p_container =3D 'M= ED=20 BAG'::bpchar) AND ("inner".p_partkey =3D "outer".l_partkey) AND=20 ("inner".p_brand =3D 'Brand#52'::bpchar) AND ("outer".l_quantity >=3D=20 14::numeric) AND ("outer".l_quantity <=3D 24::numeric) AND ("inner".p_size = >=3D=20 1) AND ("inner".p_size <=3D 10) AND ("outer".l_shipinstruct =3D 'DELIVER IN= =20 PERSON'::bpchar)) OR (("outer".l_shipmode =3D 'AIR'::bpchar) AND=20 ("inner".p_container =3D 'MED BOX'::bpchar) AND ("inner".p_partkey =3D=20 "outer".l_partkey) AND ("inner".p_brand =3D 'Brand#52'::bpchar) AND=20 ("outer".l_quantity >=3D 14::numeric) AND ("outer".l_quantity <=3D 24::nume= ric)=20 AND ("inner".p_size >=3D 1) AND ("inner".p_size <=3D 10) AND=20 ("outer".l_shipinstruct =3D 'DELIVER IN PERSON'::bpchar)) OR=20 (("outer".l_shipmode =3D 'AIR REG'::bpchar) AND ("inner".p_container =3D 'M= ED=20 BOX'::bpchar) AND ("inner".p_partkey =3D "outer".l_partkey) AND=20 ("inner".p_brand =3D 'Brand#52'::bpchar) AND ("outer".l_quantity >=3D=20 14::numeric) AND ("outer".l_quantity <=3D 24::numeric) AND ("inner".p_size = >=3D=20 1) AND ("inner".p_size <=3D 10) AND ("outer".l_shipinstruct =3D 'DELIVER IN= =20 PERSON'::bpchar)) OR (("outer".l_shipmode =3D 'AIR'::bpchar) AND=20 ("inner".p_container =3D 'MED PKG'::bpchar) AND ("inner".p_partkey =3D=20 "outer".l_partkey) AND ("inner".p_brand =3D 'Brand#52'::bpchar) AND=20 ("outer".l_quantity >=3D 14::numeric) AND ("outer".l_quantity <=3D 24::nume= ric)=20 AND ("inner".p_size >=3D 1) AND ("inner".p_size <=3D 10) AND=20 ("outer".l_shipinstruct =3D 'DELIVER IN PERSON'::bpchar)) OR=20 (("outer".l_shipmode =3D 'AIR REG'::bpchar) AND ("inner".p_container =3D 'M= ED=20 PKG'::bpchar) AND ("inner".p_partkey =3D "outer".l_partkey) AND=20 ("inner".p_brand =3D 'Brand#52'::bpchar) AND ("outer".l_quantity >=3D=20 14::numeric) AND ("outer".l_quantity <=3D 24::numeric) AND ("inner".p_size = >=3D=20 1) AND ("inner".p_size <=3D 10) AND ("outer".l_shipinstruct =3D 'DELIVER IN= =20 PERSON'::bpchar)) OR (("outer".l_shipmode =3D 'AIR'::bpchar) AND=20 ("inner".p_container =3D 'MED PACK'::bpchar) AND ("inner".p_partkey =3D=20 "outer".l_partkey) AND ("inner".p_brand =3D 'Brand#52'::bpchar) AND=20 ("outer".l_quantity >=3D 14::numeric) AND ("outer".l_quantity <=3D 24::nume= ric)=20 AND ("inner".p_size >=3D 1) AND ("inner".p_size <=3D 10) AND=20 ("outer".l_shipinstruct =3D 'DELIVER IN PERSON'::bpchar)) OR=20 (("outer".l_shipmode =3D 'AIR REG'::bpchar) AND ("inner".p_container =3D 'M= ED=20 PACK'::bpchar) AND ("inner".p_partkey =3D "outer".l_partkey) AND=20 ("inner".p_brand =3D 'Brand#52'::bpchar) AND ("outer".l_quantity >=3D=20 14::numeric) AND ("outer".l_quantity <=3D 24::numeric) AND ("inner".p_size = >=3D=20 1) AND ("inner".p_size <=3D 10) AND ("outer".l_shipinstruct =3D 'DELIVER IN= =20 PERSON'::bpchar))) -> Seq Scan on lineitem (cost=3D0.00..235620.15 rows=3D6001215= =20 width=3D95) -> Seq Scan on part (cost=3D0.00..7367.00 rows=3D200000 width=3D= 51) You'll pardon me for not doing an "ANALYZE", but I didn't want to wait=20 overnight. Manually disabling Seqscan and Nestloop did nothing to affect= =20 this query plan; neither did removing the aggregate. Tommorrow I will test 7.4 Beta 4. How can we fix this? --=20 -Josh Berkus Aglio Database Solutions San Francisco