Re: sql indexing suggestions needed

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

On 03/20/07 13:54, 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 )

If it's less than 10, it can't be NULL. No need for this predicate.

> 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.

The problem is that the bool columns only have 2 values, and so it's
more efficient to scan the whole table than to use indexes.

How many *distinct* values are there in int_c? What percentage of
them match "int_c <= 10"?

Same questions, but for int_h.

If int_h is relatively unique, then this index might help:
(INT_H, INT_C)

If that helps, try
(INT_H, INT_C, BOOL_A, BOOL_B, BOOL_D, BOOL_E, BOOL_G, BOOL_I)

HTH.

-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.6 (GNU/Linux)

iD8DBQFGADT0S9HxQb37XmcRAubuAJ0do/zu0vkaw5XzVQyPeJnFB2cJtwCeMCna
cH3p6UGwqes8ZbAc5QfE1ok=
=pPl0
-----END PGP SIGNATURE-----