Postgres Pro
Downloads
Home   >   mailing lists

Thread: How come index isn't being used when query by function return value?

How come index isn't being used when query by function return value?

From
Joseph Shraibman
Date:
db:db=>explain select * from elog where id = eds('2006-01-01');
                                         QUERY PLAN
-------------------------------------------------------------------------------------------
  Seq Scan on elog  (cost=0.00..1894975.10 rows=1 width=204)
    Filter: (id = eds('2006-01-01 00:00:00'::timestamp without time zone))
(2 rows)

db:db=>explain select * from elog, (select eds('2006-01-01') as trg) aa
where id = trg;
                                       QUERY PLAN
--------------------------------------------------------------------------------------
  Nested Loop  (cost=0.00..4.36 rows=1 width=208)
    ->  Subquery Scan aa  (cost=0.00..0.02 rows=1 width=4)
          ->  Result  (cost=0.00..0.01 rows=1 width=0)
    ->  Index Scan using elog_pkey on elog  (cost=0.00..4.33 rows=1
width=204)
          Index Cond: (elog.id = "outer".trg)
(5 rows)

Time: 0.978 ms
db:db=>select version();
                                                  version
----------------------------------------------------------------------------------------------------------
  PostgreSQL 8.0.8 on x86_64-unknown-linux-gnu, compiled by GCC gcc
(GCC) 3.4.4 20050721 (Red Hat 3.4.4-2)
(1 row)

Re: How come index isn't being used when query by function return value?

From
"Merlin Moncure"
Date:
On 7/13/06, Joseph Shraibman <jks@selectacast.net> wrote:
> db:db=>explain select * from elog where id = eds('2006-01-01');
>                                          QUERY PLAN
> -------------------------------------------------------------------------------------------
>   Seq Scan on elog  (cost=0.00..1894975.10 rows=1 width=204)
>     Filter: (id = eds('2006-01-01 00:00:00'::timestamp without time zone))
> (2 rows)

is eds immutable function?

Merlin

Re: How come index isn't being used when query by function return

From
Joseph Shraibman
Date:
It is STABLE, which I finally figured out.  I had to find section 31.6
of the docs, which is nowhere near the part about writing functions.

Merlin Moncure wrote:
> On 7/13/06, Joseph Shraibman <jks@selectacast.net> wrote:
>> db:db=>explain select * from elog where id = eds('2006-01-01');
>>                                          QUERY PLAN
>> -------------------------------------------------------------------------------------------
>>
>>   Seq Scan on elog  (cost=0.00..1894975.10 rows=1 width=204)
>>     Filter: (id = eds('2006-01-01 00:00:00'::timestamp without time
>> zone))
>> (2 rows)
>
> is eds immutable function?
>
> Merlin
>
> ---------------------------(end of broadcast)---------------------------
> TIP 6: explain analyze is your friend
>