On Sat, Mar 23, 2013 at 3:27 PM, Misa Simic misa.si...@gmail.com wrote:
Hi Jeff,
It seems my previous mail has not showed up in the list... copied/pasted
again belloew
However, you said something important:
The join to the state table is not necessary. Between the foreign key
and the
I basically don't have any control over the generated select statement.
I'm using Mondrian and that is the select statement that gets passed to
Postgres. You're right that if you remove the count(id), the query is
faster but I can't do that since the select statement is being executed
from
I assume there are reasons not to throw away join to state. May be it still
can be done as the last thing. This should help further:
SELECT counts.* FROM (
SELECT busbase.state AS state, count(busbase.id) AS m0 FROM busbase
GROUP BY busbase.state ) AS counts
INNER JOIN state USING (state)
On Friday, March 22, 2013, Cindy Makarowsky wrote:
I have two tables in Postgres 9.2 on a Linux server with 8GB of RAM. The
first table has 60 million records:
You have over 40GB of data in that table, so there is no way you are going
to get it into 8GB RAM without some major reorganization.
Hi Jeff,
It seems my previous mail has not showed up in the list... copied/pasted
again belloew
However, you said something important:
The join to the state table is not necessary. Between the foreign key
and the primary key, you know that every state exists, and that every state
exists only
But, I do have an index on Table1 on the state field which is in my group
by condition:
CREATE INDEX statidx2
ON table1
USING btree
(state COLLATE pg_catalog.default );
I have vacuumed the table too.
On Fri, Mar 22, 2013 at 5:13 PM, Josh Berkus j...@agliodbs.com wrote:
On 03/22/2013
Hi,
there is something mixed..
your index is on table1
Explain Analyze reports about table called: busbase
Kind Regards,
Misa
2013/3/22 Cindy Makarowsky cindymakarow...@gmail.com
But, I do have an index on Table1 on the state field which is in my group
by condition:
CREATE
I changed the name of the table for the post but forgot to change it in the
results of the explain. Table1 is busbase.
On Fri, Mar 22, 2013 at 6:25 PM, Misa Simic misa.si...@gmail.com wrote:
Hi,
there is something mixed..
your index is on table1
Explain Analyze reports about table
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
Alberto Dalmaso dalm...@clesius.it 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,
Kevin Grittner kevin.gritt...@wicourts.gov 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
Tom Lane t...@sss.pgh.pa.us wrote:
Kevin Grittner kevin.gritt...@wicourts.gov 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.
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
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
Alberto Dalmaso dalm...@clesius.it 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.
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 dalm...@clesius.it 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
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
Alberto Dalmaso dalm...@clesius.it 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
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 maintenance to
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
Alberto Dalmaso dalm...@clesius.it 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
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 switched off?
Alberto Dalmaso dalm...@clesius.it 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?
because
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 say?
Alberto Dalmaso dalm...@clesius.it 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
Il giorno mar, 16/06/2009 alle 11.31 -0400, Tom Lane ha scritto:
Alberto Dalmaso dalm...@clesius.it 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
Alberto Dalmaso dalm...@clesius.it 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
Alberto Dalmaso dalm...@clesius.it 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
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
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
So the issue is that instead of taking 174 seconds the query now takes
201?
I'm guessing that SQL server might be using index covering, but that's
just a guess. Posting query plans (prefferably with actual timing info;
EXPLAIN ANALYZE on PostgreSQL and whatever the equivalent would be for
MSSQL)
32 matches
Mail list logo
