Thread: index usage
I have a table with 37000 rows, an integer column, and an index on that column. I've got a function that returns an integer. When I do a select where I restrict that column to being equal to a static number, explain tells me the index will be used. When I do the same thing but use the function instead of a static number, explain shows me a full scan on the table. I must be missing something, because my understanding is that the function will be evaluated once for the statement and then collapsed into a static number for the filtering. But the results of the explain seem to imply that's not the case....?
> De : pgsql-performance-owner@postgresql.org [mailto:pgsql-performance- > owner@postgresql.org] De la part de Ben > Envoyé : vendredi, juillet 28, 2006 15:21 > À : pgsql-performance@postgresql.org > Objet : [PERFORM] index usage > > I have a table with 37000 rows, an integer column, and an index on that > column. I've got a function that returns an integer. When I do a select > where I restrict that column to being equal to a static number, explain > tells me the index will be used. When I do the same thing but use the > function instead of a static number, explain shows me a full scan on the > table. > > I must be missing something, because my understanding is that the function > will be evaluated once for the statement and then collapsed into a static > number for the filtering. But the results of the explain seem to imply > that's not the case....? > Is your function IMMUTABLE, STABLE or VOLATILE? -- Daniel
It's volatile, but it will always return an integer. On Fri, 28 Jul 2006, Daniel Caune wrote: >> De : pgsql-performance-owner@postgresql.org [mailto:pgsql-performance- >> owner@postgresql.org] De la part de Ben >> Envoyé : vendredi, juillet 28, 2006 15:21 >> À : pgsql-performance@postgresql.org >> Objet : [PERFORM] index usage >> >> I have a table with 37000 rows, an integer column, and an index on that >> column. I've got a function that returns an integer. When I do a select >> where I restrict that column to being equal to a static number, explain >> tells me the index will be used. When I do the same thing but use the >> function instead of a static number, explain shows me a full scan on the >> table. >> >> I must be missing something, because my understanding is that the function >> will be evaluated once for the statement and then collapsed into a static >> number for the filtering. But the results of the explain seem to imply >> that's not the case....? >> > > Is your function IMMUTABLE, STABLE or VOLATILE? > > -- > Daniel >
A volatile function has may return a different result for each row; think of the random() or nextval() functions for example. You wouldn't want them to return the same value for each row returned. -- Mark Lewis On Fri, 2006-07-28 at 13:59 -0700, Ben wrote: > It's volatile, but it will always return an integer. > > On Fri, 28 Jul 2006, Daniel Caune wrote: > > >> De : pgsql-performance-owner@postgresql.org [mailto:pgsql-performance- > >> owner@postgresql.org] De la part de Ben > >> Envoyé : vendredi, juillet 28, 2006 15:21 > >> À : pgsql-performance@postgresql.org > >> Objet : [PERFORM] index usage > >> > >> I have a table with 37000 rows, an integer column, and an index on that > >> column. I've got a function that returns an integer. When I do a select > >> where I restrict that column to being equal to a static number, explain > >> tells me the index will be used. When I do the same thing but use the > >> function instead of a static number, explain shows me a full scan on the > >> table. > >> > >> I must be missing something, because my understanding is that the function > >> will be evaluated once for the statement and then collapsed into a static > >> number for the filtering. But the results of the explain seem to imply > >> that's not the case....? > >> > > > > Is your function IMMUTABLE, STABLE or VOLATILE? > > > > -- > > Daniel > > > ---------------------------(end of broadcast)--------------------------- > TIP 5: don't forget to increase your free space map settings
Ben <bench@silentmedia.com> writes:
> It's volatile, but it will always return an integer.
If it's volatile then it can't be used for an index condition.
regards, tom lane