From: soni de <soni.de@gmail.com>
To: 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 09:24:15 +0530
Message-ID: <9f2e40a90605082054g6dd3e4f5ne3faf631e4792116@mail.gmail.com> (raw)
In-Reply-To: <007801c66547$35e26270$8300a8c0@tridecap.com>
References: <9f2e40a90604202142x5adbe6f0r7a993f8319619cc7@mail.gmail.com>
<007801c66547$35e26270$8300a8c0@tridecap.com>
Hello,
I have tried the query SELECT * FROM wan ORDER BY stime DESC OFFSET 0 LIMIT
50; and it is working great.
EXPLAIN ANALYSE of the above query is:
pdb=# EXPLAIN ANALYZE select * from wan order by stime desc limit 50 ;
NOTICE: QUERY PLAN:
Limit (cost=0.00..12.10 rows=50 width=95) (actual time=24.29..50.24 rows=50
loops=1)
-> Index Scan Backward using wan_pkey on wan
(cost=0.00..19983.31rows=82586 width=95) (actual time=
24.28..50.14 rows=51 loops=1)
Total runtime: 50.55 msec
EXPLAIN
Now I am facing another problem, If I use where clause is select query it is
taking too much time. Can you please help me on this.
Explain analyze are follows:
pdb=# EXPLAIN ANALYZE select count(1) from wan where kname = 'pluto';
NOTICE: QUERY PLAN:
Aggregate (cost=3507.84..3507.84 rows=1 width=0) (actual time=
214647.53..214647.54 rows=1 loops=1)
-> Seq Scan on wan (cost=0.00..3507.32 rows=208 width=0) (actual time=
13.65..214599.43 rows=18306 loops=1)
Total runtime: 214647.87 msec
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
EXPLAIN
pdb=# EXPLAIN ANALYZE SELECT * FROM wan WHERE stime >= 20123 AND stime <=
24000 ORDER BY stime limit 50;
NOTICE: QUERY PLAN:
Limit (cost=0.00..2519.70 rows=50 width=95) (actual
time=7346.74..7351.42rows=50 loops=1)
-> Index Scan using wan_pkey on wan (cost=0.00..20809.17 rows=413
width=95) (actual time=7346.73..7351.32 rows=51 loops=1)
Total runtime: 7351.71 msec
EXPLAIN
for above queries if I use desc order then the queries takes too much time.
I am not getting for the above queries how do I increase the speed.
Postgresql version is 7.2.3
total no. of records: 5700300
On 4/21/06, Dave Dutcher <dave@tridecap.com> wrote:
>
> I've never used a cursor in Postgres, but I don't think it will help you
> a lot. In theory cursors make it easier to do paging, but your main
> problem is that getting the first page is slow. A cursor isn't going to
> be any faster at getting the first page than OFFSET/LIMIT is.
>
>
>
> Did you try Bruno's suggestion of:
>
>
>
> SELECT * FROM wan ORDER BY stime DESC OFFSET 0 LIMIT 50;
>
>
>
> You should run an EXPLAIN ANALYZE on that query to see if it is using an
> index scan. Also what version of Postgres are you using? You can run
> select version(); to check.
>
>
>
>
>
>
>
> -----Original Message-----
> *From:* pgsql-performance-owner@postgresql.org [mailto:
> pgsql-performance-owner@postgresql.org] *On Behalf Of *soni de
> *Sent:* Thursday, April 20, 2006 11:42 PM
> *To:* Merlin Moncure
> *Cc:* pgsql-performance@postgresql.org
> *Subject:* Re: [PERFORM] Takes too long to fetch the data from database
>
>
>
> I don't want to query exactly 81900 rows into set. I just want to fetch 50
> or 100 rows at a time in a decreasing order of stime.(i.e 50 or 100 rows
> starting from last to end).
>
>
>
> if we fetched sequentially, there is also problem in fetching all the
> records (select * from wan where kname='pluto' order by stime) it is taking
> more than 4~5 minutes. tried it on same table having more than 326054
> records.
>
>
>
>
> On 4/20/06, *Merlin Moncure* <mmoncure@gmail.com> wrote:
>
> > SELECT * FROM wan ORDER BY stime LIMIT 50 OFFSET 81900;
>
> you need to try and solve the problem without using 'offset'. you could
> do:
> BEGIN;
> DECLARE crs cursor FOR SELECT * FROM wan ORDER BY stime;
> FETCH ABSOLUTE 81900 in crs;
> FETCH 49 in crs;
> CLOSE crs;
> COMMIT;
>
> this may be a bit faster but will not solve the fundamental problem.
>
> the more interesting question is why you want to query exactly 81900
> rows into a set. This type of thinking will always get you into
> trouble, absolute positioning will not really work in a true sql
> sense. if you are browsing a table sequentially, there are much
> better methods.
>
> merlin
>
>
>
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: soni.de@gmail.com, dave@tridecap.com
Subject: Re: Takes too long to fetch the data from database
In-Reply-To: <9f2e40a90605082054g6dd3e4f5ne3faf631e4792116@mail.gmail.com>
* 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