> Hi Bruce,
>
> Ok. Sorry about the delay.
>
> I added the math.h but that doesn't seem to change the
> query plan output.
>
> I then dropped all the indices, made a new one on three of the
> variables and clustered:
>
> create index m_col on lmctot using btree (j_m, h_m, k_m);
> cluster m_col on lmctot;
> vacuum analyze;
>
> where the j_m, h_m, k_m are three float4 fields.
>
> The cluster took about 18 hours on my 7.1 million records
> (this is a dual 450Mhz Xeon Linux box). Not sure why
> this was so slow.
That is a long time.
> Anyway, this *hugely* improved queries of form:
>
> select count(*) from lmctot where j_m>3.4::float4 and j_m<3.5:
>
> although the explain query plan output is identical. However
> using h_m or k_m (not the first variable in the index) appears
> to be doing a sequential scan. Is that right?
Yes, that is right. The index is only on the one field, and can only
use secondary index variables after the first one is matched.
>
> I then made indices on h_m and k_m, vacuum analyzed and tried
> again, but got identical performance. If this is the way
> it is, so be it, but I have the feeling that something is
> not working properly.
The big problem is that you can only cluster on one index.
What cluster has done is prevent the system from bouncing all over the
disk getting matching rows. They are all sequential on the disk.
--
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