X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [64.117.224.193]) by svr1.postgresql.org (Postfix) with ESMTP id 9F672310AC7 for ; Mon, 30 Jun 2003 21:13:49 +0000 (GMT) Received: from svr1.postgresql.org ([64.117.224.193]) by localhost (svr1.postgresql.org [64.117.224.193]) (amavisd-new, port 10024) with ESMTP id 85652-01 for ; Mon, 30 Jun 2003 18:13:38 -0300 (ADT) Received: from perrin.int.nxad.com (internal.ext.nxad.com [69.1.70.251]) by svr1.postgresql.org (Postfix) with ESMTP id 7A4C0310ABD for ; Mon, 30 Jun 2003 18:13:37 -0300 (ADT) Received: by perrin.int.nxad.com (Postfix, from userid 1001) id 4F4E421058; Mon, 30 Jun 2003 14:13:36 -0700 (PDT) Date: Mon, 30 Jun 2003 14:13:36 -0700 From: Sean Chittenden To: Robert Wille Cc: pgsql-performance@postgresql.org Subject: Re: Query planner plans very inefficient plans Message-ID: <20030630211336.GF96753@perrin.int.nxad.com> References: <032101c33f31$0d239910$2264a8c0@zucchini> Mime-Version: 1.0 Content-Type: text/plain; charset=us-ascii Content-Disposition: inline In-Reply-To: <032101c33f31$0d239910$2264a8c0@zucchini> X-PGP-Key: finger seanc@FreeBSD.org X-PGP-Fingerprint: 3849 3760 1AFE 7B17 11A0 83A6 DD99 E31F BC84 B341 X-Web-Homepage: http://sean.chittenden.org/ User-Agent: Mutt/1.5.4i X-Archive-Number: 200306/209 X-Sequence-Number: 2293 > I have somewhere around 3M rows in the image table, and 37K rows in the > ancestry table. The following is representative of some of the common > queries I issue: > > select * from image natural join ancestry where ancestorid=1000000 and > (state & 7::bigint) = 0::bigint; > > When I ask postgres to EXPLAIN it, I get the following: > > Merge Join (cost=81858.22..81900.60 rows=124 width=49) > -> Sort (cost=81693.15..81693.15 rows=16288 width=41) > -> Seq Scan on image (cost=0.00..80279.17 rows=16288 width=41) > -> Sort (cost=165.06..165.06 rows=45 width=8) > -> Index Scan using ancestry_ancestorid_key on ancestry > (cost=0.00..163.83 rows=45 width=8) > > It appears to me that the query executes as follows: > > 1. Scan every row in the image table to find those where (state & > 7::bigint) = 0::bigint > 2. Sort the results > 3. Use an index on ancestry to find rows where ancestorid=1000000 > 4. Sort the results > 5. Join the two FWIW, I use INTs as bit vectors for options in various applications and have run into this in a few cases. In the database, I only care about a few bits in the options INT, so what I did was create a function for each of the bits that I care about and then a function index. Between the two, I've managed to solve my performance problems. CREATE FUNCTION app_option_foo_is_set(INT) RETURNS BOOL IMMUTABLE AS ' BEGIN IF $1 & 7::INT THEN RETURN TRUE; ELSE RETURN FALSE; END IF; END; ' LANGUAGE 'plpgsql'; CREATE INDEX app_option_foo_fidx ON app_option_tbl (app_option_foo_is_set(options)); VACUUM ANALYZE; Just make sure that you set your function to be IMMUTABLE. -sc PS It'd be slick if PostgreSQL would collapse adjacent booleans into a bit in a byte: it'd save some apps a chunk of space. 32 options == 32 bytes with the type BOOL, but if adjacent BOOLs were collapsed, it'd only be 4 bytes on disk and maybe some page header data. -- Sean Chittenden