Optimizing further

From: Ken Williams <ken(at)mathforum(dot)org>
To: pgsql-general(at)postgresql(dot)org
Subject: Optimizing further
Date: 2002-06-14 01:35:03
Message-ID: F2B69EAD-7F36-11D6-AFA5-0003936C1626@mathforum.org
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

Hi,

I'm wondering whether I need to do something to further optimize
a query. My query looks like this:

select price, date FROM foo
WHERE date < '2000-07-01 00:29:00+10' and code='UCL'
ORDER by date DESC, stock DESC
LIMIT 100;

I've got over 7 million rows in the table, and a btree
index(date, code). According to "explain", the query will be
executed as follows:

Limit (cost=0.00..393.45 rows=100 width=32)
-> Index Scan Backward using foo_date_code on trades
(cost=0.00..12309.13 rows=3128 width=32)

When I run the query, it takes a really long time (10 seconds)
the first time I execute it, and then returns instantly the
second time.

Is there something I need to do in order to get these running
faster, the *first* time? I've run "VACUUM ANALYZE" on the
table after all the rows were inserted.

Thanks.

-Ken

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Jim Morcombe 2002-06-14 01:36:59 Help required with installing postgreSQL on SCO Openserver
Previous Message Scott Marlowe 2002-06-13 23:43:33 Re: Close idle connections