Our database has slowed right down. We are not getting any performance from
our biggest table forecastelement.
The table has 93,218,671 records in it and climbing.
The index is on 4 columns, origianlly it was on 3. I added another to see
if it improve performance. It did not.
Should there be
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
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
: (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,Dan [CIS]; [EMAIL PROTECTED]
Subject: Re: [PERFORM] database performance and query
-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 can we improve
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
estimates
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
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
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 if you use a valid_time =
'2004-01-22 00:00:00' the query will use
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: [PERFORM] database performance and query performance
question
This sure speed up
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
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=#
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 (
SELECT
13 matches
Mail list logo
