Re: Takes too long to fetch the data from database

["soni de" <soni.de@xxxxxxxxx>]

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.31 rows=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.42 rows=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@xxxxxxxxxxxx> 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@xxxxxxxxxxxxxx [mailto:pgsql-performance-owner@xxxxxxxxxxxxxx] On Behalf Of soni de
Sent: Thursday, April 20, 2006 11:42 PM
To: Merlin Moncure
Cc: pgsql-performance@xxxxxxxxxxxxxx
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@xxxxxxxxx> 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