Re: index not used in joins

Sebastian Böck wrote:
> Richard Huxton wrote:
>> Can you post the output from your "explain analyse" calls too? The
>> statistics aren't going to be the same on different machines.
>>
>
> Sure, here it is.

Thanks. (PS - remember to cc the list too).

> EXPLAIN ANALYZE SELECT * FROM v;
> QUERY PLAN
> -------------------------------------------------------------------------------------------------------------------------------
>
> Nested Loop (cost=0.00..263.12 rows=116 width=20) (actual
> time=5.171..109.910 rows=1020 loops=1)
> Join Filter: (("inner"."version" = "outer"."version") OR
> ("inner".approved IS NOT NULL))
> -> Seq Scan on users u (cost=0.00..1.01 rows=1 width=12) (actual
> time=0.005..0.009 rows=1 loops=1)
> -> Index Scan using test_ on test t (cost=0.00..155.74 rows=7092
> width=20) (actual time=0.012..64.873 rows=21000 loops=1)
> Index Cond: (t.datum <= "outer".datum)
> Total runtime: 111.879 ms

> EXPLAIN ANALYZE SELECT t.* FROM test AS t INNER JOIN users AS u ON
> t.datum <= u.datum AND (t.version = '999' OR t.approved IS NOT NULL);
> QUERY PLAN
> -----------------------------------------------------------------------------------------------------------------------------
>
> Nested Loop (cost=0.00..7.78 rows=133 width=20) (actual
> time=0.035..7.733 rows=1020 loops=1)
> -> Seq Scan on users u (cost=0.00..1.01 rows=1 width=8) (actual
> time=0.006..0.010 rows=1 loops=1)
> -> Index Scan using test_999 on test t (cost=0.00..5.11 rows=132
> width=20) (actual time=0.017..3.358 rows=1020 loops=1)
> Index Cond: (t.datum <= "outer".datum)
> Filter: (("version" = 999) OR (approved IS NOT NULL))
> Total runtime: 9.528 ms

OK - so what you want to know is why index "test_999" is used in the
second but not the first, even though both return the same rows.

The fact is that the conditional index:
CREATE INDEX test_999 ON test (datum)
WHERE version = '999' OR approved IS NOT NULL;
AFAIK looks at the WHERE clause of your query to determine where it can
run. Don't forget that the planner needs to pick which index is best
*before* it starts fetching data.

So - in the first example there might be rows where e.g. t.version=998
which means test_999 would be a poor choice of index.

--
Richard Huxton
Archonet Ltd