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.85 rows=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.50 rows=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;
> 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
