Re: database performance and query performance question

"Shea,Dan [CIS]" <Dan(dot)Shea(at)ec(dot)gc(dot)ca> 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