Make a query faster...

From: v(dot)demartino2(at)virgilio(dot)it
To: "postgresql" <pgsql-general(at)postgresql(dot)org>
Subject: Make a query faster...
Date: 2004-12-18 18:26:48
Message-ID: 41536B85000F5B30@ims3e.cp.tin.it
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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

Browse pgsql-general by date

  From Date Subject
Next Message Tom Lane 2004-12-18 18:31:48 Re: OSX 10.3.7 broke Postgresql 8.0.0b5?
Previous Message Bruno Wolff III 2004-12-18 17:51:08 Re: Multi row sequence?