Re: Trying to get postgres to use an index

At 10:11 PM +0100 11/6/04, Pierre-Frédéric Caillaud wrote:
>>explain select notificationID from NOTIFICATION n, ITEM i where
>>n.itemID = i.itemID;
>> QUERY PLAN
>>
>>------------------------------------------------------------------------
>>------
>> Hash Join (cost=47162.85..76291.32 rows=223672 width=44)
>> Hash Cond: ("outer".itemid = "inner".itemid)
>> -> Seq Scan on notification n (cost=0.00..12023.71 rows=223671
>>width=48)
>> -> Hash (cost=42415.28..42415.28 rows=741028 width=4)
>> -> Seq Scan on item i (cost=0.00..42415.28 rows=741028
>>width=4)
>>
>>This query takes about 20 seconds to run.
>
> Well, you're joining the entire two
>tables, so yes, the seq scan might be faster.
> Try your query with enable_seqscan=0 so
>it'll use an index scan and compare the times.
> You may be surprised to find that the
>planner has indeed made the right choice.
> This query selects 223672 rows, are you surprised it's slow ?

I'm not a SQL guru by any stretch but would a
constrained sub-select be appropriate here?

e.g. a simple test setup where each record in
table test1 has a FK referenced to an entry in
test:

joels=# \d test
Table "public.test"
Column | Type | Modifiers
--------+--------------+-----------
id | integer | not null
foo | character(3) |
Indexes:
"test_pkey" primary key, btree (id)

joels=# \d test1
Table "public.test1"
Column | Type | Modifiers
---------+---------+-----------
id | integer | not null
test_id | integer |
Indexes:
"test1_pkey" primary key, btree (id)
"test1_test_id_idx" btree (test_id)
Foreign-key constraints:
"$1" FOREIGN KEY (test_id) REFERENCES test(id) ON DELETE CASCADE

joels=# select count(*) from test;
count
-------
10001
(1 row)

joels=# select count(*) from test1;
count
-------
10001
(1 row)

joels=# explain select test_id from test1 t1, test t where t1.test_id = t.id;
QUERY PLAN
------------------------------------------------------------------------
Hash Join (cost=170.01..495.05 rows=10002 width=4)
Hash Cond: ("outer".test_id = "inner".id)
-> Seq Scan on test1 t1 (cost=0.00..150.01 rows=10001 width=4)
-> Hash (cost=145.01..145.01 rows=10001 width=4)
-> Seq Scan on test t (cost=0.00..145.01 rows=10001 width=4)
(5 rows)

joels=# explain select test_id from test1 t1
where test_id in (select id from test where id =
t1.test_id);
QUERY PLAN
------------------------------------------------------------------------------
Seq Scan on test1 t1 (cost=0.00..15269.02 rows=5001 width=4)
Filter: (subplan)
SubPlan
-> Index Scan using test_pkey on test (cost=0.00..3.01 rows=2 width=4)
Index Cond: (id = $0)
(5 rows)

So with the subselect the query planner would use
the primary key index on test when finding
referencing records in the test1 table.

Pierre, I seen the advice to use an additional
where condition in certain cases to induce an
index scan; how is this done?

my 1.2 pennies,
-Joel