pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedOptimizing AGE()
2+ messages / 2 participants
[nested] [flat]
* Optimizing AGE()
@ 2008-06-06 13:20 Volkan YAZICI <yazicivo@ttmail.com>
2008-06-08 06:09 ` Re: Optimizing AGE() Scott Marlowe <scott.marlowe@gmail.com>
0 siblings, 1 reply; 2+ messages in thread
From: Volkan YAZICI @ 2008-06-06 13:20 UTC (permalink / raw)
To: pgsql-performance
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.
^ permalink raw reply [nested|flat] 2+ messages in thread
* Re: Optimizing AGE()
2008-06-06 13:20 Optimizing AGE() Volkan YAZICI <yazicivo@ttmail.com>
@ 2008-06-08 06:09 ` Scott Marlowe <scott.marlowe@gmail.com>
0 siblings, 0 replies; 2+ messages in thread
From: Scott Marlowe @ 2008-06-08 06:09 UTC (permalink / raw)
To: Volkan YAZICI <yazicivo@ttmail.com>; +Cc: pgsql-performance
On Fri, Jun 6, 2008 at 7:20 AM, Volkan YAZICI <yazicivo@ttmail.com> wrote:
> 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.
Yeah, age() isn't real performent in such situations. I generally
stick to simpler date math like:
where timestampvalue < now() - interval '2 year'
which can use an index on timestampvalue
The problem with age is it's always compared to now, so there's always
gonna be some math.
^ permalink raw reply [nested|flat] 2+ messages in thread
end of thread, other threads:[~2008-06-08 06:09 UTC | newest]
Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2008-06-06 13:20 Optimizing AGE() Volkan YAZICI <yazicivo@ttmail.com>
2008-06-08 06:09 ` Scott Marlowe <scott.marlowe@gmail.com>
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