Re: Takes too long to fetch the data from database

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(at)tridecap(dot)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(at)postgresql(dot)org [mailto:
> pgsql-performance-owner(at)postgresql(dot)org] *On Behalf Of *soni de
> *Sent:* Thursday, April 20, 2006 11:42 PM
> *To:* Merlin Moncure
> *Cc:* pgsql-performance(at)postgresql(dot)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(at)gmail(dot)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
>
>
>