Re: Slow Query- Simple taking

On mysql the same query only takes milliseconds not seconds. That's a
big difference.

-----Original Message-----
From: Kevin Grittner [mailto:Kevin(dot)Grittner(at)wicourts(dot)gov]
Sent: Tuesday, October 19, 2010 1:59 PM
To: Ozer, Pam; pgsql-performance(at)postgresql(dot)org
Subject: Re: [PERFORM] Slow Query- Simple taking

"Ozer, Pam" <pozer(at)automotive(dot)com> wrote:

> I have the following query running on 8.4, which takes 3516 ms.
> It is very straight forward. It brings back 116412 records. The
> explain only takes 1348ms

The EXPLAIN ANALYZE doesn't have to return 116412 rows to the
client. It doesn't seem too out of line to me that it takes two
seconds to do that.

> Can someone tell me why after it runs the index scan it hen runs a
> bitmap heap scan?

Without visiting the heap it can't tell whether the tuples it has
found are visible to your query. Also, it needs to get the actual
values out of the heap.

> It should not take this long to run should it?

If you want an answer to that, we need more information. See this
page for ideas:

http://wiki.postgresql.org/wiki/SlowQueryQuestions

> If I limit the results it comes back in 300ms.

I don't find that surprising. Wouldn't you think that reading and
transmitting more rows would take more time?

-Kevin