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