Re: INDEX and JOINs

Il Friday 26 October 2007 13:05:10 Martijn van Oosterhout ha scritto:
> On Fri, Oct 26, 2007 at 12:34:06PM +0200, Reg Me Please wrote:
> > it's very fast (of course!). But when I run:
> >
> > SELECT * FROM T_DATA NATURAL JOIN T_FIELDS WHERE TABL_ID='TABL';
> >
> > it's very slow.
> > The EXPLAIN says that in the second case it has to do a sequential
> > scan on T_DATA. And this explains the timing.
> > Is there a way to avoid such a behaviour by acting on indexes?
>
> Firstly, have you run ANALYZE recently. Secondly, you'll have to show
> us the output of EXPLAIN ANALYZE if you want some useful help.
>
> Have a nice day,

Yes, I'm often runing analyze while trying to sort this kind of
things out.

This is the output:

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)
-> Seq Scan on t_dati (cost=0.00..326851.72 rows=14010172 width=73)
(actual time=0.028..43814.946 rows=14011712 loops=1)
-> Hash (cost=3.91..3.91 rows=3 width=33) (actual time=0.129..0.129
rows=3 loops=1)
-> Seq Scan on t_campi (cost=0.00..3.91 rows=3 width=33) (actual
time=0.040..0.121 rows=3 loops=1)
Filter: (tabe_id = 'CONTE'::text)
Total runtime: 57713.449 ms

(I translated the table and column names. The substance is the same.)