pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedcount is ten times faster
3+ messages / 3 participants
[nested] [flat]
* count is ten times faster
@ 2010-04-13 12:32 Sabin Coanda <coanda@hotpop.com>
0 siblings, 2 replies; 3+ messages in thread
From: Sabin Coanda @ 2010-04-13 12:32 UTC (permalink / raw)
To: pgsql-performance
Hi there,
I have a simple aggregate query: SELECT count("PK_ID") AS "b1" FROM "tbA"
WHERE "PK_ID" > "f1"( 'c1' ), which has the following execution plan:
"Aggregate (cost=2156915.42..2156915.43 rows=1 width=4)"
" -> Seq Scan on "tbA" (cost=0.00..2137634.36 rows=7712423 width=4)"
" Filter: ("PK_ID" > "f1"('c1'::character varying))"
I tried to get the same result with the following query:
SELECT (
SELECT count("PK_ID") AS "b1" FROM "tbA" ) -
(
SELECT count("PK_ID") AS "b1"
FROM "tbA"
WHERE "PK_ID" <= "f1"( 'c1' )
)
with the execution plan:
"Result (cost=248952.95..248952.96 rows=1 width=0)"
" InitPlan"
" -> Aggregate (cost=184772.11..184772.12 rows=1 width=4)"
" -> Seq Scan on "tbA" (cost=0.00..165243.49 rows=7811449
width=4)"
" -> Aggregate (cost=64180.81..64180.82 rows=1 width=4)"
" -> Index Scan using "tbA_pkey" on "tbA" (cost=0.25..63933.24
rows=99026 width=4)"
" Index Cond: ("PK_ID" <= "f1"('c1'::character varying))"
How do you explain the cost is about ten times lower in the 2nd query than
the first ?
TIA,
Sabin
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: count is ten times faster
@ 2010-04-13 18:09 Pierre C <lists@peufeu.com>
parent: Sabin Coanda <coanda@hotpop.com>
1 sibling, 0 replies; 3+ messages in thread
From: Pierre C @ 2010-04-13 18:09 UTC (permalink / raw)
To: Sabin Coanda <coanda@hotpop.com>; pgsql-performance
> How do you explain the cost is about ten times lower in the 2nd query
> than the first ?
Function call cost ?
Can you EXPLAIN ANALYZE ?
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: count is ten times faster
@ 2010-04-14 14:16 Kevin Grittner <Kevin.Grittner@wicourts.gov>
parent: Sabin Coanda <coanda@hotpop.com>
1 sibling, 0 replies; 3+ messages in thread
From: Kevin Grittner @ 2010-04-14 14:16 UTC (permalink / raw)
To: Sabin Coanda <coanda@hotpop.com>; pgsql-performance
"Sabin Coanda" <coanda@hotpop.com> 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
^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2010-04-14 14:16 UTC | newest]
Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2010-04-13 12:32 count is ten times faster Sabin Coanda <coanda@hotpop.com>
2010-04-13 18:09 ` Pierre C <lists@peufeu.com>
2010-04-14 14:16 ` Kevin Grittner <Kevin.Grittner@wicourts.gov>
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