Re: [GENERAL] Speed of joins using sparse indexes
От | Bruce Momjian |
---|---|
Тема | Re: [GENERAL] Speed of joins using sparse indexes |
Дата | |
Msg-id | 199908091522.LAA01599@candle.pha.pa.us обсуждение исходный текст |
Ответ на | Speed of joins using sparse indexes (Roberto Moreda <moreda@sanluis.net>) |
Список | pgsql-general |
[Charset iso-8859-1 unsupported, filtering to ASCII...] > 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. This sounds like a very good solution. Rules can help. -- Bruce Momjian | http://www.op.net/~candle maillist@candle.pha.pa.us | (610) 853-3000 + If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup. | Drexel Hill, Pennsylvania 19026
В списке pgsql-general по дате отправления: