Re: order by and index path
Re: order by and index path
От:
Andreas Zeugswetter <andreas.zeugswetter@telecom.at>
Дата:
Jan wrote: > If there is an ORDER BY clause, using an index scan is the > clever way if the indexqual dramatically reduces the the > amount of data selected and sorted. I think this is the > normal case yes > (who really selects nearly all rows from a 5M row > table?). Data Warehouse apps > This will hurt if someone really selects most of the rows and the index > scan jumps over the disc. I think this is a non issue, since if a qual is not restrictive enough, the optimizer should choose a seq scan anyway. Doesn' t it do this already ? > But here the programmer should use > an unqualified query to perform a seqscan and do the > qualification in the frontend application. I would reformulate this to: Here the backend should do a seq scan and use the qualification to eliminate not wanted rows. Resumee: You have to look at this from the cost point of view. If there is an order by that can be done with an index, this will make the index a little more preferrable than for the same query without the order by, but it should not force the index. You have to give the sort a cost, so that the index access can be compared to the seq scan and sort path. Andreas
Re: [HACKERS] Re: order by and index path
От:
Bruce Momjian <maillist@candle.pha.pa.us>
Дата:
> Jan wrote: > > If there is an ORDER BY clause, using an index scan is the > > clever way if the indexqual dramatically reduces the the > > amount of data selected and sorted. I think this is the > > normal case > > yes > > > (who really selects nearly all rows from a 5M row > > table?). > > Data Warehouse apps > > > This will hurt if someone really selects most of the rows and the index > > scan jumps over the disc. > > I think this is a non issue, since if a qual is not restrictive enough, > the optimizer should choose a seq scan anyway. Doesn' t it do this already ? Yes it does. > > > But here the programmer should use > > an unqualified query to perform a seqscan and do the > > qualification in the frontend application. > > I would reformulate this to: > Here the backend should do a seq scan and use the qualification to eliminate > not wanted rows. > > Resumee: > You have to look at this from the cost point of view. If there is an order by that can be > done with an index, this will make the index a little more preferrable than for the same > query without the order by, but it should not force the index. > You have to give the sort a cost, so that the index access can be compared to the > seq scan and sort path. This cost is compared. The optimizer uses the min-max values for the column stored in pg_statistic to see how much of the table is being requested, and decides on an index or not. Doing the restriction on the fontend sounds kind of cheesy to me. -- 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
