Re: Avoiding a seq scan on a table.

On Mon, January 14, 2008 12:35 pm, LWATCDR wrote:
> that is very odd since that table has 141 records in it.
>
> here is a different query that I ran.
> SELECT COUNT(*) FROM rma where terminatedate is NULL;
> This returns a value of 254 for the count but this is what I get from
> explain.
>
> Aggregate (cost=219.77..219.78 rows=1 width=0)
> -> Seq Scan on rma (cost=0.00..219.11 rows=264 width=0)
> Filter: (terminatedate IS NULL)
> This says that rows =1 but returns 254 rows of data?
> The table contains over 7000 rows.

Spend some time reading this page:
http://www.postgresql.org/docs/8.2/interactive/using-explain.html

This query returns one row: The _count_ of the number of rows. This it
gets from a sequence scan, which that returns 264 rows, approximately.
(The explain isn't doing the query itself, it is only looking at the
statistics it has, and telling you what it _would_ to, and estimating
costs based on that.) That scan has a filter on it, you initial
condition.

In reality when you run the query it will return 254 rows in the scan, and
then do the 'count' aggregate operation on those.

In your original, the cost was ~12. That's a very low cost, really. It
is unlikely any index plan will beat that, regardless of the database.
The above query would likely be sped up somewhat by an index, but how much
is a question.

A sequential scan is computationally cheap, and of predictable disk cost.
An index scan is computationally much more expensive, has a somewhat
unpredictable disk cost, and has a non-zero startup cost. The planner
will prefer the first unless it is sure the second will do better.

Daniel T. Staal

---------------------------------------------------------------
This email copyright the author. Unless otherwise noted, you
are expressly allowed to retransmit, quote, or otherwise use
the contents for non-commercial purposes. This copyright will
expire 5 years after the author's death, or in 30 years,
whichever is longer, unless such a period is in excess of
local copyright law.
---------------------------------------------------------------