From: | Roberto Moreda <moreda(at)sanluis(dot)net> |
---|---|
To: | gpsql-general <pgsql-general(at)hub(dot)org> |
Subject: | Speed of joins using sparse indexes |
Date: | 1999-08-09 13:39:35 |
Message-ID: | 19990809153935.D13798@sanluis.net |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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 | Vovk G. Grigoriy | 1999-08-09 15:05:32 | problem keyboard koi8-r |
Previous Message | Leon | 1999-08-09 12:00:00 | Re: [GENERAL] Client dies in transaction ? |