'now' vs now() performance
От | Jeffrey Melloy |
---|---|
Тема | 'now' vs now() performance |
Дата | |
Msg-id | DA3CAD0E-D1E7-11D7-A756-000393C78AC0@visualdistortion.org обсуждение исходный текст |
Ответы |
Re: 'now' vs now() performance
|
Список | pgsql-general |
I was recently running into performance problems with a query containing now()::date or CURRENT_DATE. When I went to debug, 'now'::date made efficient use of the index (on a timestamp field). The docs say that 'now' is turned into a constant right away. Is this overhead/poor planning simply because 'now' gets converted to a constant so much earlier in the process? I've pasted the query plans below. Jeff jmelloy=# explain analyze select distinct sender_id from messages where message_date > now()::date; QUERY PLAN ------------------------------------------------------------------------ -------------------------------------------------- Unique (cost=4517.17..4639.74 rows=2451 width=4) (actual time=1697.62..1697.90 rows=4 loops=1) -> Sort (cost=4517.17..4578.45 rows=24515 width=4) (actual time=1697.61..1697.74 rows=62 loops=1) Sort Key: sender_id -> Seq Scan on messages (cost=0.00..2729.88 rows=24515 width=4) (actual time=1695.42..1697.22 rows=62 loops=1) Filter: (message_date > ((now())::date)::timestamp without time zone) Total runtime: 1698.11 msec (6 rows) jmelloy=# explain analyze select distinct sender_id from messages where message_date > 'now'::date; QUERY PLAN ------------------------------------------------------------------------ ------------------------------------------------------------------------ -------- Unique (cost=201.86..202.14 rows=6 width=4) (actual time=1.24..1.52 rows=4 loops=1) -> Sort (cost=201.86..202.00 rows=56 width=4) (actual time=1.23..1.36 rows=62 loops=1) Sort Key: sender_id -> Index Scan using adium_msg_date_sender_recipient on messages (cost=0.00..200.22 rows=56 width=4) (actual time=0.23..0.84 rows=62 loops=1) Index Cond: (message_date > '2003-08-18 00:00:00'::timestamp without time zone) Total runtime: 1.74 msec (6 rows)
В списке pgsql-general по дате отправления: