Date: Tue, 4 Oct 2005 16:15:41 -0500
From: "Jim C. Nasby" <jnasby@pervasive.com>
To: K C Lau <kclau60@netvigator.com>
Cc: Simon Riggs <simon@2ndquadrant.com>,
	pgsql-performance@postgresql.org
Subject: Re: SELECT LIMIT 1 VIEW Performance Issue
Message-ID: <20051004211541.GB40138@pervasive.com>
References: <6.2.1.2.0.20050615100524.04f9d6a8@localhost>
	<6.2.1.2.0.20050922102035.07bcf2e0@localhost>
	<1127378456.4145.76.camel@localhost.localdomain>
	<6.2.1.2.0.20050922181009.02c36ca8@localhost>
	<1127474141.19345.10.camel@localhost.localdomain>
	<6.2.1.2.0.20050923195457.02c4fd30@localhost>
Mime-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Disposition: inline
In-Reply-To: <6.2.1.2.0.20050923195457.02c4fd30@localhost>
User-Agent: Mutt/1.5.9i

On Fri, Sep 23, 2005 at 08:17:03PM +0800, K C Lau wrote:
> esdt=> create or replace function player_max_atdate (varchar(32)) returns 
> varchar(32) as $$
> esdt$>  select distinct on (PlayerID) AtDate from player where PlayerID= $1 
> order by PlayerID desc, AtDate desc limit 1;
> esdt$> $$ language sql immutable;
> CREATE FUNCTION

That function is not immutable, it should be defined as stable.

> esdt=> create or replace view VCurPlayer3 as select * from Player where 
> AtDate = player_max_atdate(PlayerID);
> CREATE VIEW
> esdt=> explain analyze select PlayerID,AtDate from VCurPlayer3 where 
> PlayerID='22220';
> 
>  Index Scan using pk_player on player  (cost=0.00..1331.83 rows=9 
> width=23) (actual time=76.660..76.664 rows=1 loops=1)
>    Index Cond: ((playerid)::text = '22220'::text)
>    Filter: ((atdate)::text = (player_max_atdate(playerid))::text)
>  Total runtime: 76.716 ms
> 
> Why wouldn't the function get the row as quickly as the direct sql does?

PostgreSQL doesn't pre-compile functions, at least not until 8.1 (and
I'm not sure how much those are pre-compiled, though they are
syntax-checked at creation). Do you get the same result time when you
run it a second time? What time do you get from running just the
function versus the SQL in the function?

Also, remember that every layer you add to the cake means more work for
the database. If speed is that highly critical you'll probably want to
not wrap things in functions, and possibly not use views either.

Also, keep in mind that getting below 1ms doesn't automatically mean
you'll be able to scale to 1000TPS. Things will definately change when
you load the system down, so if performance is that critical you should
start testing with the system under load if you're not already.
-- 
Jim C. Nasby, Sr. Engineering Consultant      jnasby@pervasive.com
Pervasive Software      http://pervasive.com    work: 512-231-6117
vcard: http://jim.nasby.net/pervasive.vcf       cell: 512-569-9461