Thread: Slow query to get last created row using CURRVAL

Hi all,

I have a table that stores all the page loads in my web application:

shs-dev=# \d log_event
                                      Table "public.log_event"
     Column      |           Type           |
Modifiers
-----------------+--------------------------+--------------------------------------------------------
 id              | bigint                   | not null default
nextval('log_event_id_seq'::regclass)
 user_id         | integer                  |
 ip              | inet                     | not null
 action_id       | integer                  | not null
 object1_id      | integer                  |
 object2_id      | integer                  |
 event_timestamp | timestamp with time zone | not null
 data            | text                     |
 comments        | text                     |
Indexes:
    "log_event_pkey" PRIMARY KEY, btree (id)
    "log_event_action_id_idx" btree (action_id)
    "log_event_object1_idx" btree (object1_id)
    "log_event_object2_idx" btree (object2_id)
    "log_event_timestamp_idx" btree (event_timestamp)
    "log_event_user_id_idx" btree (user_id)
Foreign-key constraints:
    "log_event_action_id_fkey" FOREIGN KEY (action_id) REFERENCES
config.log_action(id)
Referenced by:
    TABLE "log_data" CONSTRAINT "log_data_event_id_fkey" FOREIGN KEY
(event_id) REFERENCES log_event(id) ON DELETE CASCADE DEFERRABLE
INITIALLY DEFERRED
    TABLE "log_report" CONSTRAINT "log_report_event_id_fkey" FOREIGN
KEY (event_id) REFERENCES log_event(id)

shs-dev=# select count(*) from log_event;
  count
---------
 5755566


For each page load I first create an entry in that table, e.g.:

INSERT INTO log_event (user_id, ip, action_id, object1_id, object2_id,
event_timestamp, comments) VALUES (1, '127.0.0.1', 96, null, null,
NOW(), 'TEST');

After that, I want to retrieve the data stored in log_event from a
trigger, e.g.:

SELECT user_id FROM log_event WHERE id = CURRVAL('log_event_id_seq');

This way my insert-trigger knows who is creating the new row, while
using only one pg-user to query the database.

The problem is that this query is very slow because it refuses to use
an index scan:


shs-dev=# set enable_seqscan = off;
SET
shs-dev=# explain analyze SELECT user_id FROM log_event WHERE id =
CURRVAL('log_event_id_seq');
                                                         QUERY PLAN

-----------------------------------------------------------------------------------------------------------------------------
 Seq Scan on log_event  (cost=10000000000.00..10000139202.07 rows=1
width=4) (actual time=2086.272..2086.273 rows=1 loops=1)
   Filter: (id = currval('log_event_id_seq'::regclass))
 Total runtime: 2086.305 ms


If I specify one specific value, it's OK:

shs-dev=# explain analyze SELECT user_id FROM log_event WHERE id =
1283470192837401;
                                                        QUERY PLAN

--------------------------------------------------------------------------------------------------------------------------
 Index Scan using log_event_pkey on log_event  (cost=0.00..8.90 rows=1
width=4) (actual time=0.034..0.034 rows=0 loops=1)
   Index Cond: (id = 1283470192837401::bigint)
 Total runtime: 0.056 ms

If I experiment with RANDOM, it's slow again:

shs-dev=# explain analyze SELECT user_id FROM log_event WHERE id =
RANDOM()::bigint;
                                                         QUERY PLAN

-----------------------------------------------------------------------------------------------------------------------------
 Seq Scan on log_event  (cost=10000000000.00..10000153591.24 rows=1
width=4) (actual time=1353.425..1353.425 rows=0 loops=1)
   Filter: (id = (random())::bigint)
 Total runtime: 1353.452 ms

On the other hand, for some undeterministic cases, it does run fast:
(in this example the planner cannot predict what will be the value of
the filter condition)

shs-dev=# explain analyze SELECT user_id FROM log_event WHERE id =
(select id from artist where id > 1000 limit 1);
                                                               QUERY
PLAN

-----------------------------------------------------------------------------------------------------------------------------------------
 Index Scan using log_event_pkey on log_event  (cost=0.08..8.98 rows=1
width=4) (actual time=0.069..0.069 rows=0 loops=1)
   Index Cond: (id = $0)
   InitPlan 1 (returns $0)
     ->  Limit  (cost=0.00..0.08 rows=1 width=4) (actual
time=0.039..0.039 rows=1 loops=1)
           ->  Index Scan using artist_pkey on artist
(cost=0.00..3117.11 rows=40252 width=4) (actual time=0.038..0.038
rows=1 loops=1)
                 Index Cond: (id > 1000)


I have no idea why in some cases the index scan is not considered.
Does anyone have an idea?

Thanks!

Kind regards,
Mathieu

On Sat, Dec 4, 2010 at 13:56, Mathieu De Zutter <mathieu@dezutter.org> wrote:
> I have no idea why in some cases the index scan is not considered.
> Does anyone have an idea?

I guess that it's because the currval() function is volatile -- its
value has to be tested for again each row.

Try this instead:
SELECT user_id FROM log_event WHERE id = (SELECT CURRVAL('log_event_id_seq'));

This will assure that there's only one call to currval().

Regards,
Marti

On Sat, Dec 4, 2010 at 1:35 PM, Marti Raudsepp <marti@juffo.org> wrote:
> On Sat, Dec 4, 2010 at 13:56, Mathieu De Zutter <mathieu@dezutter.org> wrote:
>> I have no idea why in some cases the index scan is not considered.
>> Does anyone have an idea?
>
> I guess that it's because the currval() function is volatile -- its
> value has to be tested for again each row.
>
> Try this instead:
> SELECT user_id FROM log_event WHERE id = (SELECT CURRVAL('log_event_id_seq'));
>
> This will assure that there's only one call to currval().

OK, that makes a lot of sense. Your suggestion solves my problem.

Thanks!

Mathieu

INSERT INTO log_event (user_id, ip, action_id, object1_id, object2_id,
event_timestamp, comments) VALUES (1, '127.0.0.1', 96, null, null,
NOW(), 'TEST') returning user_id;
--
Best regards,
 Vitalii Tymchyshyn