Re: Index not used in join.. (example included) - Mailing list pgsql-novice

From A. Kretschmer
Subject Re: Index not used in join.. (example included)
Date
Msg-id 20090918110210.GC1841@a-kretschmer.de
Whole thread Raw
In response to Index not used in join.. (example included)  (Ron Arts <ron.arts@neonova.nl>)
Responses Re: Index not used in join.. (example included)  (Ron Arts <ron.arts@neonova.nl>)
List pgsql-novice
In response to Ron Arts :
> Hi,
>
> I don't understand why my query doesn't use the index on the name column:
>
> CREATE TABLE celeb (
>  id serial,
>  name varchar(255) NOT NULL,
>  PRIMARY KEY  (id)
> );
>
> create index celeb_name_index on celeb (name);
> INSERT INTO celeb (name) values ('Cameron Diaz');
> INSERT INTO celeb (name) values ('Angelina Jolie');
> INSERT INTO celeb (name) values ('Reese Witherspoon');
>
> CREATE TABLE detail (
>  id serial,
>  name varchar(255) NOT NULL,
>  award date,
>  PRIMARY KEY  (id)
> );
>
> create index detail_name_index on detail (name);
> INSERT INTO detail (name, award) values ('Cameron Diaz', '2009-01-01');
> INSERT INTO detail (name, award) values ('Cameron Diaz', '2009-02-01');
> INSERT INTO detail (name, award) values ('Cameron Diaz', '2009-03-01');
> INSERT INTO detail (name, award) values ('Angelina Jolie', '2009-01-01');
> INSERT INTO detail (name, award) values ('Angelina Jolie', '2009-02-01');
> INSERT INTO detail (name, award) values ('Angelina Jolie', '2009-03-01');
> INSERT INTO detail (name, award) values ('Angelina Jolie', '2009-04-01');
> INSERT INTO detail (name, award) values ('Reese Witherspoon', '2009-01-01');
> INSERT INTO detail (name, award) values ('Reese Witherspoon', '2009-02-01');
> INSERT INTO detail (name, award) values ('Reese Witherspoon', '2009-03-01');
> INSERT INTO detail (name, award) values ('Reese Witherspoon', '2009-04-01');
> INSERT INTO detail (name, award) values ('Reese Witherspoon', '2009-05-01');
>
>
> stium=# explain select * from celeb left join detail on detail.name =
> celeb.name;
>                               QUERY PLAN
> -----------------------------------------------------------------------
>  Hash Left Join  (cost=11.75..25.30 rows=140 width=1044)
>    Hash Cond: (("outer".name)::text = ("inner".name)::text)
>    ->  Seq Scan on celeb  (cost=0.00..11.40 rows=140 width=520)
>    ->  Hash  (cost=11.40..11.40 rows=140 width=524)
>          ->  Seq Scan on detail  (cost=0.00..11.40 rows=140 width=524)
> (5 rows)
>
>
> Now, why does it use sequential scans?? I don't get it. It also does this
> on a very large table which is ANALYZE'd.

First, bad design. Both tables contains the text-column name, waste of
space. You should better use the PK from celeb as foreign key in detail.

Furthermore, all distinct values for name in detail are in the result
set, so a seq-scan is the best solution.

You can try to force an index-scan by disabling the seq-scan (set
enable_seqscan='no';) and re-run the query.


Andreas
--
Andreas Kretschmer
Kontakt:  Heynitz: 035242/47150,   D1: 0160/7141639 (mehr: -> Header)

pgsql-novice by date:

Previous
From: Ron Arts
Date:
Subject: Index not used in join.. (example included)
Next
From: Ron Arts
Date:
Subject: Re: Index not used in join.. (example included)