From: soni de <soni.de@gmail.com>
To: Merlin Moncure <mmoncure@gmail.com>
To: pgsql-performance@postgresql.org
Subject: Re: Takes too long to fetch the data from database
Date: Thu, 20 Apr 2006 11:07:31 +0530
Message-ID: <9f2e40a90604192237i5d3e8f0dl1c8b19887bc34e22@mail.gmail.com> (raw)
In-Reply-To: <b42b73150604110613h2c63ea61oec8035c8e2f4ed98@mail.gmail.com>
References: <9f2e40a90604100021n1cb694d0lb95f318f800f33ee@mail.gmail.com>
<b42b73150604100845w553dcee6kde20e4b82770aab0@mail.gmail.com>
<9f2e40a90604110004u787716c2s22bdf53fe349326@mail.gmail.com>
<b42b73150604110613h2c63ea61oec8035c8e2f4ed98@mail.gmail.com>
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@gmail.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
>
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: soni.de@gmail.com, mmoncure@gmail.com
Subject: Re: Takes too long to fetch the data from database
In-Reply-To: <9f2e40a90604192237i5d3e8f0dl1c8b19887bc34e22@mail.gmail.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox