Denis,
> I'm wondering what else can be done to tune this type of query. Is 3
> minutes reasonable given the amount of data that is loaded into the 3
> tables? Is there anyone else who has made comparisons between Oracle and
> PostgreSQL?
We will probably be a bit slower on aggregates than Orac
"Sailer, Denis (YBUSA-CDR)" <[EMAIL PROTECTED]> writes:
> We are evaluating PostgreSQL for a typical data warehouse application. I
> have 3 tables below that are part of a Star schema design. The query listed
> below runs in 16 seconds on Oracle 9.2 and 3+ minutes on PostgreSQL 7.3.3
> Here are t
We are evaluating PostgreSQL for a typical data warehouse application. I
have 3 tables below that are part of a Star schema design. The query listed
below runs in 16 seconds on Oracle 9.2 and 3+ minutes on PostgreSQL 7.3.3
Here are the details.
I'm wondering what else can be done to tune this ty
Hi all!
I have a strange behavior with this query:
SELECT c.id_contenido,p.fecha_publicacion,c.titulo_esp,c.activo,c.activo,s.label_esp
as label_sbc,p.orden
,p.tapa_spc,p.tapa_cat,p.tapa_principal,p.id_publicacion,ca.label_esp as
label_cat,sp.label_esp as label_spc
FROM cont_contenido c ,cont_p
"Michael Mattox" <[EMAIL PROTECTED]> writes:
> It's much slower but I appreciate you taking the time to try. I'm pretty
> new to SQL so I must admin this query is very confusing for me. I'm using
> Java Data Objects (JDO, an O/R mapping framework) but the implementation I'm
> using (Kodo) isn't s
Rod Taylor <[EMAIL PROTECTED]> writes:
>> monitorstatusx_datex_monitorx_index on monitorstatusx ms
>> (cost=3D0.00..1159.33 rows=3D890 width=3D83) (actual time=3D0.19..1287.02=
> rows=3D628
>> loops=3D1)
>> Index Cond: (("outer".jdoidx =3D ms.moni=
> torx)
>> AND (ms.datex >=3D '2003-06-20 08:57:2
With a slight correction (you had m & mx so I changed them to be all mx, I
hope this is what you intended) this query works. It's exactly the same
speed, but it doesn't give me the warnings I was getting:
NOTICE: Adding missing FROM-clause entry for table "monitorx"
NOTICE: Adding missing FROM-
> Finally, decided to do an ad-hoc adjustment. Try this, or (wild guess) try
> to completely eliminate the WHERE part by subselects on ms and monitorx.
>
> This may be faster, slower, or even give different results, based
> on whether
> I guessed the 1:N relationships right or not.
It's much slowe
Michael,
Actually, you missed an alias :) the select now returned 800k rows!
(according to explain)
pointed it out below. See my prev mail for more.
If it's possible, try your query on a backend and look for notices like
"Adding missing FROM clause for table ..."
G.
> I didn't notice that before, thanks for pointing that out. I just tried
> adding monitorx.idx to the select and it ended up making my query take
> several minutes long. Any ideas how I can fix this and keep my performance?
By using it aliased and non-aliased (2 different references to the same
Michael,
This whole query looks like a mess to me. Since I don't know the exact model
and the table stats, I don't even try to rewrite your query, however, here
are the weak points I can think of:
* as Rod pointed out, there are more tables in WHERE that aren't in FROM.
This can be a bug, but the
> Oh, and using tables in your where clause that aren't in the from clause
> is non-portable and often hides bugs:
>
> from monitorstatusx ms
> , monitorstatusitemx msi
> where monitorx.idx = 'M-TEST_1444-TEST_00_10560561260561463219352'
>
> Are you sure you sure you don't have any duplicated
> > You might try a multi-column index on (ms.monitorx, ms.datex).
>
> Just tried it, it didn't prevent the sort. But it sounds like the sort
> isn't the problem, correct?
The sort isn't actually doing any sorting, so it's virtually free. The
sort is taking less than 3ms as the data is already
On Wed, 25 Jun 2003, Achilleus Mantzios wrote:
> What i think would be ideal (helpful/feasible)
> is some kind of documentation of the algorithms involved
> in the planner/optimizer, along with some pointers
> to postgresql.conf parameters where applicable.
>
> This way we will know
> - Why somet
> Are jdoidx and monitorx integers?
Yes both are integers:
-- Table: public.monitorstatusx
CREATE TABLE public.monitorstatusx (
averageconnecttimex numeric(65535, 65532),
averagedurationx numeric(65535, 65532),
datex timestamp,
idx varchar(255),
jdoclassx varchar(255),
jdoidx int8 NOT
> Here's the output of explain analyze. The query typically takes 0-4 seconds
> depending on the time frame. It's run very frequently especially to process
> the nightly reports.
The plan picked seems reasonable (estimated costs / tuples is close to
actual).
I think the biggest hit is this inde
Sorry, I neglected to say the version, yes I'm using Postgres 7.3.2 on
Linux.
Here's the output of explain analyze. The query typically takes 0-4 seconds
depending on the time frame. It's run very frequently especially to process
the nightly reports.
veriguard=# explain analyze select ms.averag
Is this 7.3.x? Can we see explain analyze output for the query?
On Wed, 2003-06-25 at 07:46, Michael Mattox wrote:
> I've used indexes to speed up my queries but this query escapes me. I'm
> curious if someone can suggest an index or a way to modify the query to use
> the index. The query is:
>
On 25/06/2003 10:47 Michael Mattox wrote:
I'm using Java Data Objects (JDO) which is an O/R mapper. It generated
the
schema from my object model by default it used a table for a sequence. I
just got finished configuring it to use a real postgres sequence. With
the
way they have it designed, it o
I've used indexes to speed up my queries but this query escapes me. I'm
curious if someone can suggest an index or a way to modify the query to use
the index. The query is:
select ms.averageconnecttimex as ms_averageconnecttime, ms.averagedurationx
as ms_averageduration, ms.datex as ms_date, ms.
I agree that a "directed graph"-like performance map
would be difficult to be written or understood.
What i think would be ideal (helpful/feasible)
is some kind of documentation of the algorithms involved
in the planner/optimizer, along with some pointers
to postgresql.conf parameters where appli
> I think the biggest area of confusion for me was that the various parameters
> are very briefly described and no context is given for their parameters.
> improvements to the performance sections of the documentation would make a
> huge difference.
Agreed.. Josh has done some work recently re-
On Wed, 2003-06-25 at 04:12, Hilary Forbes wrote:
> PM4JI but from my point of view this has been a most useful thread. I too have
> found it difficult to find the right bit of documentation on performance. I *think*
> what is needed is some sort of a route map, Poor Performance - start here.
On 25 Jun 2003 at 11:47, Michael Mattox wrote:
> I'm using Java Data Objects (JDO) which is an O/R mapper. It generated the
> schema from my object model by default it used a table for a sequence. I
> just got finished configuring it to use a real postgres sequence. With the
> way they have it d
> [ This has been written offline yesterday. Now I see that most of it
> has already been covered. I send it anyway ... ]
Still great advice with slightly different explanations, very useful.
> |INFO: --Relation public.jdo_sequencex--
> |INFO: Pages 28: Changed 1, Empty 0; Tup 1: Vac 5124, Ke
I agree a route map would really help.
> -Original Message-
> From: Hilary Forbes [SMTP:[EMAIL PROTECTED]
> Sent: 25 June 2003 10:12
> To: Rod Taylor
> Cc: [EMAIL PROTECTED]
> Subject: Re: [PERFORM] Performance advice
>
> PM4JI but from my point of view this has been a most usefu
PM4JI but from my point of view this has been a most useful thread. I too have found
it difficult to find the right bit of documentation on performance. I *think* what is
needed is some sort of a route map, Poor Performance - start here. Then some
questions with sections of the documentation
27 matches
Mail list logo
