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