Skip site navigation (1) Skip section navigation (2)

Re: database performance and query performance question

From: "Shea,Dan [CIS]" <Dan(dot)Shea(at)ec(dot)gc(dot)ca>
To: "Shea,Dan [CIS]" <Dan(dot)Shea(at)ec(dot)gc(dot)ca>,'Hannu Krosing' <hannu(at)tm(dot)ee>
Cc: "'josh(at)agliodbs(dot)com'" <josh(at)agliodbs(dot)com>,"'pgsql-performance(at)postgresql(dot)org'" <pgsql-performance(at)postgresql(dot)org>
Subject: Re: database performance and query performance question
Date: 2004-01-22 21:32:28
Message-ID: F2D63B916C88C14D9B59F93C2A5DD33F0B911B@cisxa.cis.ec.gc.ca (view raw or flat)
Thread:
Lists: pgsql-performance
The end date in the previous example was actually invalid  between
'2004-01-12'::date and '2003-01-12'::date;
There have been multiple inserts since I recreated the index but it took
quite some time to complete the following
PWFPM_DEV=# explain analyze select * from forecastelement where valid_time
between '2004-01-12'::date and '2004-01-13'::date;
 
QUERY PLAN                                                  
----------------------------------------------------------------------------
----------------------------------------------------------------------------
-------
 Index Scan using forecastelement_v_idx on forecastelement
(cost=0.00..832139.81 rows=2523119 width=129) (actual time=0.519..467159.658
rows=2940600 loops=1)
   Index Cond: ((valid_time >= '2004-01-12 00:00:00'::timestamp without time
zone) AND (valid_time <= '2004-01-13 00:00:00'::timestamp without time
zone))
 Total runtime: 472627.148 ms
(3 rows)

-----Original Message-----
From: Shea,Dan [CIS] 
Sent: Thursday, January 22, 2004 4:10 PM
To: 'Hannu Krosing'; Shea,Dan [CIS]
Cc: 'josh(at)agliodbs(dot)com'; pgsql-performance(at)postgresql(dot)org
Subject: RE: [PERFORM] database performance and query performance
question


This sure speed up the query, it is fast.
PWFPM_DEV=# explain analyze select * from forecastelement where valid_time
between '2004-01-12'::date and '2003-01-12'::date;
 
QUERY PLAN                                                    
----------------------------------------------------------------------------
----------------------------------------------------------------------------
---
 Index Scan using forecastelement_v_idx on forecastelement
(cost=0.00..159607.11 rows=466094 width=129) (actual time=49.504..49.504
rows=0 loops=1)
   Index Cond: ((valid_time >= '2004-01-12 00:00:00'::timestamp without time
zone) AND (valid_time <= '2003-01-12 00:00:00'::timestamp without time
zone))
 Total runtime: 49.589 ms
(3 rows)

-----Original Message-----
From: Hannu Krosing [mailto:hannu(at)tm(dot)ee]
Sent: Thursday, January 22, 2004 3:54 PM
To: Shea,Dan [CIS]
Cc: 'josh(at)agliodbs(dot)com'; pgsql-performance(at)postgresql(dot)org
Subject: Re: [PERFORM] database performance and query performance
question


Hannu Krosing kirjutas N, 22.01.2004 kell 22:46:
> Shea,Dan [CIS] kirjutas N, 22.01.2004 kell 22:35:
> > Something that I do not understand is why if you use a valid_time =
> > '2004-01-22 00:00:00' the query will use the index but if you do a
> > valid_time >  '2004-01-22 00:00:00' it does not use the index?
> 
> It probably can't tell if > is selective enough to justify using index.
> 
> Together with "limit 10" it may be.
> 
> You could try 
> 
> explain analyze select * from forecastelement where valid_time between 
> '2004-01-22'::date and '2004-01-22'::date limit 10;

Sorry, that should have been:

between '2004-01-22'::date and '2004-01-23'::date


> to see if this is considered good enough.
> 
> --------------
> Hannu
> 
> 
> ---------------------------(end of broadcast)---------------------------
> TIP 1: subscribe and unsubscribe commands go to majordomo(at)postgresql(dot)org

Responses

pgsql-performance by date

Next:From: Josh BerkusDate: 2004-01-22 21:52:38
Subject: Re: database performance and query performance question
Previous:From: Shea,Dan [CIS]Date: 2004-01-22 21:09:32
Subject: Re: database performance and query performance question

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group