From: Bruno Wolff III <bruno@wolff.to>
To: soni de <soni.de@gmail.com>
Cc: Dave Dutcher <dave@tridecap.com>
Cc: pgsql-performance@postgresql.org
Subject: Re: Takes too long to fetch the data from database
Date: Tue, 9 May 2006 02:38:55 -0500
Message-ID: <20060509073855.GA11659@wolff.to> (raw)
In-Reply-To: <9f2e40a90605082054g6dd3e4f5ne3faf631e4792116@mail.gmail.com>
References: <9f2e40a90604202142x5adbe6f0r7a993f8319619cc7@mail.gmail.com>
<007801c66547$35e26270$8300a8c0@tridecap.com>
<9f2e40a90605082054g6dd3e4f5ne3faf631e4792116@mail.gmail.com>
On Tue, May 09, 2006 at 09:24:15 +0530,
soni de <soni.de@gmail.com> wrote:
>
> EXPLAIN
> pdb=# EXPLAIN ANALYZE select * from wan where kname = 'pluto' order by stime
> limit 50;
> NOTICE: QUERY PLAN:
>
> Limit (cost=3515.32..3515.32 rows=50 width=95) (actual time=
> 230492.69..230493.07 rows=50 loops=1)
> -> Sort (cost=3515.32..3515.32 rows=208 width=95) (actual time=
> 230492.68..230493.00 rows=51 loops=1)
> -> Seq Scan on wan (cost=0.00..3507.32 rows=208 width=95) (actual
> time=0.44..229217.38 rows=18306 loops=1)
> Total runtime: 230631.62 msec
Unless you have an index on (kname, stime) the query is going to need to
find the records with a value for kname of 'pluto' and then get the most
recent 50 of them. It looks like there are enough estimated records
with kname = 'pluto', that a sequential scan is being prefered.
Creating an extra index will slow down inserts somewhat, but will speed
up queries like the above significantly, so may be worthwhile for you.
I think later versions of Postgres are smarter, but for sure in 7.2
you will need to write the query like:
SELECT *
FROM wan
WHERE kname = 'pluto'
ORDER BY kname DESC, stime DESC
LIMIT 50
;
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: bruno@wolff.to, soni.de@gmail.com, dave@tridecap.com
Subject: Re: Takes too long to fetch the data from database
In-Reply-To: <20060509073855.GA11659@wolff.to>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox