pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedRe: QRY seems not using indexes
2+ messages / 2 participants
[nested] [flat]
* Re: QRY seems not using indexes
@ 2005-08-09 04:30 Qingqing Zhou <zhouqq@cs.toronto.edu>
0 siblings, 1 reply; 2+ messages in thread
From: Qingqing Zhou @ 2005-08-09 04:30 UTC (permalink / raw)
To: pgsql-performance
<morandell@gmail.com> writes
>
>
> so, if I do a qry like "EXPLAIN ANALYZE select * from pridecdr where
> idsede=8977758488" it tooks a lot of time before i get back any result:
>
> Index Scan using prd_id_sede on pridecdr (cost=0.00..699079.90
> rows=181850 width=138) (actual time=51.241..483068.255 rows=150511
> loops=1)
> Index Cond: (idsede = 8977758488::bigint)
> Total runtime: 483355.325 ms
>
The query plan looks ok. Try to do EXPLAIN ANALYZE twice and see if there is
any difference. This could reduce the IO time to read your index/data since
you got enough RAM.
Also, if you haven't done VACUUM FULL for a long time, do so and compare the
difference.
Regards,
Qingqing
^ permalink raw reply [nested|flat] 2+ messages in thread
* Re: QRY seems not using indexes
@ 2005-08-09 05:05 Mark Kirkwood <markir@paradise.net.nz>
parent: Qingqing Zhou <zhouqq@cs.toronto.edu>
0 siblings, 0 replies; 2+ messages in thread
From: Mark Kirkwood @ 2005-08-09 05:05 UTC (permalink / raw)
To: morandell@gmail.com; +Cc: Qingqing Zhou <zhouqq@cs.toronto.edu>; pgsql-performance
Qingqing Zhou wrote:
> <morandell@gmail.com> writes
>
>>
>>so, if I do a qry like "EXPLAIN ANALYZE select * from pridecdr where
>>idsede=8977758488" it tooks a lot of time before i get back any result:
>>
>>Index Scan using prd_id_sede on pridecdr (cost=0.00..699079.90
>>rows=181850 width=138) (actual time=51.241..483068.255 rows=150511
>>loops=1)
>> Index Cond: (idsede = 8977758488::bigint)
>> Total runtime: 483355.325 ms
>>
>
>
> The query plan looks ok. Try to do EXPLAIN ANALYZE twice and see if there is
> any difference. This could reduce the IO time to read your index/data since
> you got enough RAM.
>
> Also, if you haven't done VACUUM FULL for a long time, do so and compare the
> difference.
>
Could also be libpq buffering all 150000 rows before showing any.
It might be worthwhile using a CURSOR and doing 1 FETCH. If that is
quick, then buffering is probably the issue. BTW - do you really want
all the rows?
Cheers
Mark
^ permalink raw reply [nested|flat] 2+ messages in thread
end of thread, other threads:[~2005-08-09 05:05 UTC | newest]
Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2005-08-09 04:30 Re: QRY seems not using indexes Qingqing Zhou <zhouqq@cs.toronto.edu>
2005-08-09 05:05 ` Mark Kirkwood <markir@paradise.net.nz>
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