Re: Takes too long to fetch the data from database - Mailing list pgsql-performance

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