From: | Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> |
---|---|
To: | Bryce Nesbitt <bryce2(at)obviously(dot)com> |
Cc: | pgsql-sql(at)postgresql(dot)org |
Subject: | Re: Sequential scan evaluating function for each row, seemingly needlessly |
Date: | 2010-09-07 20:31:39 |
Message-ID: | 25196.1283891499@sss.pgh.pa.us |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-sql |
Bryce Nesbitt <bryce2(at)obviously(dot)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
From | Date | Subject | |
---|---|---|---|
Next Message | Lew | 2010-09-08 02:14:31 | Re: is there a distinct function for comma lists ? |
Previous Message | Bryce Nesbitt | 2010-09-07 19:15:21 | Sequential scan evaluating function for each row, seemingly needlessly |