Thread: Query plan when using currval
Hi,
Steve
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');
QUERY PLAN
--------------------------------------------------------------------------------------------------------
Seq Scan on log_statement (cost=0.00..14.05 rows=1 width=8) (actual time=0.027..0.027 rows=0 loops=1)
Filter: (id = currval('log_statement_id_seq'::regclass))
Rows Removed by Filter: 9
Planning time: 0.081 ms
Execution time: 0.048 ms
(5 rows)
bcaas=> explain analyze select id from log_statement where id = (select currval('log_statement_id_seq'));
QUERY PLAN
---------------------------------------------------------------------------------------------------------------------------------------
Index Only Scan using log_statement_pkey on log_statement (cost=0.16..8.18 rows=1 width=8) (actual time=0.017..0.017 rows=0 loops=1)
Index Cond: (id = $0)
Heap Fetches: 0
InitPlan 1 (returns $0)
-> Result (cost=0.00..0.01 rows=1 width=8) (actual time=0.003..0.004 rows=1 loops=1)
Planning time: 0.105 ms
Execution time: 0.045 ms
(7 rows)
(This is with pg 10.3)
Thanks,
Steve
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
On 9 May 2018 at 18:24, Steve Baldwin <steve.baldwin@gmail.com> wrote:
> bcaas=> explain analyze select id from log_statement where id = (select currval('log_statement_id_seq'));
I know Tom has answered your question, but you may be interested in
INSERT RETURNING as this will save you from having to do this to
lookup the ID of the last inserted sequence value.
https://www.postgresql.org/docs/10/static/sql-insert.html
--
David Rowley http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services
Thanks Tom and David. My current logic flow was to do an insert and then a bit later, and update the same row. The update was using 'where id = currval(...)' and was getting slower and slower as the table grew (surprise surprise). I realise I can solve my performance issue in a couple of ways, but I think I'm going to refactor it and try to avoid the update.
I really appreciate the insight.
Steve
On Thu, May 10, 2018 at 8:22 AM, David Rowley <david.rowley@2ndquadrant.com> wrote:
On 9 May 2018 at 18:24, Steve Baldwin <steve.baldwin@gmail.com> wrote:
> bcaas=> explain analyze select id from log_statement where id = (select currval('log_statement_id_seq'));
I know Tom has answered your question, but you may be interested in
INSERT RETURNING as this will save you from having to do this to
lookup the ID of the last inserted sequence value.
https://www.postgresql.org/docs/10/static/sql-insert.html
--
David Rowley http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services