pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Sean Chittenden <sean@chittenden.org>
To: Robert Wille <a2om6sy02@sneakemail.com>
Cc: pgsql-performance@postgresql.org
Subject: Re: Query planner plans very inefficient plans
Date: Mon, 30 Jun 2003 14:13:36 -0700
Message-ID: <20030630211336.GF96753@perrin.int.nxad.com> (raw)
In-Reply-To: <032101c33f31$0d239910$2264a8c0@zucchini>
References: <032101c33f31$0d239910$2264a8c0@zucchini>

>    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



view thread (3+ messages)

Message-ID: <20030630211336.GF96753@perrin.int.nxad.com>
Permalink:  ../20030630211336.GF96753@perrin.int.nxad.com/
Also on:    postgresql.org/message-id/20030630211336.GF96753@perrin.int.nxad.com

 · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients from your mail client; the link fills in
  the headers below: reply via email
  Some clients, webmail especially, drop In-Reply-To and References from
  a link, and the reply then starts a new thread: use the mbox below.

  To: pgsql-performance@postgresql.org
  Cc: sean@chittenden.org, a2om6sy02@sneakemail.com
  Subject: Re: Query planner plans very inefficient plans
  In-Reply-To: <20030630211336.GF96753@perrin.int.nxad.com>
  References: <032101c33f31$0d239910$2264a8c0@zucchini> <20030630211336.GF96753@perrin.int.nxad.com>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox