Re: count is ten times faster

From: Kevin Grittner
Subject: Re: count is ten times faster
Date: ,
Msg-id: 4BC587E00200002500030859@gw.wicourts.gov
(view: Whole thread, Raw)
In response to: count is ten times faster  ("Sabin Coanda")
List: pgsql-performance

"Sabin Coanda" <> wrote:

> How do you explain the cost is about ten times lower in the 2nd
> query than the first ?

To elaborate on Pierre's answer:

In the first query, you scan the entire table and execute the "f1"
function on each row.  In the second query you pass the entire table
just counting visible tuples and then run the "f1" function once,
and use the resulting value to scan an index on which it expects to
find one row.

It estimates the cost of running the "f1" function 7.7 million times
as being roughly ten times the cost of scanning the table.  Now,
this is all just estimates; if they don't really reflect the
relative cost of *running* the two queries, you might want to adjust
costs factors -- perhaps the estimated cost of the "f1" function.

-Kevin


pgsql-performance by date:

From: Krzysztof Kardas
Date:
Subject: Re: PostgreSQL with Zabbix - problem of newbe
From: Dave Crooke
Date:
Subject: JDBC question for PG 8.3.9