Re: How to speed up a time dimension query

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 on dim_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

----------------------------------------------------------------------------------------------------------------------------------------------------------
Index Scan 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