Home > mailing lists

Query plan when using currval - Mailing list pgsql-novice

From	Steve Baldwin
Subject	Query plan when using currval
Date	May 9, 2018 12:24:10
Msg-id	CAKE1Aib_H1Si0wNv+27-RKNGoKv+afmciZhxPaKsoYrMfSAJfw@mail.gmail.com Whole thread Raw
Responses	Re: Query plan when using currval (Tom Lane <tgl@sss.pgh.pa.us>) Re: Query plan when using currval (David Rowley <david.rowley@2ndquadrant.com>)
List	pgsql-novice

Hi,

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

From: Antonio Silva
Date: 09 May 2018, 06:55:55
Subject: Re: issues when installing postgres

From: Tom Lane
Date: 09 May 2018, 20:17:52
Subject: Re: Query plan when using currval