Postgres Pro
Downloads
Home   >   mailing lists

Thread: Not use index on setof from Sql Function

Not use index on setof from Sql Function

From
Alessandro Vincelli
Date:
This simple function return setof table "DatiRegione"


CREATE OR REPLACE FUNCTION public._stato_dati_regione_rt(text)
   RETURNS SETOF "DatiRegione" AS
'
    SELECT DISTINCT  ON ("Codice_regione")  dr.* FROM "DatiRegione"
'
  LANGUAGE 'sql' VOLATILE STRICT;


When I perform query on this setof, pgsql not use the indexes on table
"DatiRegion".
This is a big problem.

##################à

Query on _stato_dati_regione_rt(text) without index

QUERY PLAN
Aggregate  (cost=17.50..17.51 rows=1 width=17) (actual
time=14555.295..14555.297 rows=1 loops=1)
   ->  Function Scan on _stato_dati_regione_rt  (cost=0.00..17.50 rows=1
width=17) (actual time=14528.756..14554.753 rows=119 loops=1)
         Filter: (("Categoria" = 1) AND ("Tipologia" = 'ALB'::bpchar))
Total runtime: 14561.981 ms

######################

Query on "DatiRegione" with index

QUERY PLAN
Aggregate  (cost=672.10..672.10 rows=1 width=17) (actual
time=8.752..8.753 rows=1 loops=1)
   ->  Index Scan using idx_dr_cat_tipo on "DatiRegione"
(cost=0.00..671.64 rows=181 width=17) (actual time=0.123..6.289 rows=504
loops=1)
         Index Cond: (("Categoria" = 1) AND ("Tipologia" = 'ALB'::bpchar))
Total runtime: 9.565 ms


###########################


Can I use index on setof from _stato_dati_regione_rt(text)?

Tannks in adbace.

By,Alessandro

--
-------------------------------
Alessandro Vincelli
W4B - web for business s.r.l.
Firenze
via Pellicceria 10 - 50123
E-mail: a.vincelli@w4b.it
tel: 055-2654270

Re: Not use index on setof from Sql Function

From
Martijn van Oosterhout
Date:
On Wed, Mar 09, 2005 at 04:49:28PM +0100, Alessandro Vincelli wrote:
>
> This simple function return setof table "DatiRegione"
>
>
> CREATE OR REPLACE FUNCTION public._stato_dati_regione_rt(text)
>   RETURNS SETOF "DatiRegione" AS
> '
>     SELECT DISTINCT  ON ("Codice_regione")  dr.* FROM "DatiRegione"
> '
>  LANGUAGE 'sql' VOLATILE STRICT;

You can't do an index scan on a function. As far as the executor is
concerned, a function is an opaque object. Perhaps you actually want a
view? They allow optimisations like the one you're looking for...

Hope this helps,
--
Martijn van Oosterhout   <kleptog@svana.org>   http://svana.org/kleptog/
> Patent. n. Genius is 5% inspiration and 95% perspiration. A patent is a
> tool for doing 5% of the work and then sitting around waiting for someone
> else to do the other 95% so you can sue them.
Attachment