Tom,
You nailed it. The t1 table was using 9600 relpages versus 410 after the
vacuum full. The two databases are now showing similar execution plans and
times.
Thanks for your help. It is greatly appreciated.
Doug Eck
- Original Message
From: Tom Lane <[EMAIL PROTECTED]>
To: Dou
Doug Eck <[EMAIL PROTECTED]> writes:
> The new plan from the slower db:
>-> Index Scan using t1_uc2 on t1 (cost=0.00..25604.74 rows=204906
> width=32) (actual time=0.061..327.285 rows=8438 loops=1)
> Filter: active
This seems a bit fishy. In the first place, with such a simple fi
Setting enable_nestloop = off did result in a hash join, so I also set
enable_hashjoin = off.
The new plan from the slower db:
QUERY PLAN
-
Doug Eck <[EMAIL PROTECTED]> writes:
> Any ideas as to what could the first db to opt for the slower subquery rather
> than the merge?
Not from the information given. Presumably db1 thinks that the
mergejoin plan would be slower, but why it thinks that isn't clear yet.
Try setting enable_nestloo
I have two identical databases that run the same query each morning. Starting
this morning, something caused the first db to start using a different
execution plan for the query, resulting in much worse performance. I've have
tried several things this morning, but I am currently stumped on wha
Hello
I'm running pgsql 8.1.11 (from debian stable) on a server with 16GB RAM
(Linux helios 2.6.18-6-amd64 #1 SMP Tue Aug 19 04:30:56 UTC 2008 x86_64
GNU/Linux).
I have a table "tickets" with 1 000 000 insert by month ( ~2600 each 2hours
) (for the moment 1300 rows for 5GB )
and i have to extr
kiki wrote:
> I expanded work_mem to 256 Mb and created index on table
>
> create index xxx on system_alarm (id_camera, date, time) where confirmed =
> 'false' and dismissed = 'false';
That index is not used for the query (as could be expected).
You better remove it.
> the processor load now exe
> kiki wrote:
>> The speed of the query is not a problem but the strange thing is the
>> processor load with postmaster when the query is executed.
>> I dont now how to reduce processor load.
>
> Did you try without the ORDER BY?
> Where are the execution plans?
>
> Yours,
> Laurenz Albe
>
I expa
kiki wrote:
> The speed of the query is not a problem but the strange thing is the
> processor load with postmaster when the query is executed.
> I dont now how to reduce processor load.
Did you try without the ORDER BY?
Where are the execution plans?
Yours,
Laurenz Albe
--
Sent via pgsql-perf
Please try to avoid top-posting where inappropriate.
kiki wrote:
>>> There is still heavy load of postmaster process (up to 100%) for a simple
>>> query
>>>
>>> EXPLAIN ANALYSE SELECT * FROM system_alarm WHERE id_camera='3' AND
>>> confirmed='false' AND dismissed='false' ORDER BY date DESC, time
On Mon, Sep 29, 2008 at 10:29:45AM +0200, [EMAIL PROTECTED] wrote:
> >> EXPLAIN ANALYSE SELECT * FROM system_alarm WHERE id_camera='3' AND
> >> confirmed='false' AND dismissed='false' ORDER BY date DESC, time DESC
> >> LIMIT 1;
> Sorry, without LIMIT returns around 70 rows.
> Tried to index da
Hello Herald,
the queried table is used for communication between server application and
web user interface.
When application detects an event it writes it down in table.
The web client checks every 10 second if something new is written in the
table.
Usually nothing new is written but the client h
Sorry, without LIMIT returns around 70 rows.
Tried to index date column and time column but the performance is pretty
much the same.
Everything is OK, I just dont understand way is this query burdening the
processor so much.
Regards,
Maja
> kiki wrote:
>> First I have increased shared_buffer
kiki wrote:
> First I have increased shared_buffers from 2000 to 8000. Since the
> postgresql is on Debian I had to increase SHMMAX kernel value.
> Everything is working much faster now.
Good to hear that the problem is gone.
> There is still heavy load of postmaster process (up to 100%) for a si
Hello Maja,
> EXPLAIN ANALYSE SELECT * FROM system_alarm WHERE id_camera='3' AND
> confirmed='false' AND dismissed='false' ORDER BY date DESC, time DESC
> LIMIT 1;
>
> (the table is indexed by id_camera, has around 1 milion rows, and this
> query returns around 70 rows and is executed (EXPLAI
Thanks for the instructions for detecting the problem.
It helped a lot.
First I have increased shared_buffers from 2000 to 8000. Since the
postgresql is on Debian I had to increase SHMMAX kernel value.
Everything is working much faster now.
There is still heavy load of postmaster process (up to 1
16 matches
Mail list logo
