Re: Index not used in join.. (example included)

From: Ron Arts <ron(dot)arts(at)neonova(dot)nl>
To: "A(dot) Kretschmer" <andreas(dot)kretschmer(at)schollglas(dot)com>
Cc: PostgreSQL List - Novice <pgsql-novice(at)postgresql(dot)org>
Subject: Re: Index not used in join.. (example included)
Date: 2009-09-18 12:21:06
Message-ID: 4AB37B32.8000402@neonova.nl
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-novice

A. Kretschmer schreef:
> 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.
>

Yes, bad design, although in the real tables there are multiple records
for each celeb. Still bad design, I know.

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

If I add a column to celeb, say 'birthdate', and use that in the where clause
it still uses seq scan, even though not all distinct values are in the result set,
aren't they?

stium=# explain select * from celeb left join detail on detail.name = celeb.name where celeb.birthdate > '1973-01-01';
QUERY PLAN
-----------------------------------------------------------------------
Merge Left Join (cost=29.45..30.85 rows=47 width=1048)
Merge Cond: ("outer"."?column4?" = "inner"."?column4?")
-> Sort (cost=13.06..13.17 rows=47 width=524)
Sort Key: (celeb.name)::text
-> Seq Scan on celeb (cost=0.00..11.75 rows=47 width=524)
Filter: (birthdate > '1973-01-01'::date)
-> Sort (cost=16.39..16.74 rows=140 width=524)
Sort Key: (detail.name)::text
-> Seq Scan on detail (cost=0.00..11.40 rows=140 width=524)
(9 rows)

I'd expect it would take all records above the given date, join the
records from detail using the index on that. Am I wrong?

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

That does force it to use the index, but doesn't speed up my query.
>
> Andreas

Thanks for responding.

Ron

--
NeoNova BV
innovatieve internetoplossingen

http://www.neonova.nl Science Park 140 1098 XG Amsterdam
info: 020-5611300 servicedesk: 020-5611302 fax: 020-5611301
KvK Amsterdam 34151241

Op dit bericht is de volgende disclaimer van toepassing:
http://www.neonova.nl/maildisclaimer

In response to

Responses

Browse pgsql-novice by date

  From Date Subject
Next Message Michael Wood 2009-09-18 12:38:27 Re: Index not used in join.. (example included)
Previous Message A. Kretschmer 2009-09-18 11:02:10 Re: Index not used in join.. (example included)