pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedSequential scan evaluating function for each row, seemingly needlessly
2+ messages / 2 participants
[nested] [flat]
* Sequential scan evaluating function for each row, seemingly needlessly
@ 2010-09-07 19:15 Bryce Nesbitt <bryce2@obviously.com>
0 siblings, 1 reply; 2+ messages in thread
From: Bryce Nesbitt @ 2010-09-07 19:15 UTC (permalink / raw)
To: pgsql-sql
On psql 8.3.9, I ran a limited query limited to 5 results. There was a
moderately expensive function call
which I expected to be called 5 times, but was apparently called for
each row of the sequential scan. Why?
preproduction=> explain analyze select url(context_key) from extractq
order by add_date desc limit 5;
QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------------
Limit (cost=19654.53..19654.54 rows=5 width=12) (actual
time=10001.976..10001.990 rows=5 loops=1)
-> Sort (cost=19654.53..19826.16 rows=68651 width=12) (actual
time=10001.972..10001.976 rows=5 loops=1)
Sort Key: add_date
Sort Method: top-N heapsort Memory: 25kB
-> Seq Scan on extractq (cost=0.00..18514.26 rows=68651
width=12) (actual time=19.145..9770.689 rows=73550 loops=1)
Total runtime: 10002.150 ms
(6 rows)
preproduction=> explain analyze select context_key from extractq order
by add_date desc limit 5;
QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------
Limit (cost=2491.78..2491.79 rows=5 width=12) (actual
time=250.188..250.203 rows=5 loops=1)
-> Sort (cost=2491.78..2663.41 rows=68651 width=12) (actual
time=250.184..250.188 rows=5 loops=1)
Sort Key: add_date
Sort Method: top-N heapsort Memory: 25kB
-> Seq Scan on extractq (cost=0.00..1351.51 rows=68651
width=12) (actual time=0.015..145.432 rows=73557 loops=1)
Total runtime: 250.450 ms
(6 rows)
preproduction=> select version();
version
------------------------------------------------------------------------------------------------
PostgreSQL 8.3.9 on x86_64-pc-linux-gnu, compiled by GCC gcc-4.3.real
(Debian 4.3.2-1.1) 4.3.2
^ permalink raw reply [nested|flat] 2+ messages in thread
* Re: Sequential scan evaluating function for each row, seemingly needlessly
@ 2010-09-07 20:31 Tom Lane <tgl@sss.pgh.pa.us>
parent: Bryce Nesbitt <bryce2@obviously.com>
0 siblings, 0 replies; 2+ messages in thread
From: Tom Lane @ 2010-09-07 20:31 UTC (permalink / raw)
To: Bryce Nesbitt <bryce2@obviously.com>; +Cc: pgsql-sql
Bryce Nesbitt <bryce2@obviously.com> writes:
> On psql 8.3.9, I ran a limited query limited to 5 results. There was a
> moderately expensive function call
> which I expected to be called 5 times, but was apparently called for
> each row of the sequential scan. Why?
Given the plan:
> Limit (cost=19654.53..19654.54 rows=5 width=12) (actual
> time=10001.976..10001.990 rows=5 loops=1)
> -> Sort (cost=19654.53..19826.16 rows=68651 width=12) (actual
> time=10001.972..10001.976 rows=5 loops=1)
> Sort Key: add_date
> Sort Method: top-N heapsort Memory: 25kB
> -> Seq Scan on extractq (cost=0.00..18514.26 rows=68651
> width=12) (actual time=19.145..9770.689 rows=73550 loops=1)
> Total runtime: 10002.150 ms
> (6 rows)
any interesting work is going to be done at the seqscan level. Sort
just sorts, and Limit just limits; neither do any user-defined
calculations. So yeah, your functions got run for every row of the
table. (This isn't totally a PG aberration, btw: if you read the SQL
spec closely you'll discover that ORDER BY is defined to happen after
any calculations specified in the SELECT list.)
You could try something like
select my_expensive_function(...), etc, etc from
(select * from some-tables order by foo limit n) ss;
where the inner select list just pulls the columns you'll need in
the outer calculations.
regards, tom lane
^ permalink raw reply [nested|flat] 2+ messages in thread
end of thread, other threads:[~2010-09-07 20:31 UTC | newest]
Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2010-09-07 19:15 Sequential scan evaluating function for each row, seemingly needlessly Bryce Nesbitt <bryce2@obviously.com>
2010-09-07 20:31 ` Tom Lane <tgl@sss.pgh.pa.us>
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