From: | Alessandro Vincelli <a(dot)vincelli(at)w4b(dot)it> |
---|---|
To: | pgsql-general(at)postgresql(dot)org |
Subject: | Not use index on setof from Sql Function |
Date: | 2005-03-09 15:49:28 |
Message-ID: | 422F1B08.3000903@w4b.it |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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(dot)vincelli(at)w4b(dot)it
tel: 055-2654270
From | Date | Subject | |
---|---|---|---|
Next Message | Mican Bican | 2005-03-09 15:54:40 | java is locked when select for update |
Previous Message | Gordan Bobic | 2005-03-09 15:09:13 | Re: Triggers and User Defined Trigger Functions |