Dear all,
Context: FreeBSD postgresql 7.4.5, on a pentium 3 server 128MB.
I have a huge table letture02 made of 1,340,000 "freezed" records of 98
columns each (they do not change over time because the data are related
to measurements made in 2002 and 2003 there is no need to update them or,
worst, insert new records).
Because I have to query the table according to a condition related to two
columns only , contatore and data, I created a primary index on both of
them (..primary index (contatore,data)...)
But, if I query the database according to setr value of the indexed variables
an index scan is used and total runtime is a snap
explain analyze select * from letture02 where contatore=1244 and data=180;
QUERY PLAN
--------------------------------------------------------------------------------
----------------------------------------------
Index Scan using contatoredata on letture02 (cost=0.00..67.96 rows=17
width=11
60) (actual time=0.413..0.423 rows=1 loops=1)
Index Cond: ((contatore = 1244) AND (data = 180))
Total runtime: 0.899 ms
(3 rows)
On the contrary querying the table on the same fields but with a > or <
condition a sequential scan is used with unbearable runtime
explain analyze select * from letture02 where contatore>1244 and data>180;
QUERY PLAN
--------------------------------------------------------------------------------
--------------------------------------------
Seq Scan on letture02 (cost=0.00..118103.00 rows=73000 width=1160) (actual
tim
e=6061.921..121600.729 rows=287860 loops=1)
Filter: ((contatore > 1244) AND (data > 180))
Total runtime: 122696.066 ms
(3 rows)
Because this second type of query should be highly demanded, is there anything
I could do to shorten the runtime?
Vittorio