Re: sql indexing suggestions needed

I wonder if converting all of your bools to a bit string and storing
that string as a number would make things faster?

Benjamin

On Mar 20, 2007, at 11:54 AM, Jonathan Vanasco wrote:

> i'm going crazy trying to optimize this select.
>
> The table has ~25 columns, the select is based on 10.   There are
> approx 5 million records in the table and growing.
>
> No matter how I index + analyze this table, including making an
> index of every related column on the search, pg keeps doing a
> sequential scan and never includes an index -- which takes
> ~2minutes to do.  I really need to cut this down.
>
>     SELECT
>         *
>     FROM
>         table_a
>     WHERE
>         ( bool_a = False )
>         AND
>         ( bool_b= False )
>         AND
>         ( int_c IS NOT NULL )
>         AND
>         ( int_c <= 10 )
>         AND
>         ( bool_d = False )
>         AND
>         ( bool_e= True )
>         AND
>         ( timestamp_f IS NULL )
>         AND
>         ( bool_g IS False )
>         AND
>         ( int_h= 1 )
>         AND
>         ( bool_i = False )
>     ORDER BY
>         id ASC
>     LIMIT 100
>
> can anyone suggest an indexing approach that might get pg to use
> the indexes ?  this is driving me crazy.
>
> thanks.
>
>
>
>
> ---------------------------(end of
> broadcast)---------------------------
> TIP 4: Have you searched our list archives?
>
>               http://archives.postgresql.org/
>