Hello pgsql-sql,
I have postgresql 8.1.3 and database with about 2,7GB (90% large
objects).
When I execute this query postgresql calculate this 2min 50sec. How
can I optimize this query?
select towar.id_towar,towar.key2,towar.nazwa,0 as min,0 as
Send an EXPLAIN ANALYZE of the query along with the description of the
involved tables. Also hardware information (RAM, disks, CPU), what
other applications are running on that box and the parameter values in
postgresql.conf that you changed from the defaults would be
interesting.
Markus
Hello Markus,
Wednesday, March 22, 2006, 6:58:44 PM, you wrote:
MB Send an EXPLAIN ANALYZE of the query along with the description of the
MB involved tables. Also hardware information (RAM, disks, CPU), what
MB other applications are running on that box and the parameter values in
MB
That's an explain. We need explain analyze.
2006/3/23, Maciej Piekielniak [EMAIL PROTECTED]:
Hello Markus,
Wednesday, March 22, 2006, 6:58:44 PM, you wrote:
MB Send an EXPLAIN ANALYZE of the query along with the description of the
MB involved tables. Also hardware information (RAM, disks,
In the meantime, try this:
SELECT
towar.id_towar,
towar.key2,
towar.nazwa,
0 AS min,
0 AS max,
towar.ilosc_jed,
towar.ilosc_nom,
towar.ilosc_paczkowa,
dostawcy.id_dostawcy,
jednostka_miary.jednostka,
0.0 AS
Hello Markus,
Wednesday, March 22, 2006, 7:32:11 PM, you wrote:
MB foo.z_zamowien,
MB ) AS foo ON (foo.id_towar = towar.id_towar)
foo?
--
Best regards,
Maciejmailto:[EMAIL PROTECTED]
---(end of
Hello Markus,
ERROR: column foo.z_zamowien must appear in the GROUP BY clause or be used
in an aggregate function
--
Best regards,
Maciejmailto:[EMAIL PROTECTED]
---(end of broadcast)---
TIP 5: don't forget to
Hello Markus,
Sorry, I try this:
SELECT
towar.id_towar,
towar.key2,
towar.nazwa,
0 AS min,
0 AS max,
towar.ilosc_jed,
towar.ilosc_nom,
towar.ilosc_paczkowa,
dostawcy.id_dostawcy,
jednostka_miary.jednostka,
0.0
Maciej Piekielniak wrote:
Hello Markus,
Oryginal query return 7881 rows , your query only 729 rows.
But it's faster!
---(end of broadcast)---
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if
On Wed, 27 Aug 2003, ProgHome wrote:
You were right, Stephan !
The query below is still not correct ... because the second line
shouldn't be shown !
Now I really don't know how I could rewrite this without a subquery
because it doesn't seem to be possible with some LEFT or INNER joins !
Do
-
From: Stephan Szabo [mailto:[EMAIL PROTECTED]
Sent: Wednesday, August 13, 2003 2:29 PM
To: ProgHome
Cc: 'Franco Bruno Borghesi'; [EMAIL PROTECTED]
Subject: RE: [SQL] How to optimize this query ?
On Wed, 13 Aug 2003, ProgHome wrote:
I tried with some LEFT JOINS, which give me the possibility
]
Sent: Wednesday, August 13, 2003 12:18 PM
To: [EMAIL PROTECTED];
[EMAIL PROTECTED]
Subject: Re: [SQL] How to optimize
this query ?
Maybe its better now. Anyway, what I think is that joining will perform better than using IN. Am I wrong?
SELECT
L.*
FROM
lead L
LEFT JOIN purchase P ON (L.id
that is not equal to 21101.
-Original Message-
From: Stephan Szabo [mailto:[EMAIL PROTECTED]
Sent: Wednesday, August 13, 2003 1:10 PM
To: Franco Bruno Borghesi
Cc: [EMAIL PROTECTED]; [EMAIL PROTECTED]
Subject: Re: [SQL] How to optimize this query ?
On 13 Aug 2003, Franco Bruno
On Tue, 12 Aug 2003, Franco Bruno Borghesi wrote:
I *guess* this query does the same as yours (please verify).
It does not in general unfortunately. :( I see two possible
problems.
The first is that by using an inner join you're going to lose
any rows where there is no match on the right hand
OK, here is the final query without any subquery ...
--
SELECT L. *
FROM lead L
LEFT JOIN purchase P ON ( L.id = P.lead_id )
LEFT JOIN member_exclusion M ON ( P.member_id = M.member_id_to_exclude
)
LEFT JOIN (
SELECT DISTINCT affiliate_locked_id
FROM affiliate_lockout
21101
GROUP BY lead.id
-Original Message-
From: Stephan Szabo [mailto:[EMAIL PROTECTED]
Sent: Wednesday, August 13, 2003 1:10 PM
To: Franco Bruno Borghesi
Cc: [EMAIL PROTECTED]; [EMAIL PROTECTED]
Subject: Re: [SQL] How to optimize this query ?
On 13 Aug 2003, Franco Bruno Borghesi
On 13 Aug 2003, Franco Bruno Borghesi wrote:
Maybe its better now. Anyway, what I think is that joining will perform
better than using IN. Am I wrong?
Generally that's true (for 7.3 and earlier). For 7.4 IN has gotten much
better, and you probably want to retry with IN. However, it's
Hi all
I have to optmize this query, because it takes a while to run (about
30s)
Here are the tables (with the keys):
affiliate_lockout (6 rows) (member_id, affiliate_id)
lead (4490 rows) (id, ...)
member (6 rows) (id, ...)
member_exclusion (3 rows)(member_id,
I tried with some LEFT JOINS, which give me the possibility to keep
the information of the right table.
I have now the following query, which is 10 times faster !!! (from 16s
to 1.6s)
But I's like to remove the last subquery, to see if it faster ;)
Can somebody help me ?
On 12 Aug 2003, krysto wrote:
Hi all
I have to optmize this query, because it takes a while to run (about
30s)
Here are the tables (with the keys):
affiliate_lockout (6 rows) (member_id, affiliate_id)
lead (4490 rows) (id, ...)
member (6 rows) (id, ...)
Title: RE: [SQL] How to optimize this query ?
Actually, I have got another query where I need to remove the subqueries ... It is almost the same query but this time, I don't search in the table LEAD but in the table MEMBER
HERE are the tables:
affiliate_lockout (6 rows) (member_id
On Wed, 13 Aug 2003, ProgHome wrote:
select member.id, automated.delivery, member.email
from (automated INNER JOIN member ON member.id = automated.member_id)
where activated=1
and website='$SITE_NAME'
and (select count(*) from trans_member where
I *guess* this query does the same as yours (please verify).
SELECT
L.*
FROM
lead L
INNER JOIN purchase P ON (L.id=P.lead_id)
INNER JOIN affiliate_lockout A ON
(L.affiliate_id=A.affiliate_locked_id)
INNER JOIN member_exclusion M ON
For the following query, I have a little problem ...
First, I have to rewrite several times the same query because the
alias are not recognised in the same query ( I got an error when I try
to reuse the alias nb_bogus_leads, for instance). Do you have a way
to avoid this ? Because If I do so, the
How to optimize query or just force postgre to do it my way ?
Example:
table continets (
id numeric, ..., active numeric
);
table countries (
id numeric, id_continent numeric, ..., active numeric
);
table cities (
id numeric, id_country numeric, ..., active numeric
);
-Original Message-
From: [EMAIL PROTECTED]
[mailto:[EMAIL PROTECTED]]On Behalf Of Milosz Krajewski
Sent: Monday, July 29, 2002 16:50
To: [EMAIL PROTECTED]
Subject: [SQL] How to optimize SQL query ?
How to optimize query or just force postgre to do it my way ?
Example:
table
Milosz Krajewski [EMAIL PROTECTED] writes:
Can I force postgre do it my way ?
Possibly. See
http://www.ca.postgresql.org/users-lounge/docs/7.2/postgres/explicit-joins.html
regards, tom lane
---(end of broadcast)---
TIP
!
-Original Message-
From: Milosz Krajewski [mailto:[EMAIL PROTECTED]]
Sent: Friday, August 02, 2002 15:42
To: Cdric Dufour (Cogito Ergo Soft)
Subject: Re: [SQL] How to optimize SQL query ?
Cdric Dufour (Cogito Ergo Soft) wrote:
Use the explicit JOIN syntax and join each table one after
28 matches
Mail list logo
