Обсуждение: Index Scanning
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
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
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
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