From: Volkan YAZICI <yazicivo@ttmail.com>
To: pgsql-performance@postgresql.org
Subject: Optimizing AGE()
Date: Fri, 06 Jun 2008 16:20:59 +0300
Message-ID: <87abhyaedg.fsf@alamut.mobiliz.com.tr> (raw)
Hi,
Am I wrong or AGE() always gets directed to a sequential scan?
# BEGIN;
] SET enable_seqscan TO off;
] EXPLAIN ANALYZE
] SELECT count(1)
] FROM incomingmessageslog
] WHERE AGE(time) < '1 year';
] ROLLBACK;
BEGIN
SET
QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------------------
Aggregate (cost=100000528.33..100000528.34 rows=1 width=0) (actual time=13.789..13.790 rows=1 loops=1)
-> Seq Scan on incomingmessageslog (cost=100000000.00..100000520.00 rows=3333 width=0) (actual time=13.783..13.783 rows=0 loops=1)
Filter: (age((('now'::text)::date)::timestamp without time zone, "time") < '1 year'::interval)
Total runtime: 13.852 ms
(4 rows)
ROLLBACK
As far as I know, AGE() can take advantage of a very simple equation for
constant comparisons:
= AGE(field) < constant_criteria
= AGE(field, constant_ts) < constant_criteria
= AGE(field) < constant_criteria + constant_ts
= AGE(field) < CONSTANT_CRITERIA
How much does such a hack into optimizer cost? I don't know about its
implications but I'll really appreciate such a functionality. At the
moment, I'm trying replace every AGE() usage in my code and it really
feels a PITA.
Regards.
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: yazicivo@ttmail.com
Subject: Re: Optimizing AGE()
In-Reply-To: <87abhyaedg.fsf@alamut.mobiliz.com.tr>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox