"Shea,Dan [CIS]" <[EMAIL PROTECTED]> writes:
> Indexes:
> "forecastelement_vrwi_idx" btree (valid_time,region_id.wx_element.issue_time)
>
> explain analyze
> SELECT DISTINCT ON (valid_time)
> to_char(valid_time,'YYYYMMDDHH24MISS') AS valid_time,
> value
> from (
> SELECT valid_time,value, "time"(valid_time) AS hour, reception_time,
> issue_time
> FROM forecastelement
> WHERE valid_time BETWEEN '2002-09-02 04:00:00' AND '2002-09-07 03:59:59'
> AND region_id = 'PU-REG-WTO-00200'
> AND wx_element = 'TEMP_VALEUR1'
> AND issue_time BETWEEN '2002-09-02 05:00:00' AND '2002-09-06 05:00:00'
> AND origin = 'REGIONAL'
> AND "time"(issue_time) = '05:00:00'
> ORDER BY issue_time,reception_time DESC,valid_time
> ) AS foo
> WHERE
> ( date(valid_time) = date(issue_time)+1 -1
> OR date(valid_time) = date(issue_time)+1
> OR ( valid_time BETWEEN '2002-09-07 00:00:00' AND '2002-09-07 03:59:59'
> AND issue_time = '2002-09-06 05:00:00'
> )
> )
> ORDER BY valid_time ,issue_time DESC;
Incidentally, I find it easier to analyze queries when they've been formatted
well. This makes what's going on much clearer.
>From this it's clear your index doesn't match the query. Adding more columns
will be useless because only the leading column "valid_time" will be used at
all. Since you're fetching a whole range of valid_times the remaining columns
are all irrelevant. They only serve to bloat the index and require reading a
lot more data.
You could either try creating an index just on valid_time, or create an index
on (region_id,wx_element,valid_time) or (region_id,wx_element,issue_time)
whichever is more selective. You could put wx_element first if it's more
selective than region_id.
Moreover, what purpose does the inner ORDER BY clause serve? It's only going
to be re-sorted again by the outer ORDER BY.
--
greg
---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faqs/FAQ.html
