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)
