Postgres Pro
Downloads
Home   >   mailing lists

Thread: indexing issues: what's the fasted join method?

indexing issues: what's the fasted join method?

From
Zachariah Baum
Date:
I'm running PostgreSQL 6.4 on Linux, and have the following database setup:

i have 2 tables, table1 and table2, both with an 'id' field, and both with
  an index on the 'id' field.
Usually my selects involve a join between the tables, but the join is quite
  slow.  I played around with the EXPLAIN command, and got the following
  results:

 EXPLAIN SELECT table1.id FROM table1,table2 WHERE table1.id = 100 AND table2.id = 100\g
NOTICE:  QUERY PLAN:

Nested Loop  (cost=4.10 size=2 width=8)
  ->  Index Scan using table1_idx3 on table1  (cost=2.05 size=1 width=8)
  ->  Index Scan using table2_idx on table2  (cost=2.05 size=2 width=0)

Which was to be expected, since the 2 indices that are used are on the 'id'
  field.

However, when I also get these disappointing results:
EXPLAIN SELECT table1.version FROM table1,table2 WHERE table2.id = table1.id\g
NOTICE:  QUERY PLAN:

Hash Join  (cost=92.95 size=772 width=16)
  ->  Seq Scan on table1  (cost=37.44 size=771 width=12)
  ->  Hash  (cost=0.00 size=0 width=0)
        ->  Seq Scan on table2  (cost=18.03 size=304 width=4)

How come it doesn't use the indices here?  Ideas?  Is there a better way to do
  this?
--

                          Yes is a BARGAIN
                          No is a TURNOFF  - Wire
--Zachariah - Studio Archetype - 415-659-4435 - mailto:zack@studioarchetype.com

Re: [ADMIN] indexing issues: what's the fasted join method?

From
David Hartwig
Date:
Did you do a VACUUM ANALYZE?

Check out man page on: vacuum

Zachariah Baum wrote:

> I'm running PostgreSQL 6.4 on Linux, and have the following database setup:
>
> i have 2 tables, table1 and table2, both with an 'id' field, and both with
>   an index on the 'id' field.
> Usually my selects involve a join between the tables, but the join is quite
>   slow.  I played around with the EXPLAIN command, and got the following
>   results:
>
>  EXPLAIN SELECT table1.id FROM table1,table2 WHERE table1.id = 100 AND table2.id = 100\g
> NOTICE:  QUERY PLAN:
>
> Nested Loop  (cost=4.10 size=2 width=8)
>   ->  Index Scan using table1_idx3 on table1  (cost=2.05 size=1 width=8)
>   ->  Index Scan using table2_idx on table2  (cost=2.05 size=2 width=0)
>
> Which was to be expected, since the 2 indices that are used are on the 'id'
>   field.
>
> However, when I also get these disappointing results:
> EXPLAIN SELECT table1.version FROM table1,table2 WHERE table2.id = table1.id\g
> NOTICE:  QUERY PLAN:
>
> Hash Join  (cost=92.95 size=772 width=16)
>   ->  Seq Scan on table1  (cost=37.44 size=771 width=12)
>   ->  Hash  (cost=0.00 size=0 width=0)
>         ->  Seq Scan on table2  (cost=18.03 size=304 width=4)
>
> How come it doesn't use the indices here?  Ideas?  Is there a better way to do
>   this?
> --
>
>                           Yes is a BARGAIN
>                           No is a TURNOFF  - Wire
> --Zachariah - Studio Archetype - 415-659-4435 - mailto:zack@studioarchetype.com

Re: [ADMIN] indexing issues: what's the fasted join method?

From
Zachariah Baum
Date:
daveh@insightdist.com said:
> Did you do a VACUUM ANALYZE?

that worked!  Thanks a lot.
--

                          Yes is a BARGAIN
                          No is a TURNOFF  - Wire
--Zachariah - Studio Archetype - 415-659-4435 - mailto:zack@studioarchetype.com