Skip site navigation (1) Skip section navigation (2)

Re: Large # of rows in query extremely slow, not using

From: Stephen Crowley <stephen(dot)crowley(at)gmail(dot)com>
To: pgsql-performance(at)postgresql(dot)org
Cc: Pierre-Frédéric Caillaud <lists(at)boutiquenumerique(dot)com>
Subject: Re: Large # of rows in query extremely slow, not using
Date: 2004-09-17 01:51:11
Message-ID: 3f71fdf1040916185113e277e6@mail.gmail.com (view raw or flat)
Thread:
Lists: pgsql-performance
Here are some results of explain analyze, I've included the LIMIT 10
because otherwise the resultset would exhaust all available memory.


explain analyze select * from history where date='2004-09-07' and
stock='ORCL' LIMIT 10;

"Limit  (cost=0.00..17.92 rows=10 width=83) (actual
time=1612.000..1702.000 rows=10 loops=1)"
"  ->  Index Scan using island_history_date_stock_time on
island_history  (cost=0.00..183099.72 rows=102166 width=83) (actual
time=1612.000..1702.000 rows=10 loops=1)"
"        Index Cond: ((date = '2004-09-07'::date) AND ((stock)::text =
'ORCL'::text))"
"Total runtime: 1702.000 ms"


Ok, so for 100,000 rows  it decides to use the index and returns very
quicktly.. now for

 explain analyze select * from history where date='2004-09-07' and
stock='MSFT' LIMIT 10;

"Limit  (cost=0.00..14.30 rows=10 width=83) (actual
time=346759.000..346759.000 rows=10 loops=1)"
"  ->  Seq Scan on island_history  (cost=0.00..417867.13 rows=292274
width=83) (actual time=346759.000..346759.000 rows=10 loops=1)"
"        Filter: ((date = '2004-09-07'::date) AND ((stock)::text =
'MSFT'::text))"
"Total runtime: 346759.000 ms"

Nearly 8 minutes.. Why would it take this long? Is there anything else
I can do to debug this?

When I set enable_seqscan to OFF and force everything to use the index
every stock I query returns within 100ms, but turn seqscan back ON and
its back up to taking several minutes for non-index using plans.

Any ideas?
--Stephen


On Tue, 14 Sep 2004 21:27:55 +0200, Pierre-Frédéric Caillaud
<lists(at)boutiquenumerique(dot)com> wrote:
> 
> >> I have a table with ~8 million rows and I am executing a query which
> >> should return about ~800,000 rows. The problem is that as soon as I
> >> execute the query it absolutely kills my machine and begins swapping
> >> for 5 or 6 minutes before it begins returning results. Is postgres
> >> trying to load the whole query into memory before returning anything?
> >> Also, why would it choose not to use the index? It is properly
> >> estimating the # of rows returned. If I set enable_seqscan to off it
> >> is just as slow.
> 
>         1; EXPLAIN ANALYZE.
> 
>         Note the time it takes. It should not swap, just read data from the disk
> (and not kill the machine).

In response to

Responses

pgsql-performance by date

Next:From: Joshua D. DrakeDate: 2004-09-17 03:14:16
Subject: Re: Large # of rows in query extremely slow, not using
Previous:From: mudfootDate: 2004-09-16 23:00:19
Subject: Re: Article about PostgreSQL and RAID in Brazil

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group