pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedFunction to either return one or all records
5+ messages / 3 participants
[nested] [flat]
* Function to either return one or all records
@ 2005-04-20 12:41 KÖPFERL Robert <robert.koepferl@sonorys.at>
0 siblings, 0 replies; 5+ messages in thread
From: KÖPFERL Robert @ 2005-04-20 12:41 UTC (permalink / raw)
To: pgsql-sql
Hi all,
I think I have got a usual problem. I'm asking here, however, because I
wonder why it works this way.
The problem is to write a fcn that eihter returns all records or just
one/none filtered by some expression. For example get a value by id or
return all values if the given id is null.
For a table like this (id (PK) int | val int ) one would write a function
like this:
CREATE OR REPLACE FUNCTION getval(integer)
RETURNS SETOF id_val_tbl AS
$BODY$
select * from id_bal_tbl where ( $1 is null )or (id=$1 );
$BODY$
LANGUAGE 'sql' VOLATILE SECURITY DEFINER;
It works fine, however an index is never used (if just one record is
requested). The column id has a btree-Index but what aobut it. I'm wondering
how this comes and how one can overcome this limit.
I read that btree and null values do not like each other very well. And
surely, if the Select is transformed reduced to
select * from id_bal_tbl where (false)or (id=$1 );
the Index is used!
What I don't get is: When expanding parameters in the SQL-function it will
evaluate to the statement above. Bevore any data is looked up. Why is that?
Thanks again in advance
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Function to either return one or all records
@ 2005-04-20 20:02 Tambet Matiisen <t.matiisen@aprote.ee>
0 siblings, 1 reply; 5+ messages in thread
From: Tambet Matiisen @ 2005-04-20 20:02 UTC (permalink / raw)
To: KÖPFERL Robert <robert.koepferl@sonorys.at>; pgsql-sql
>
> CREATE OR REPLACE FUNCTION getval(integer)
> RETURNS SETOF id_val_tbl AS
> $BODY$
> select * from id_bal_tbl where ( $1 is null )or (id=$1 ); $BODY$
> LANGUAGE 'sql' VOLATILE SECURITY DEFINER;
>
>
> It works fine, however an index is never used (if just one
> record is requested). The column id has a btree-Index but
> what aobut it. I'm wondering how this comes and how one can
> overcome this limit.
The reason why the query worked as plain query may come from the fact that NULL IS NULL was evaluated to constant FALSE and optimized out from OR. In case of function the query was planned before substituting parameters, so the OR was still there and prevented index scan.
Standard technique is to rewrite OR queries to UNION queries. I believe PostgreSQL optimizer does not do that automatically. So you could try instead:
select * from id_bal_tbl where $1 is null
union all
select * from id_bal_tbl where id = $1;
(Note: in general you would need UNION without ALL, to keep the semantics of OR.)
Tambet
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Function to either return one or all records
@ 2005-04-20 21:06 Tom Lane <tgl@sss.pgh.pa.us>
parent: Tambet Matiisen <t.matiisen@aprote.ee>
0 siblings, 0 replies; 5+ messages in thread
From: Tom Lane @ 2005-04-20 21:06 UTC (permalink / raw)
To: Tambet Matiisen <t.matiisen@aprote.ee>; +Cc: KÖPFERL Robert <robert.koepferl@sonorys.at>; pgsql-sql
"Tambet Matiisen" <t.matiisen@aprote.ee> writes:
> Standard technique is to rewrite OR queries to UNION queries. I believe PostgreSQL optimizer does not do that automatically. So you could try instead:
> select * from id_bal_tbl where $1 is null
> union all
> select * from id_bal_tbl where id = $1;
... but that won't do anything to solve the performance problem.
Doesn't help for the second select to use an index, if the first
one grovels over the whole table anyway ...
regards, tom lane
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Function to either return one or all records
@ 2005-04-21 06:43 KÖPFERL Robert <robert.koepferl@sonorys.at>
0 siblings, 0 replies; 5+ messages in thread
From: KÖPFERL Robert @ 2005-04-21 06:43 UTC (permalink / raw)
To: ; +Cc: pgsql-sql
Hmmm, you're right.
Is there any way to do a
explain select * from id_val_tbl where false
while explain doesn't tell SEQ_SCAN?
I have to admit that I didn't performance tested this. I just saw explain
writing SEQ_SCAN.
|-----Original Message-----
|From: Tom Lane [mailto:tgl@sss.pgh.pa.us]
|Sent: Mittwoch, 20. April 2005 23:06
|To: Tambet Matiisen
|Cc: KÖPFERL Robert; pgsql-sql@postgresql.org
|Subject: Re: [SQL] Function to either return one or all records
|
|
|"Tambet Matiisen" <t.matiisen@aprote.ee> writes:
|> Standard technique is to rewrite OR queries to UNION
|queries. I believe PostgreSQL optimizer does not do that
|automatically. So you could try instead:
|
|> select * from id_bal_tbl where $1 is null
|> union all
|> select * from id_bal_tbl where id = $1;
|
|... but that won't do anything to solve the performance problem.
|Doesn't help for the second select to use an index, if the first
|one grovels over the whole table anyway ...
|
| regards, tom lane
|
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Function to either return one or all records
@ 2005-04-21 07:34 Tambet Matiisen <t.matiisen@aprote.ee>
0 siblings, 0 replies; 5+ messages in thread
From: Tambet Matiisen @ 2005-04-21 07:34 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: KÖPFERL Robert <robert.koepferl@sonorys.at>; pgsql-sql
> -----Original Message-----
> From: Tom Lane [mailto:tgl@sss.pgh.pa.us]
> Sent: Thursday, April 21, 2005 12:06 AM
> To: Tambet Matiisen
> Cc: KÖPFERL Robert; pgsql-sql@postgresql.org
> Subject: Re: [SQL] Function to either return one or all records
>
>
> "Tambet Matiisen" <t.matiisen@aprote.ee> writes:
> > Standard technique is to rewrite OR queries to UNION queries. I
> > believe PostgreSQL optimizer does not do that automatically. So you
> > could try instead:
>
> > select * from id_bal_tbl where $1 is null
> > union all
> > select * from id_bal_tbl where id = $1;
>
> ... but that won't do anything to solve the performance
> problem. Doesn't help for the second select to use an index,
> if the first one grovels over the whole table anyway ...
>
Hmm, I tought about that before sending mail to list and did a little test. My test indicated, that the seq scan was discarded, it was definitely faster than OR query. I couldn't verify plan, as EXPLAIN shows only function scan.
Tambet
^ permalink raw reply [nested|flat] 5+ messages in thread
end of thread, other threads:[~2005-04-21 07:34 UTC | newest]
Thread overview: 5+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2005-04-20 12:41 Function to either return one or all records KÖPFERL Robert <robert.koepferl@sonorys.at>
2005-04-20 20:02 Re: Function to either return one or all records Tambet Matiisen <t.matiisen@aprote.ee>
2005-04-20 21:06 ` Tom Lane <tgl@sss.pgh.pa.us>
2005-04-21 06:43 Re: Function to either return one or all records KÖPFERL Robert <robert.koepferl@sonorys.at>
2005-04-21 07:34 Re: Function to either return one or all records Tambet Matiisen <t.matiisen@aprote.ee>
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