Re: Query plan when using currval

Steve Baldwin <steve.baldwin@gmail.com> writes:
> Can someone please help me understand why the first query doesn't use the
> primary key index whereas the second query does use it:

> bcaas=> explain analyze select id from log_statement where id =
> currval('log_statement_id_seq');

currval is marked as a volatile function (because its value can change
during a statement), so it's not a candidate for use in an index
qualifier.  In principle (i.e. in a naive interpretation of SQL
semantics), the WHERE clause should be evaluated in full for each row
produced by the FROM clause --- and if a volatile function is involved
then we actually have to do it that way, because we don't know enough
about how the function will act.  So you get a seqscan.  But a clause
comparing an indexed column to a value that we know won't change for
the duration of the scan is OK to optimize into an indexscan.

> bcaas=> explain analyze select id from log_statement where id = (select
> currval('log_statement_id_seq'));

Use of a sub-select hides the function's volatility, making it possible to
use the clause as an index qual.  This is not quite entirely unprincipled,
but it relies on the fact that this is an uncorrelated sub-select, so it
needn't be re-executed for each row of the outer query.  You could argue
about how well that agrees with a naive view of SQL semantics ;-) ... but
Postgres has acted this way for as long as it's had sub-selects.

            regards, tom lane