pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Scott Marlowe <scott.marlowe@gmail.com>
To: Volkan YAZICI <yazicivo@ttmail.com>
Cc: pgsql-performance@postgresql.org
Subject: Re: Optimizing AGE()
Date: Sun, 8 Jun 2008 00:09:19 -0600
Message-ID: <dcc563d10806072309i49aaa3d9g38a36834507e81f5@mail.gmail.com> (raw)
In-Reply-To: <87abhyaedg.fsf@alamut.mobiliz.com.tr>
References: <87abhyaedg.fsf@alamut.mobiliz.com.tr>

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.



view thread (2+ messages)

Message-ID: <dcc563d10806072309i49aaa3d9g38a36834507e81f5@mail.gmail.com>
Permalink:  ../dcc563d10806072309i49aaa3d9g38a36834507e81f5@mail.gmail.com/
Also on:    postgresql.org/message-id/dcc563d10806072309i49aaa3d9g38a36834507e81f5@mail.gmail.com

 · 

reply

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 from your mail client; the link fills in
  the headers below: reply via email
  Some clients, webmail especially, drop In-Reply-To and References from
  a link, and the reply then starts a new thread: use the mbox below.

  To: pgsql-performance@postgresql.org
  Cc: scott.marlowe@gmail.com, yazicivo@ttmail.com
  Subject: Re: Optimizing AGE()
  In-Reply-To: <dcc563d10806072309i49aaa3d9g38a36834507e81f5@mail.gmail.com>
  References: <87abhyaedg.fsf@alamut.mobiliz.com.tr> <dcc563d10806072309i49aaa3d9g38a36834507e81f5@mail.gmail.com>

* 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