Hi,
I had a similar problem with many left join, reading about planning
optimization i tried to edit postgresql.conf and uncommented the line
join_collapse_limit = 8 and set it to 1, disables collapsing of explicit .
My query its taking 2000s in 8.4 and the same query 2ms in 8.3. Now its
working
On Wed, 15 Sep 2010, Merlin Moncure wrote:
On Wed, Sep 15, 2010 at 2:32 AM, Gerhard Wiesinger wrote:
On Tue, 14 Sep 2010, Merlin Moncure wrote:
np -- this felt particularly satisfying for some reason. btw, I think
you have some more low hanging optimization fruit. I think (although
it would
On Wed, Sep 15, 2010 at 2:32 AM, Gerhard Wiesinger wrote:
> On Tue, 14 Sep 2010, Merlin Moncure wrote:
>>
>> np -- this felt particularly satisfying for some reason. btw, I think
>> you have some more low hanging optimization fruit. I think (although
>> it would certainly have to be tested) hidin
On Tue, 14 Sep 2010, Merlin Moncure wrote:
np -- this felt particularly satisfying for some reason. btw, I think
you have some more low hanging optimization fruit. I think (although
it would certainly have to be tested) hiding your attribute
description under keyid is buying you nothing but head
On Tue, Sep 14, 2010 at 3:59 PM, Gerhard Wiesinger wrote:
> On Tue, 14 Sep 2010, Merlin Moncure wrote:
>
>> On Tue, Sep 14, 2010 at 2:07 AM, Gerhard Wiesinger
>> wrote:
>>>
>>> Hello Merlin,
>>>
>>> Seems to be a feasible approach. On problem which might be that when
>>> multiple rows are returne
On Tue, 14 Sep 2010, Merlin Moncure wrote:
On Tue, Sep 14, 2010 at 2:07 AM, Gerhard Wiesinger wrote:
Hello Merlin,
Seems to be a feasible approach. On problem which might be that when
multiple rows are returned that they are not ordered in each subselect
correctly. Any idea to solve that?
e.
On Tue, Sep 14, 2010 at 2:07 AM, Gerhard Wiesinger wrote:
> Hello Merlin,
>
> Seems to be a feasible approach. On problem which might be that when
> multiple rows are returned that they are not ordered in each subselect
> correctly. Any idea to solve that?
>
> e.g.
> Raumsolltemperatur | Raumistte
Hello Merlin,
Seems to be a feasible approach. On problem which might be that when
multiple rows are returned that they are not ordered in each subselect
correctly. Any idea to solve that?
e.g.
Raumsolltemperatur | Raumisttemperatur
Value from time 1 | Value from time 2
Value from time 2 |
On Mon, Sep 13, 2010 at 2:39 AM, Gerhard Wiesinger wrote:
> Hello,
>
> Any news or ideas regarding this issue?
hm. is retooling the query an option? specifically, can you try converting
CREATE OR REPLACE VIEW log_entries AS
SELECT
l.id AS id,
l.datetime AS datetime,
l.tdate AS tdate,
l
Hello,
Any news or ideas regarding this issue?
Thnx.
Ciao,
Gerhard
--
http://www.wiesinger.com/
On Sat, 4 Sep 2010, Gerhard Wiesinger wrote:
On Fri, 3 Sep 2010, Tom Lane wrote:
Gerhard Wiesinger writes:
8.3 query plans: http://www.wiesinger.com/tmp/pg_perf_83_new.txt
8.4 quey plans: ht
On Fri, 3 Sep 2010, Tom Lane wrote:
Gerhard Wiesinger writes:
8.3 query plans: http://www.wiesinger.com/tmp/pg_perf_83_new.txt
8.4 quey plans: http://www.wiesinger.com/tmp/pg_perf_84.txt
Hmm. The 8.3 plan is indeed assuming that the number of rows will stay
constant as we bubble up through
Gerhard Wiesinger writes:
> Back to the original problem:
Finally ;-)
> 8.3 query plans: http://www.wiesinger.com/tmp/pg_perf_83_new.txt
> 8.4 quey plans: http://www.wiesinger.com/tmp/pg_perf_84.txt
Hmm. The 8.3 plan is indeed assuming that the number of rows will stay
constant as we bubble up
On Fri, 3 Sep 2010, Tom Lane wrote:
Gerhard Wiesinger writes:
On Fri, 3 Sep 2010, Tom Lane wrote:
I think what may be happening here is that a postgres executable expects
to find itself in a full installation tree, ie if it's in /someplace/bin
then the timezone files are in /someplace/share,
Gerhard Wiesinger writes:
> On Fri, 3 Sep 2010, Tom Lane wrote:
>> I think what may be happening here is that a postgres executable expects
>> to find itself in a full installation tree, ie if it's in /someplace/bin
>> then the timezone files are in /someplace/share, etc. Did you do a full
>> "ma
On Fri, 3 Sep 2010, Tom Lane wrote:
Gerhard Wiesinger writes:
On Fri, 3 Sep 2010, Tom Lane wrote:
Doh. I hadn't looked closely at that. Probably you want
/usr/share/zoneinfo --- at least that's what the Red Hat RPMs use.
I tried even before I wrote to the mailinglist without success:
./c
Gerhard Wiesinger writes:
> On Fri, 3 Sep 2010, Tom Lane wrote:
>> Doh. I hadn't looked closely at that. Probably you want
>> /usr/share/zoneinfo --- at least that's what the Red Hat RPMs use.
> I tried even before I wrote to the mailinglist without success:
> ./configure
I'd definitely sugges
On Fri, 3 Sep 2010, Tom Lane wrote:
Gerhard Wiesinger writes:
On Fri, 3 Sep 2010, Tom Lane wrote:
Huh. Try strace'ing the process to see what it's doing.
It tries to find something in /usr/share/ ...
Ok, I think from the compile time options:
./configure --with-system-tzdata=/usr/share
Gerhard Wiesinger writes:
> On Fri, 3 Sep 2010, Tom Lane wrote:
>> Huh. Try strace'ing the process to see what it's doing.
> It tries to find something in /usr/share/ ...
> Ok, I think from the compile time options:
> ./configure --with-system-tzdata=/usr/share
Doh. I hadn't looked closely at
On Fri, 3 Sep 2010, Tom Lane wrote:
Gerhard Wiesinger writes:
On Fri, 3 Sep 2010, Tom Lane wrote:
Not even in that pgstartup.log file you sent stderr to?
Yes, also the redirected log file is empty. Also kernel log is empty.
Huh. Try strace'ing the process to see what it's doing.
It t
Gerhard Wiesinger writes:
> On Fri, 3 Sep 2010, Tom Lane wrote:
>> Not even in that pgstartup.log file you sent stderr to?
> Yes, also the redirected log file is empty. Also kernel log is empty.
Huh. Try strace'ing the process to see what it's doing.
> BTW: Shared memory can't be any issue?
I
On Fri, 3 Sep 2010, Tom Lane wrote:
Not even in that pgstartup.log file you sent stderr to?
I have seen cases before where Postgres couldn't log anything because of
SELinux. If this is a Red Hat based system, look in the kernel log for
AVC messages. If you see any, then SELinux is probably blo
Gerhard Wiesinger writes:
> On Thu, 2 Sep 2010, Tom Lane wrote:
>> Hm, you sure about that? What's in the postmaster log?
> That's the strange thing: I don't have anything in stdout/stderr log and
> in pg_log directory and even in syslog.
Not even in that pgstartup.log file you sent stderr to?
On Thu, 2 Sep 2010, Tom Lane wrote:
Gerhard Wiesinger writes:
Problem is that PostgreSQL doesn't listen and take much CPU and also disk
I/O. 8.3 was shut down cleanly.
Hm, you sure about that? What's in the postmaster log?
BTW: Do I need other postgres user with a different home directory
On Thu, 2 Sep 2010, Tom Lane wrote:
Gerhard Wiesinger writes:
Problem is that PostgreSQL doesn't listen and take much CPU and also disk
I/O. 8.3 was shut down cleanly.
Hm, you sure about that? What's in the postmaster log?
That's the strange thing: I don't have anything in stdout/stderr l
Gerhard Wiesinger writes:
> Problem is that PostgreSQL doesn't listen and take much CPU and also disk
> I/O. 8.3 was shut down cleanly.
Hm, you sure about that? What's in the postmaster log?
regards, tom lane
--
Sent via pgsql-performance mailing list (pgsql-performan
On Mon, 30 Aug 2010, Tom Lane wrote:
Gerhard Wiesinger writes:
BTW: I have the old data setup. /var/lib/pgsql-old. Is there a fast setup
with old version on different TCP port possible to compare query plans?
You'll need to reinstall the old executables. If you put the new
executables in th
Gerhard Wiesinger writes:
> BTW: I have the old data setup. /var/lib/pgsql-old. Is there a fast setup
> with old version on different TCP port possible to compare query plans?
You'll need to reinstall the old executables. If you put the new
executables in the same directories, it's not going to
On Mon, 30 Aug 2010, Tom Lane wrote:
Gerhard Wiesinger writes:
I know the drawbacks of an EAV design but I don't want to discuss that. I
want to discuss the major performance decrease of PostgreSQL 8.3
(performance was ok) to PostgreSQL 8.4 (performance is NOT ok).
Any further ideas how I c
Gerhard Wiesinger writes:
> I know the drawbacks of an EAV design but I don't want to discuss that. I
> want to discuss the major performance decrease of PostgreSQL 8.3
> (performance was ok) to PostgreSQL 8.4 (performance is NOT ok).
> Any further ideas how I can track this down?
> Can someone
On Mon, 30 Aug 2010, Pavel Stehule wrote:
2010/8/30 Gerhard Wiesinger :
On Mon, 30 Aug 2010, Pavel Stehule wrote:
Hello
2010/8/30 Andreas Kretschmer :
Gerhard Wiesinger wrote:
I know that the data model is key/value pairs but it worked well in 8.3.
I need this flexibility.
Any ideas?
2010/8/30 Gerhard Wiesinger :
> On Mon, 30 Aug 2010, Pavel Stehule wrote:
>
>> Hello
>>
>> 2010/8/30 Andreas Kretschmer :
>>>
>>> Gerhard Wiesinger wrote:
>>>
I know that the data model is key/value pairs but it worked well in 8.3.
I need this flexibility.
Any ideas?
>>>
>>> If
On Mon, 30 Aug 2010, Pavel Stehule wrote:
Hello
2010/8/30 Andreas Kretschmer :
Gerhard Wiesinger wrote:
I know that the data model is key/value pairs but it worked well in 8.3.
I need this flexibility.
Any ideas?
If i understand the query correctly it's a pivot-table, right?
no - it's
On Mon, 30 Aug 2010, Scott Marlowe wrote:
On Mon, Aug 30, 2010 at 1:25 AM, Gerhard Wiesinger wrote:
On Mon, 30 Aug 2010, Scott Marlowe wrote:
On Mon, Aug 30, 2010 at 12:20 AM, Gerhard Wiesinger
wrote:
Hello,
I just upgraded with pg_dump/restore from PostgreSQL 8.3.11 to 8.4.4 but
I'm
hav
On Mon, 30 Aug 2010, Andreas Kretschmer wrote:
Gerhard Wiesinger wrote:
I know that the data model is key/value pairs but it worked well in 8.3.
I need this flexibility.
Any ideas?
If i understand the query correctly it's a pivot-table, right?
The view flattens the key/value structure (
Hello
2010/8/30 Andreas Kretschmer :
> Gerhard Wiesinger wrote:
>
>> I know that the data model is key/value pairs but it worked well in 8.3.
>> I need this flexibility.
>>
>> Any ideas?
>
> If i understand the query correctly it's a pivot-table, right?
>
no - it's just EAV table on very large d
On Mon, Aug 30, 2010 at 1:25 AM, Gerhard Wiesinger wrote:
> On Mon, 30 Aug 2010, Scott Marlowe wrote:
>
>> On Mon, Aug 30, 2010 at 12:20 AM, Gerhard Wiesinger
>> wrote:
>>>
>>> Hello,
>>>
>>> I just upgraded with pg_dump/restore from PostgreSQL 8.3.11 to 8.4.4 but
>>> I'm
>>> having major perform
On Mon, 30 Aug 2010, Scott Marlowe wrote:
On Mon, Aug 30, 2010 at 12:20 AM, Gerhard Wiesinger wrote:
Hello,
I just upgraded with pg_dump/restore from PostgreSQL 8.3.11 to 8.4.4 but I'm
having major performance problems with a query with many left joins. Problem
is that costs are now very, ver
Gerhard Wiesinger wrote:
> I know that the data model is key/value pairs but it worked well in 8.3.
> I need this flexibility.
>
> Any ideas?
If i understand the query correctly it's a pivot-table, right?
If yes, and if i where you, i would try to rewrite this query, to
something like:
select
On Mon, Aug 30, 2010 at 12:20 AM, Gerhard Wiesinger wrote:
> Hello,
>
> I just upgraded with pg_dump/restore from PostgreSQL 8.3.11 to 8.4.4 but I'm
> having major performance problems with a query with many left joins. Problem
> is that costs are now very, very, very high (was ok in 8.3). Analyze
Hello,
I just upgraded with pg_dump/restore from PostgreSQL 8.3.11 to 8.4.4 but
I'm having major performance problems with a query with many left joins.
Problem is that costs are now very, very, very high (was ok in 8.3).
Analyze has been done. Indexes are of course there.
-> Merge Left J
40 matches
Mail list logo
