Обсуждение: Function to either return one or all records

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

>
> 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
andoptimized out from OR. In case of function the query was planned before substituting parameters, so the OR was still
thereand 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

"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

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
|

> -----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