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
---------------------------(end of broadcast)--------------------------- TIP 7: don't forget to increase your free space map settings
