Re: Takes too long to fetch the data from database

Please provide me some help regarding how could I use cursor in following
cases? :

I want to fetch 50 records at a time starting from largest stime.

Total no. of records in the "wan" table: 82019

pdb=# \d wan

Table "wan"

Column | Type | Modifiers

-------------+--------------------------+-----------

stime | bigint | not null

kname | character varying(64) |

eid | smallint |

rtpe | smallint |

taddr | character varying(16) |

ntime | bigint |

Primary key: wan_pkey

stime is the primary key.

pdb=#

SELECT * FROM wan ORDER BY stime LIMIT 50 OFFSET 81900;

pdb=# explain analyze SELECT * FROM wan ORDER BY stime LIMIT 50

OFFSET 81900;

NOTICE: QUERY PLAN:

Limit (cost=17995.15..17995.15 rows=50 width=95) (actual time=
9842.92..9843.20

rows=50 loops=1)

-> Sort (cost=17995.15..17995.15 rows=82016 width=95) (actual time=
9364.56..

9793.00 rows=81951 loops=1)

-> Seq Scan on wan (cost=0.00..3281.16 rows=82016 width=95) (actu

al time=0.11..3906.29 rows=82019 loops=1)

Total runtime: 10010.76 msec

EXPLAIN

pdb=#

SELECT * FROM wan where kname='pluto' ORDER BY stime LIMIT 50 OFFSET 81900;

pdb=# explain analyze SELECT * from wan where kname='pluto' order by stime
limit 50 offset 81900;

NOTICE: QUERY PLAN:

Limit (cost=3494.13..3494.13 rows=1 width=95) (actual
time=9512.85..9512.85rows=0 loops=1)

-> Sort (cost=3494.13..3494.13 rows=206 width=95) (actual time=
9330.74..9494.90 rows=27485 loops=1)

-> Seq Scan on wan (cost=0.00..3486.20 rows=206 width=95) (actual
time=0.28..4951.76 rows=27485 loops=1)

Total runtime: 9636.96 msec

EXPLAIN

SELECT * FROM wan where kname='pluto' and rtpe=20 ORDER BY stime LIMIT 50
OFFSET 81900;

pdb=# explain analyze SELECT * from wan where kname='pluto' and rtpe = 20
order by stime limit 50 offset 81900;

NOTICE: QUERY PLAN:

Limit (cost=3691.25..3691.25 rows=1 width=95) (actual
time=7361.50..7361.50rows=0 loops=1)

-> Sort (cost=3691.25..3691.25 rows=1 width=95) (actual time=
7361.50..7361.50 rows=0 loops=1)

-> Seq Scan on wan (cost=0.00..3691.24 rows=1 width=95) (actual
time=7361.30..7361.30 rows=0 loops=1)

Total runtime: 7361.71 msec

EXPLAIN

pdb=#

all the above queries taking around 7~10 sec. to fetch the last 50 records.
I want to reduce this time because table is growing and table can contain
more than 1 GB data then for 1 GB data above queries will take too much
time.

I am not getting how to use cursor to fetch records starting from last
records in the above case offset can be any number (less than total no. of
records).

I have use following cursor, but it is taking same time as query takes.

BEGIN;

DECLARE crs cursor FOR SELECT * FROM wan ORDER BY stime LIMIT 50 OFFSET
81900;

FETCH ALL in crs;

CLOSE crs;

COMMIT;

On 4/11/06, Merlin Moncure <mmoncure(at)gmail(dot)com> wrote:
>
> > pdb=# explain analyze SELECT sdate, stime, rbts from lan WHERE (
> >
> > ( bname = 'pluto' ) AND ( cno = 17 ) AND ( pno = 1 ) AND ( ( sdate
> >
> > >= '2004-07-21' ) AND ( sdate <= '2004-07-21' ) ) ) ORDER BY sdate,
> stime
> > ;
>
> this query would benefit from an index on
> pluto, cno, pno, sdate
>
> create index Ian_idx on Ian(bname, cno, pno, sdate);
>
>
> > pdb=# explain analyze SELECT ALL sdate, stime, rbts from lan WHERE ( (
> > bname = 'neptune' ) AND ( cno = 17 ) AND ( pno = 1 ) AND ( ( sdate >=
> > '2004-07-21' ) AND ( sdate <= '2004-07-21' ) ) ) ORDER BY sdate, stime
> ;
>
> ditto above. Generally, the closer the fields in the where clause are
> matched by the index, the it will speed up your query.
>
> Merlin
>