From: | Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> |
---|---|
To: | Brent Verner <brent(at)rcfile(dot)org> |
Cc: | pgsql-hackers(at)postgresql(dot)org |
Subject: | Re: function optimization ??? |
Date: | 2001-01-24 17:14:20 |
Message-ID: | 13152.980356460@sss.pgh.pa.us |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-hackers |
Brent Verner <brent(at)rcfile(dot)org> writes:
> calling it as:
> SELECT p.*, p.book_info.title FROM pricing p WHERE vista_ans='POD';
> background and observation:
> the pricing table is fairly large, but only a small number meet
> "WHERE vista_ans='POD'". I can select all where vista_ans='POD'
> very quickly (.2 sec), but adding in the get_book(pricing) call
> slows this down to about 20sec. I can, with an external sql query,
> select all of the desired records in about 1 sec, so it appears
> to me that the function is being called regardless of whether
> or not the WHERE clause is being satisfied.
This conclusion is absolutely false: the SELECT target list is NOT
evaluated except at rows where the WHERE condition is satisfied.
I suspect the real problem is that the select inside the function
is not being done as efficiently as you'd like. How big is
catalog_general, and would a sequential scan over it inside the
function account for the performance discrepancy?
IIRC, 7.0.* is not very bright about using indexscans in situations
where the righthand side of the WHERE clause is anything more complex
than a literal constant or simple parameter reference ($n). The
fieldselect you have here would be enough to defeat the indexscan
recognizer. This is fixed in 7.1, however. For now, you could
declare book_info as taking a simple datum and invoke it as
p.vista_isbn.book_info.title
BTW, star_isbn and vista_isbn are the same datatype, I trust, else
that might cause failure to use an indexscan too.
regards, tom lane
From | Date | Subject | |
---|---|---|---|
Next Message | Peter Eisentraut | 2001-01-24 18:03:32 | Re: LEFT JOIN in pg_dumpall is a bug |
Previous Message | Bruce Momjian | 2001-01-24 17:01:17 | Re: Open 7.1 items |