[PERFORM] 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=100.00..1139202.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=100.00..1153591.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 -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] Slow query to get last created row using CURRVAL
On Sat, Dec 4, 2010 at 13:56, Mathieu De Zutter math...@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 -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] Slow query to get last created row using CURRVAL
On Sat, Dec 4, 2010 at 1:35 PM, Marti Raudsepp ma...@juffo.org wrote: On Sat, Dec 4, 2010 at 13:56, Mathieu De Zutter math...@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 -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] Slow query to get last created row using CURRVAL
2010/12/4 Mathieu De Zutter math...@dezutter.org 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. Please note that you can use next query to perform both insert and select: 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