"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,'MMDDHH24MISS') AS valid_time,
> value
>from (
>
Shea,Dan [CIS] kirjutas N, 22.01.2004 kell 23:32:
> 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=# ex
Dan,
Of course it took forever. You're retrieving 2.9 million rows!
> 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)
--
-Josh Berkus
Aglio Database Solutions
San Francisco
27;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: '[EMAIL PROTECTED]'; [EMAIL PROTECTED]
Subject: RE:
Shea,Dan [CIS]
Cc: '[EMAIL PROTECTED]'; [EMAIL PROTECTED]
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 i
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
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 eno
Dan,
> 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?
Because of the expected number of rows to be returned. Take a look at the row
estimate
-Original Message-
From: Josh Berkus [mailto:[EMAIL PROTECTED]
Sent: Thursday, January 22, 2004 3:01 PM
To: Shea,Dan [CIS]; [EMAIL PROTECTED]
Subject: Re: [PERFORM] database performance and query performance
question
Dan,
> Should there be less columns in the index?
> How
actual time=176.133..276.494
rows=10 loops=1)
Index Cond: (valid_time = '2004-01-23 00:00:00'::timestamp without
time zone)
Total runtime: 276.721 ms
(4 rows)
-Original Message-
From: Josh Berkus [mailto:[EMAIL PROTECTED]
Sent: Thursday, January 22, 2004 3:01 PM
To: Shea,D
Dan,
> Why is your effective cache size only 300mb when you have 3 GB of RAM? It's
> not affecting this query, but it could affect others.
Ignore this last question, I dropped a zero from my math. Sorry!
--
-Josh Berkus
Aglio Database Solutions
San Francisco
---(
Dan,
> Should there be less columns in the index?
> How can we improve database performance?
> How should I improve my query?
Your query plan isn't the problem. It's a good plan, and a reasonably
efficient query. Under other circumstances, the SELECT DISTINCT with the
to_char could be a perf
12 matches
Mail list logo
