pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedRe: How to speed up a time dimension query
2+ messages / 2 participants
[nested] [flat]
* Re: How to speed up a time dimension query
@ 2004-05-16 11:42 Gaetano Mendola <mendola@bigfoot.com>
2004-05-18 16:56 ` Re: How to speed up a time dimension query Hans de Bruin <hans@nl.invalid>
0 siblings, 1 reply; 2+ messages in thread
From: Gaetano Mendola @ 2004-05-16 11:42 UTC (permalink / raw)
To: Hans de Bruin <hans@nl.invalid>
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
^ permalink raw reply [nested|flat] 2+ messages in thread
* Re: How to speed up a time dimension query
2004-05-16 11:42 Re: How to speed up a time dimension query Gaetano Mendola <mendola@bigfoot.com>
@ 2004-05-18 16:56 ` Hans de Bruin <hans@nl.invalid>
0 siblings, 0 replies; 2+ messages in thread
From: Hans de Bruin @ 2004-05-18 16:56 UTC (permalink / raw)
To: pgsql-sql
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
^ permalink raw reply [nested|flat] 2+ messages in thread
end of thread, other threads:[~2004-05-18 16:56 UTC | newest]
Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2004-05-16 11:42 Re: How to speed up a time dimension query Gaetano Mendola <mendola@bigfoot.com>
2004-05-18 16:56 ` Hans de Bruin <hans@nl.invalid>
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