Обсуждение: How come index isn't being used when query by function return value?
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)
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
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 >