Re: INDEX and JOINs

Il Friday 26 October 2007 13:56:20 Martijn van Oosterhout ha scritto:
> On Fri, Oct 26, 2007 at 01:10:42PM +0200, Reg Me Please wrote:
> > prove=# explain analyze SELECT * from t_dati natural left join t_campi
> > where tabe_id='CONTE';
> > QUERY PLAN
> > -------------------------------------------------------------------------
> >----------------------------------------------------- Hash Join
> > (cost=3.95..382140.91 rows=274709 width=91) (actual
> > time=1.929..57713.305 rows=92 loops=1)
> > Hash Cond: (t_dati.camp_id = t_campi.camp_id)
>
> Neither of the columns are indexed according to the schema you sent so
> that's the problem. Or you broke something while "translating".
>
> > (I translated the table and column names. The substance is the same.)
>
> Try not translating, and we might get somewhere...
>
> Have a nice day,

prove=# \d t_dati
Tabella "public.t_dati"
Colonna | Tipo | Modificatori
---------------+--------------------------+----------------------------------------------------------
elem_id | bigint | not null
camp_id | text | not null
dato_t | text | not null
dato_r | double precision |
dato_validita | timestamp with time zone | not null
default '-infinity'::timestamp with time zone
dato_scadenza | timestamp with time zone | not null
default 'infinity'::timestamp with time zone
dato_flag | boolean | not null default true
dato_data | timestamp with time zone | not null default now()
dato_id | bigint | not null default
nextval('t_dati_dato_id_seq'::regclass)
Indici:
"t_dati_pkey" PRIMARY KEY, btree (dato_id)
"i_dati_0" btree (elem_id)
"i_dati_1" btree (camp_id)
"i_dati_2" btree (dato_t text_pattern_ops)
"i_dati_3" btree (dato_flag, dato_validita, dato_scadenza)
"i_dati_4" btree (dato_data)
Vincoli di integrità referenziale
"t_dati_camp_id_fkey" FOREIGN KEY (camp_id) REFERENCES t_campi(camp_id)

prove=# \d t_campi
Tabella "public.t_campi"
Colonna | Tipo | Modificatori
---------+------+--------------
tabe_id | text | not null
colo_id | text | not null
camp_id | text | not null
Indici:
"t_campi_pkey" PRIMARY KEY, btree (camp_id)
"i_t_campi_0" btree (tabe_id)
Vincoli di integrità referenziale
"t_campi_colo_id_fkey" FOREIGN KEY (colo_id) REFERENCES t_colonne(colo_id)
"t_campi_tabe_id_fkey" FOREIGN KEY (tabe_id) REFERENCES t_tabelle(tabe_id)

They seems to be indexed.