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
--
Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
