Обсуждение: Re: How to speed up a time dimension query
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 --------------------------------------------------------------------------------------------------------------------------------------------- IndexScan 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
Gaetano Mendola wrote: > Hans de Bruin wrote: > ... > > > 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; > The runtime increased a litle, so the optimizer made the most efficient choice. Although the query now scans the index it stil seems to use table. In the first itempt the pkey index was used, which only contains the id field. Using the composite index dit not improve. Just selectig the table gives these results: news2=> explain analyze select id,year from dim_time; QUERY PLAN -------------------------------------------------------------------------------------------------------------- Seq Scan ondim_time (cost=0.00..300.00 rows=14400 width=48) (actual time=0.017..49.797 rows=14400 loops=1) Total runtime: 67.788 ms (2 rows) explain analyze select id,year from dim_time order by id,year; QUERY PLAN ---------------------------------------------------------------------------------------------------------------------------------------------------------- IndexScan using ix_dim_time_id_year_month_day_hour on dim_time (cost=0.00..866.68 rows=14400 width=12) (actual time=0.048..139.381 rows=14400 loops=1) Total runtime: 168.210 ms (2 rows) Maybe I am expecting to much from my mini-itx. -- Hans de Bruin http://eratosthenes.xs4all.nl