Re: User-defined SQL function has slower query on 7.3.3 than

Mr. Lane:

QUERY
-----
SELECT ZIPCODE
FROM LOCATIONS
WHERE
COUNTRY = 'USA' AND ZIP_DIST_MI('07306', ZIPCODE) <= 25;

I found that the 7.1.3 server performed QUERY very slowly after a VACUUM
ANALYZE. (I can't just ANALYZE in this version, right?) It's performance
was comparable to the 7.3.3 server for awhile. Then, it improved.

I don't know how to prove that an SPI query uses an index. I do know that
this SQL: select latitude, longitude from geo_zipdata where zip = $1
uses the index through PSQL.

I use an elog(NOTICE, ...) to print when the SQL get prepared, and it is
just once. geo_zipdata is never changed for the life of the database.

db=> explain
db-> select latitude, longitude from geo_zipdata where zip = '07306';
                                      QUERY PLAN                                        
-----------------------------------------------------------------------------------------Index Scan using
geo_zipdata_zip_idxon geo_zipdata  (cost=0.00..17.07
 
rows=5 width=16)  Index Cond: (zip = '07306'::character varying)
(2 rows)

I expect QUERY to need a single full table scan for each ZIPCODE. I just
think that 7500 rows should never take over a minute. PG 7.3.3 takes 9
minutes (the one time we waited for it to finish).

How many data pages could 7500 rows need? With 2 or 3 page reads, it can't
take up much memory or I/O to do that.

- Andrew

On Wed, 6 Aug 2003, Tom Lane wrote:

> Andrew Droffner <adroffne@advance.net> writes:
> > I have this QUERY (below) that PostgreSQL 7.3.X servers run MUCH slower
> > than
> > the 7.1.3 server does.
> 
> I know of no reason for that to happen.  Have you vacuum analyzed the
> 7.3 database?
> 
> > It finds the ZIPs locations with a prepared
> > (and saved) SPI query, which uses an index:
> > "select latitude, longitude from geo_zipdata where zip = $1"
> 
> How do you know it's using the index?
> 
>             regards, tom lane
> 

-- 
[ Andrew Droffner
[ Advance Publications Internet
[
[ adroffne@advance.net