Postgres Pro
Downloads
Home   >   mailing lists

Thread: Index Scanning

Index Scanning

From
Enrico
Date:
Hi, I have this query

select TB.id_int,TR.codice_art,importo,cod_iva,prezzo,qta as qta
from bolle_carico_testata TB inner join tmp_righe_bolle_carico TR on (TB.id_bolla_rem=TR.id_bolla_rem);

and these are indexes on tables.

# \d tmp_righe_bolle_carico
Indexes:
    "tmpidx1" btree (id_bolla_rem)

gepv=# \d bolle_carico_testata
    "idxbct4" btree (id_bolla_rem)


My query plan is:

QUERY PLAN

-------------------------------------------------------------------------------------------------------------------------------------
 Hash Join  (cost=7.17..888.67 rows=22420 width=67) (actual time=1.059..118.090 rows=22420 loops=1)
   Hash Cond: (tr.id_bolla_rem = tb.id_bolla_rem)
   ->  Seq Scan on tmp_righe_bolle_carico tr  (cost=0.00..545.20 rows=22420 width=67) (actual time=0.030..32.963
rows=22420loops=1) 
   ->  Hash  (cost=6.54..6.54 rows=254 width=16) (actual time=0.980..0.980 rows=254 loops=1)
         ->  Seq Scan on bolle_carico_testata tb  (cost=0.00..6.54 rows=254 width=16) (actual time=0.025..0.500
rows=254loops=1) 
 Total runtime: 141.864 ms
(6 rows)

Can anybody tell me why I have two Seq scans instead of two Ind. scan?
And how can I do to have two ind. scan?

Thanks in advantage.

Enrico

--
If Bill Gates had a penny for everytime Windows crashed,he'd be a multi-billionaire by now .......oh look, he already
is!!!! 
scotty@linuxtime.it - Skype:sscotty71
http://www.linuxtime.it/enricopirozzi

Re: Index Scanning

From
Richard Huxton
Date:
Enrico wrote:
> Hi, I have this query
>
> select TB.id_int,TR.codice_art,importo,cod_iva,prezzo,qta as qta
> from bolle_carico_testata TB inner join tmp_righe_bolle_carico TR on (TB.id_bolla_rem=TR.id_bolla_rem);
[snip plan]
> Can anybody tell me why I have two Seq scans instead of two Ind. scan?
> And how can I do to have two ind. scan?

You're fetching all the rows from both tables - what would an index scan
gain you?

--
   Richard Huxton
   Archonet Ltd

Re: Index Scanning

From
Richard Huxton
Date:
Don't forget to cc the list

Enrico wrote:
> On Wed, 03 Jan 2007 10:55:03 +0000
> Richard Huxton <dev@archonet.com> wrote:
>
>> You're fetching all the rows from both tables - what would an index scan
>> gain you
>
> there is a join

Yes, there is a join. Between all the rows in tmp_righe_bolle_carico
(tr) and all the matches in bolle_carico_testata (tb). It looks like
there is one row in (tb) that matches each in (tr).

What do you expect two index scans over both tables would gain you?
You seem to be saying that you think:
   fetching 22420 index entries on tr + fetching 22420 rows in tr
+ fetching 22420 index entries on tb + fetching 22420 rows in tb
+ merge-join
would be faster than the hash-join you've got. I think it's unlikely
that's the case.

If you issue "set enable_seqscan = off" before the query that should
force it to use the indexes first. What does that plan show you?

--
   Richard Huxton
   Archonet Ltd

Re: Index Scanning

From
Enrico
Date:
On Wed, 03 Jan 2007 12:21:31 +0000
Richard Huxton <dev@archonet.com> wrote:

> Don't forget to cc the list

Ok thanks :)
>

> If you issue "set enable_seqscan = off" before the query that should
> force it to use the indexes first. What does that plan show you?

I try and then I post the results.

Regards Enrico




--
If Bill Gates had a penny for everytime Windows crashed,he'd be a multi-billionaire by now .......oh look, he already
is!!!! 
scotty@linuxtime.it - Skype:sscotty71
http://www.linuxtime.it/enricopirozzi