Received: from maia.hub.org (maia-3.hub.org [200.46.204.243]) by mail.postgresql.org (Postfix) with ESMTP id 81D106339AF for ; Tue, 7 Sep 2010 16:31:11 -0300 (ADT) Received: from mail.postgresql.org ([200.46.204.86]) by maia.hub.org (mx1.hub.org [200.46.204.243]) (amavisd-maia, port 10024) with ESMTP id 08473-06 for ; Tue, 7 Sep 2010 19:31:03 +0000 (UTC) X-Greylist: delayed 00:15:32.804692 by SQLgrey-1.7.6 Received: from out-0.mx.aerioconnect.net (out-0-2.mx.aerioconnect.net [216.240.47.62]) by mail.postgresql.org (Postfix) with ESMTP id 23917633484 for ; Tue, 7 Sep 2010 16:31:04 -0300 (ADT) Received: from idiom.com (postfix@mx0.idiom.com [216.240.32.160]) by out-0.mx.aerioconnect.net (8.13.8/8.13.8) with ESMTP id o87JFRcg025218 for ; Tue, 7 Sep 2010 12:15:27 -0700 X-Client-Authorized: MaGic Cook1e Received: from [192.168.1.88] (75-149-45-155-SFBA.hfc.comcastbusiness.net [75.149.45.155]) by idiom.com (Postfix) with ESMTP id B5CFE2D601A for ; Tue, 7 Sep 2010 12:15:26 -0700 (PDT) Message-ID: <4C868F49.5050601@obviously.com> Date: Tue, 07 Sep 2010 12:15:21 -0700 From: Bryce Nesbitt User-Agent: Mozilla/5.0 (X11; U; Linux x86_64; en-US; rv:1.9.1.11) Gecko/20100722 SeaMonkey/2.0.6 MIME-Version: 1.0 To: pgsql-sql@postgresql.org Subject: Sequential scan evaluating function for each row, seemingly needlessly Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit X-Scanned-By: MIMEDefang 2.67 on 216.240.47.51 X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=-1.9 tagged_above=-5 required=5 tests=BAYES_00=-1.9 X-Spam-Level: X-Archive-Number: 201009/14 X-Sequence-Number: 34545 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