From: Hans de Bruin <hans@nl.invalid>
To: pgsql-sql@postgresql.org
Subject: Re: How to speed up a time dimension query
Date: Tue, 18 May 2004 18:56:45 +0200
Message-ID: <40aa404d$0$559$e4fe514c@news.xs4all.nl> (raw)
In-Reply-To: <40A75392.90309@bigfoot.com>
References: <40a3ea79$0$568$e4fe514c@news.xs4all.nl>
<40A75392.90309@bigfoot.com>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: hans@nl.invalid
Subject: Re: How to speed up a time dimension query
In-Reply-To: <40aa404d$0$559$e4fe514c@news.xs4all.nl>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox