Re: Query planner plans very inefficient plans

>    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