Tom Lane wrote:
> "Kevin Grittner" writes:
>> With all the optimizer options on, and the from_collapse_limit and
>> join_collapse_limit values both set to 100, run an EXPLAIN (no
>> ANALYZE) on your big problem query. Let us know how long the
>> EXPLAIN runs. If it gets any errors, copy and pa
"Kevin Grittner" writes:
> With all the optimizer options on, and the from_collapse_limit and
> join_collapse_limit values both set to 100, run an EXPLAIN (no
> ANALYZE) on your big problem query. Let us know how long the EXPLAIN
> runs. If it gets any errors, copy and paste all available
> info
Alberto Dalmaso wrote:
> P.S.: to understand what the query has to make (and 80% of the view
> hve these to make): a lot of time is spend to pivoting a table with
> a structure like
> identifier, description_of_value, numeric value
> that has to be transformed in
> identifier, description_1, desc
P.S.: to understand what the query has to make (and 80% of the view hve
these to make): a lot of time is spend to pivoting a table with a
structure like
identifier, description_of_value, numeric value
that has to be transformed in
identifier, description_1, description_2, ..., description_n
where n
Alberto Dalmaso wrote:
> what does it mean using join_collapse_limit = 3
http://www.postgresql.org/docs/8.3/interactive/runtime-config-query.html#RUNTIME-CONFIG-QUERY-OTHER
-Kevin
--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscri
That what i send is the quick execution, with other parameters this
query simply doesn't come to an end.
It is the little query that changing the settings (using the default
with all the query analyzer on) becames really quick, while with this
settings (with some analyzer switched off) became very
Alberto Dalmaso wrote:
> Ok, here are the last rows for the vacuum analyze verbose
>
> INFO: free space map contains 154679 pages in 39 relations
> DETAIL: A total of 126176 page slots are in use (including
> overhead).
> 126176 page slots are required to track all free space.
> Current limits
Alberto Dalmaso wrote:
[...]
> in the explanation I'll see that the db use nasted loop.
[...]
Sorry for the remark off topic, but I *love* the term
"nasted loop". It should not go to oblivion unnoticed.
Yours,
Laurenz Albe
--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql
Ok, here are the last rows for the vacuum analyze verbose
INFO: free space map contains 154679 pages in 39 relations
DETAIL: A total of 126176 page slots are in use (including overhead).
126176 page slots are required to track all free space.
Current limits are: 16 page slots, 5000 relation
Even if the query end in aproximately 200 sec, the explain analyze is
still working and there are gone more than 1000 sec...
I leave it working this night.
Have a nice evening and thenks for the help.
--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes t
Unfortunatly the query need that level of complxity as the information I
have to show are spread around different table.
I have tryed the geqo on at the beginning but only with the default
parameters.
Tomorrow (my working day here in Italy is finished some minutes ago, so
I will wait for the end of
Alberto Dalmaso writes:
> Ok, but the problem is that my very long query performes quite well when
> it works with merge join but it cannot arrive to an end if it use other
> kind of joining.
> If i put all the parameter to on, as both of you tell me, in the
> explanation I'll see that the db use
Alberto Dalmaso wrote:
> I attach the explanation of the log query after setting all the
> enable to on. In this condition the query will never finish...
I notice that you many joins in there. If the query can't be
simplified, you probably need to boost the join_collapse_limit and
from_colla
Il giorno mar, 16/06/2009 alle 11.31 -0400, Tom Lane ha scritto:
> Alberto Dalmaso writes:
> > Il giorno mar, 16/06/2009 alle 15.58 +0100, Matthew Wakeling ha scritto:
> >>> enable_hashjoin = off
> >>> enable_nestloop = off
> >>> enable_seqscan = off
> >>> enable_sort = off
> >>
> >> Why are thes
Alberto Dalmaso wrote:
> do you thing it is impossible to find a
> configuration that works fine for both the kind of query?
No. We probably just need a little more information.
> The application have to run even versus oracle db... i wont have to
> write a different source for the two data
> Could you show us the result of SELECT version(); ?
of course I can
PostgreSQL 8.3.7 on x86_64-unknown-linux-gnu, compiled by GCC gcc (SUSE
Linux) 4.3.2 [gcc-4_3-branch revision 141291]
>
> Have you done any VACUUM VERBOSE lately and captured the output? If
> so, what do the last few lines sa
Alberto Dalmaso wrote:
> Il giorno mar, 16/06/2009 alle 15.58 +0100, Matthew Wakeling ha
> scritto:
>> On Tue, 16 Jun 2009, Alberto Dalmaso wrote:
>> > enable_hashjoin = off
>> > enable_nestloop = off
>> > enable_seqscan = off
>> > enable_sort = off
>>
>> Why are these switched off?
>>
> beca
Alberto Dalmaso writes:
> Il giorno mar, 16/06/2009 alle 15.58 +0100, Matthew Wakeling ha scritto:
>>> enable_hashjoin = off
>>> enable_nestloop = off
>>> enable_seqscan = off
>>> enable_sort = off
>>
>> Why are these switched off?
>>
> because of the need to pump up the performance of the compl
Il giorno mar, 16/06/2009 alle 15.58 +0100, Matthew Wakeling ha scritto:
> On Tue, 16 Jun 2009, Alberto Dalmaso wrote:
> >> What does your postgresql.conf file look like?
>
> > enable_hashjoin = off
> > enable_nestloop = off
> > enable_seqscan = off
> > enable_sort = off
>
> Why are these switche
Alberto Dalmaso wrote:
>> What version of PostgreSQL?
> 8.3 that comes with opensuse 11.1
Could you show us the result of SELECT version(); ?
> max_prepared_transactions = 30
Unless you're using distributed transactions or need a lot of locks,
that's just going to waste some RAM. Zero i
On Tue, 16 Jun 2009, Alberto Dalmaso wrote:
What does your postgresql.conf file look like?
enable_hashjoin = off
enable_nestloop = off
enable_seqscan = off
enable_sort = off
Why are these switched off?
and that is the explain of the too slow simple query
"Merge Join (cost=0.00..1032305.5
> What version of PostgreSQL?
8.3 that comes with opensuse 11.1
>
> What OS?
Linux, opensuse 11.1 64 bit
>
> What does the hardware look like? (CPUs, drives, memory, etc.)
2 * opteron dual core 8 GB RAM, 70 GB SCSI U320 RAID 1
>
> Do you have autovacuum running? What other regular maintenan
Alberto Dalmaso wrote:
> I have complex query that perform very well with mergejoin on and
> nestloop off.
> If I activate nestloop postgres try to use it and the query
> execution become inconclusive: after 3 hours still no answare so I
> kill the query.
> Tht's ok but, with this configuration
On Tue, Jun 16, 2009 at 03:37:42PM +0200, Alberto Dalmaso wrote:
> Hi everybody, I'm creating my database on postgres and after some days
> of hard work I'm arrived to obtain good performance and owfull
> performace with the same configuration.
> I have complex query that perform very well with mer
Hi everybody, I'm creating my database on postgres and after some days
of hard work I'm arrived to obtain good performance and owfull
performace with the same configuration.
I have complex query that perform very well with mergejoin on and
nestloop off.
If I activate nestloop postgres try to use it
25 matches
Mail list logo
