From: Jim C. Nasby <jnasby@pervasive.com>
To: K C Lau <kclau60@netvigator.com>
Cc: Simon Riggs <simon@2ndquadrant.com>
Cc: pgsql-performance@postgresql.org
Subject: Re: SELECT LIMIT 1 VIEW Performance Issue
Date: Tue, 4 Oct 2005 16:15:41 -0500
Message-ID: <20051004211541.GB40138@pervasive.com> (raw)
In-Reply-To: <6.2.1.2.0.20050923195457.02c4fd30@localhost>
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>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: jnasby@pervasive.com, kclau60@netvigator.com, simon@2ndquadrant.com
Subject: Re: SELECT LIMIT 1 VIEW Performance Issue
In-Reply-To: <20051004211541.GB40138@pervasive.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
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