Re: Queries not using Index

Good evening.

On Tue, 23 Jul 2002, Stephan Szabo wrote:

>Hmm, when the data was put in, was it put in where the same value
>would be bunched up?

I inserted the data via 30 "COPY t2002_06 from stdin" (one per day) So it
was grouped by station and then day for each insert. (My script dumped
the data from each station for the day and then repeated for each station
and then finally dumped the entire day into the DB. Are you saying that
this process has tricked pgsql into not believing it needs to use an
INDEX? Sorry for my ignorance here. I have done similar processes with
PG7.1.2 and it seemed to use the INDEX.

In fact, I just repeated the dumping scripts on a machine with 7.1.2 and
the "explain select" reports to be using the Index Scan. Hmmmm

>IIRC that's a case the optimizer won't realize if the data isn't ordered
>but merely bunched together that'd cause it to over-estimate the cost of
>an index scan. Clustering on the index might help, but cluster drops alot
>of info about the table, so you have to be careful.

Thanks for the info. I am off to read about how to do clustering!

Thanks!
Daryl