"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
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 l
13 matches
Mail list logo
