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.