Re: database performance and query performance question

From: "Shea,Dan [CIS]" <Dan(dot)Shea(at)ec(dot)gc(dot)ca>
To: 'Hannu Krosing' <hannu(at)tm(dot)ee>, "Shea,Dan [CIS]" <Dan(dot)Shea(at)ec(dot)gc(dot)ca>
Cc: "'josh(at)agliodbs(dot)com'" <josh(at)agliodbs(dot)com>, pgsql-performance(at)postgresql(dot)org
Subject: Re: database performance and query performance question
Date: 2004-01-22 21:09:32
Message-ID: F2D63B916C88C14D9B59F93C2A5DD33F0B911A@cisxa.cis.ec.gc.ca
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-performance

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

Browse pgsql-performance by date

  From Date Subject
Next Message Shea,Dan [CIS] 2004-01-22 21:32:28 Re: database performance and query performance question
Previous Message Hannu Krosing 2004-01-22 20:53:37 Re: database performance and query performance question