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>
User-Agent: Gnus/5.13 (Gnus v5.13) Emacs/23.0.60 (gnu/linux)
MIME-Version: 1.0
Content-Type: text/plain; charset=us-ascii

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.