From: | Rex McMaster <rmcm(at)compsoft(dot)com(dot)au> |
---|---|
To: | Roberto Moreda <moreda(at)sanluis(dot)net> |
Cc: | gpsql-general <pgsql-general(at)hub(dot)org> |
Subject: | Re: [GENERAL] Speed of joins using sparse indexes |
Date: | 1999-08-09 23:03:14 |
Message-ID: | 14255.24114.427387.134540@truffaut.int.compsoft.com.au |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
have you tried a composite index with another, more distributed
attribute (like a timestamp)? - with boolean as first component.
create index ix on tablename(boolean-attribute,timestamp-attribute);
------------------------------------------------------------
Rex McMaster rmcm(at)compsoft(dot)com(dot)au
rex(at)mcmaster(dot)wattle(dot)id(dot)au
PGP Public key: http://www.compsoft.com.au/~rmcm/pgp-pk
Roberto Moreda writes:
> This mail tries to explain the solution that I've found to address the
> problem of the joins that uses tables with very sparse indexes.
>
> The exact problem was :
>
> How can I manage the problem of select a few rows with a boolean atribute
> when they are 5 rows with flag='Y' in a table of 100000 rows?
> I't must to be an index, but
> the optimizer asumes that a Seq Scan is more cheap... yes, yes... I know :
> if I ask for the 100000 rows with flag='N' then Seq Scan is the solution,
> but the interesting query is the other : to extract the 5 rows with
> flag='Y' from whitin the 100000 rows with the flag='N'.
>
> A possible solution to optimize this kind of query is to create an auxiliar
> table with the id's of the 5 rows with flag='Y', maintained by rules watching
> the attribute flag in the target table. In this manner, I never do a
> update/insert in the flag table and I replace the "flag='Y'" in the query in
> favour of "TABLE.id=FLAG_TABLE.id" (another join).
>
> It's a kind of tell to Postgres "Hey, I'm very interested in the rows with
> flag='Y'" ... :) and the results in speed-up are amazing.
>
> I think is better than "fake" a dense index to change the behaviour of the
> optimizer.
>
> Any suggestions?
>
> Roberto.
>
> ... sorry for my English ;)
>
> --
> Roberto Moreda
> Resp. Dpto. Informática Handem/San Luis
> Tlf +34 981 779000
> Fax +34 981 779022
> Pol. Piadela Sur, Autovía A6 Sal.567
> 15300 Betanzos (A Coruña) - España
--
From | Date | Subject | |
---|---|---|---|
Next Message | Anand Surelia | 1999-08-09 23:41:06 | Re: where is the "applied"? |
Previous Message | Mario Haza | 1999-08-09 21:28:27 | how to recover old data |