From: Gaetano Mendola <mendola@bigfoot.com>
Subject: Re: How to speed up a time dimension query
Date: Sun, 16 May 2004 13:42:10 +0200
Organization: PYRENET Midi-pyrenees Provider
Lines: 72
Message-ID: <40A75392.90309@bigfoot.com>
References: <40a3ea79$0$568$e4fe514c@news.xs4all.nl>
Mime-Version: 1.0
Content-Type: text/plain; charset=us-ascii; format=flowed
Content-Transfer-Encoding: 7bit
To: Hans de Bruin <hans@nl.invalid>
User-Agent: Mozilla Thunderbird 0.6 (Windows/20040502)
In-Reply-To: <40a3ea79$0$568$e4fe514c@news.xs4all.nl>
To: pgsql-sql@postgresql.org

Hans de Bruin wrote:

> Hi there,
> 
> I like to speed up my homepage and need to do something about a query. A 
> half to one second to get 24 records from a time dimension table a bit 
> long. This is the table:
> 
> news2=> \d dim_time
> And here is the query which in my opinion take way to long:
> 
> news2=> explain analyze select id,day,hour from dim_time
> news2-> where id between (now()-interval '25 hours')
> news2-> and (now()- interval '1 hour')
> news2-> order by id ;

This is not what I get with a table similiars to your:

test=# select count(*) from user_logs;
   count
---------
  3025880
(1 row)


empdb=# explain analyze
empdb-# select *
empdb-# from user_logs
empdb-# where login_time between (now()-interval '25 hours') and
empdb-#                          (now()-interval '1 hour')
empdb-# order by login_time;
                                                                  QUERY PLAN 

---------------------------------------------------------------------------------------------------------------------------------------------
  Index Scan using idx_user_logs_login_time on user_logs  (cost=0.00..502.67 rows=15130 width=47) 
(actual time=61.433..61.433 rows=0 loops=1)
    Index Cond: ((login_time >= (now() - '1 day 01:00:00'::interval)) AND (login_time <= (now() - 
'01:00:00'::interval)))
  Total runtime: 61.557 ms
(3 rows)

what do you obtain if you disable the sequential scan ?

do it in this way:
set enable_seqscan = off;

if you obtain a cost higher then what you get on your explain:
cost=590.26..590.44
and of course a lower Total runtime, then you have instruct your engine that
is better perform and index scan, you can accoplish this decreasing the
following values ( that values are what I use ).

cpu_index_tuple_cost = 0.0005
cpu_operator_cost    = 0.0025
cpu_tuple_cost       = 0.005


decreasing these value you decrease the cost for the index scans if the Total time
is higher you have to deal with the parameter effective_cache_size in order to
use more ram.


Regards
Gaetano Mendola