"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
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=# explain analyze select * from forecastelement where valid_time
This sure speed up the query, it is fast.
PWFPM_DEV=# explain analyze select * from forecastelement where valid_time
between '2004-01-12'::date and '2003-01-12'::date;
QUERY PLAN
--
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 can we improve
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?
PWFPM_DEV=# explain analyze select * from forecastelement where valid_time >
date '2004-01-23'::date
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
Ok, thanks.
I will do it.
regards,
ivan.
Tom Lane wrote:
> pginfo <[EMAIL PROTECTED]> writes:
> > In wich case will this trigger work faster if write it in "C"?
>
> Given that the dominant part of the time will be spent down inside SPI
> in either case, I doubt you will be able to see much diffe
pginfo <[EMAIL PROTECTED]> writes:
> In wich case will this trigger work faster if write it in "C"?
Given that the dominant part of the time will be spent down inside SPI
in either case, I doubt you will be able to see much difference. You
need to think about how to optimize the invoked query, no
Hi,
thanks for the answer.
It is very interest, because I readet many times that if I write the trigger
in "C" it will work faster.
In wich case will this trigger work faster if write it in "C"?
In all my triggres I have "select " or "insert into mytable select ..."
or "update mytable set ...w
pginfo <[EMAIL PROTECTED]> writes:
> I was supprised that the pgsql trigger take ~8 sec. to insert this rows
> and the "C" trigger take ~ 17 sec.
The reason is that plpgsql caches the plan for the invoked SELECT,
whereas the way you coded the C function, it's re-planning that SELECT
on every call.
Hi there,
i m new in this board. I read a little here to find a solution for my problem. But i couldn´t find something.
My Problem ist:
I m programming a counter in PHP with Postgres as DB. I had 6,000 visitors across all day, so everything worked fine first.
Yesterday i got 80K Users at my sites
Hi,
I need to speed up the triggers that we are using and I began to make
some tests to compare the "C" and pgSQL trigger performance.
I try to write two identical test triggers (sorry I do not know very
good the pgsql C interface and I got one of examples and werite it) and
attached it on insert
19 matches
Mail list logo
