pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedHow to optimize this query ?
28+ messages / 7 participants
[nested] [flat]
* How to optimize this query ?
@ 2003-08-12 18:21 krystoffff <proghome@silesky.com>
0 siblings, 2 replies; 28+ messages in thread
From: proghome@silesky.com @ 2003-08-12 18:21 UTC (permalink / raw)
To: pgsql-sql
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, member_id_to_exclude)
purchase (10000 rows) (lead_id, member_id, ...)
Here is the query:
SELECT *
FROM lead
WHERE
(exclusive IS NULL OR (exclusive = 0 AND nb_purchases < 3)) AND
id NOT IN (
SELECT lead_id
FROM purchase
WHERE member_id = 21101
) AND affiliate_id NOT
IN (
SELECT affiliate_locked_id
FROM affiliate_lockout
WHERE member_id = 21101
) AND id NOT
IN (
SELECT lead_id
FROM purchase
INNER JOIN member_exclusion
WHERE purchase.member_id = member_exclusion.member_id_to_exclude AND
purchase.member_id = 21101
)
I wonder the problem is with the subqueries (which are apparently very
slow to run, according to what I read), but I can't figure how to
rewrite this query without any subquery ...
Maybe the problem comes from the index ... How would you create your
indexes to optimize this query ?
Could somebody help me ?
Thanks
krystoffff
^ permalink raw reply [nested|flat] 28+ messages in thread
* Re: How to optimize this query ?
@ 2003-08-13 00:52 Stephan Szabo <sszabo@megazone.bigpanda.com>
parent: krystoffff <proghome@silesky.com>
1 sibling, 0 replies; 28+ messages in thread
From: Stephan Szabo @ 2003-08-13 00:52 UTC (permalink / raw)
To: krystoffff <proghome@silesky.com>; +Cc: pgsql-sql
On 12 Aug 2003, krystoffff 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, ...)
> member_exclusion (3 rows) (member_id, member_id_to_exclude)
> purchase (10000 rows) (lead_id, member_id, ...)
>
I think you can possibly get better results in 7.3 and earlier
by using NOT EXISTS.
> Here is the query:
> SELECT *
> FROM lead
> WHERE
> (exclusive IS NULL OR (exclusive = 0 AND nb_purchases < 3)) AND
>
NOT EXISTS (
select lead_id from
purchase where lead_id=lead.id
and member_id=21101)
> id NOT IN (
> SELECT lead_id
> FROM purchase
> WHERE member_id = 21101
AND NOT EXISTS (
select * from affiliate_lockout
WHERE member_id=21101 and
affiliate_locked_id=lead.affiliate_id)
> ) AND affiliate_id NOT
> IN (
>
> SELECT affiliate_locked_id
> FROM affiliate_lockout
> WHERE member_id = 21101
> ) AND id NOT
> IN (
>
> SELECT lead_id
> FROM purchase
> INNER JOIN member_exclusion
> WHERE purchase.member_id = member_exclusion.member_id_to_exclude AND
> purchase.member_id = 21101
Doesn't this condition end up giving you a subset of the rows in the first
one?
^ permalink raw reply [nested|flat] 28+ messages in thread
* Re: How to optimize this query ?
@ 2003-08-13 01:04 Franco Bruno Borghesi <franco@akyasociados.com.ar>
parent: krystoffff <proghome@silesky.com>
1 sibling, 1 reply; 28+ messages in thread
From: Franco Bruno Borghesi @ 2003-08-13 01:04 UTC (permalink / raw)
To: proghome@silesky.com; +Cc: pgsql-sql
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
(P.member_id=M.member_id_to_exclude)
WHERE
L.exclusive IS NULL OR
(
L.exclusive=0 AND
L.nb_purchases<3
) AND
P.lead_id<>21101 AND
A.member_id<>21011
Hope it performs better.
> 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, member_id_to_exclude)
> purchase (10000 rows) (lead_id, member_id, ...)
>
>
> Here is the query:
> SELECT *
> FROM lead
> WHERE
> (exclusive IS NULL OR (exclusive = 0 AND nb_purchases < 3)) AND
>
> id NOT IN (
>
> SELECT lead_id
> FROM purchase
> WHERE member_id = 21101
> ) AND affiliate_id NOT
> IN (
>
> SELECT affiliate_locked_id
> FROM affiliate_lockout
> WHERE member_id = 21101
> ) AND id NOT
> IN (
>
> SELECT lead_id
> FROM purchase
> INNER JOIN member_exclusion
> WHERE purchase.member_id = member_exclusion.member_id_to_exclude AND
> purchase.member_id = 21101
> )
>
>
> I wonder the problem is with the subqueries (which are apparently very
> slow to run, according to what I read), but I can't figure how to
> rewrite this query without any subquery ...
>
> Maybe the problem comes from the index ... How would you create your
> indexes to optimize this query ?
>
> Could somebody help me ?
> Thanks
> krystoffff
>
> ---------------------------(end of
> broadcast)--------------------------- TIP 5: Have you checked our
> extensive FAQ?
>
> http://www.postgresql.org/docs/faqs/FAQ.html
^ permalink raw reply [nested|flat] 28+ messages in thread
* Re: How to optimize this query ?
@ 2003-08-13 01:29 Stephan Szabo <sszabo@megazone.bigpanda.com>
parent: Franco Bruno Borghesi <franco@akyasociados.com.ar>
0 siblings, 2 replies; 28+ messages in thread
From: Stephan Szabo @ 2003-08-13 01:29 UTC (permalink / raw)
To: Franco Bruno Borghesi <franco@akyasociados.com.ar>; +Cc: proghome@silesky.com; pgsql-sql
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 table which
the original doesn't.
The second is that if there were two rows in affiliate_lockout
with the different member_ids but the same affiliate_locked_id
say, (21101, 10) and (21201, 10) and you were matching a lead row
with a affiliate_id of 10, the second row would get past the
condition since it has a member_id!=21101, but the original
would drop the row because there existed some matching
affiliate_lockout row where the member_id was 21101.
> 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
> (P.member_id=M.member_id_to_exclude)
> WHERE
> L.exclusive IS NULL OR
> (
> L.exclusive=0 AND
> L.nb_purchases<3
> ) AND
> P.lead_id<>21101 AND
> A.member_id<>21011
>
> > 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, member_id_to_exclude)
> > purchase (10000 rows) (lead_id, member_id, ...)
> >
> >
> > Here is the query:
> > SELECT *
> > FROM lead
> > WHERE
> > (exclusive IS NULL OR (exclusive = 0 AND nb_purchases < 3)) AND
> >
> > id NOT IN (
> >
> > SELECT lead_id
> > FROM purchase
> > WHERE member_id = 21101
> > ) AND affiliate_id NOT
> > IN (
> >
> > SELECT affiliate_locked_id
> > FROM affiliate_lockout
> > WHERE member_id = 21101
> > ) AND id NOT
> > IN (
> >
> > SELECT lead_id
> > FROM purchase
> > INNER JOIN member_exclusion
> > WHERE purchase.member_id = member_exclusion.member_id_to_exclude AND
> > purchase.member_id = 21101
> > )
> >
> >
> > I wonder the problem is with the subqueries (which are apparently very
> > slow to run, according to what I read), but I can't figure how to
> > rewrite this query without any subquery ...
> >
> > Maybe the problem comes from the index ... How would you create your
> > indexes to optimize this query ?
> >
> > Could somebody help me ?
> > Thanks
> > krystoffff
> >
> > ---------------------------(end of
> > broadcast)--------------------------- TIP 5: Have you checked our
> > extensive FAQ?
> >
> > http://www.postgresql.org/docs/faqs/FAQ.html
>
>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 7: don't forget to increase your free space map settings
>
^ permalink raw reply [nested|flat] 28+ messages in thread
* Re: How to optimize this query ?
@ 2003-08-13 13:20 krystoffff <proghome@silesky.com>
parent: Stephan Szabo <sszabo@megazone.bigpanda.com>
1 sibling, 1 reply; 28+ messages in thread
From: proghome@silesky.com @ 2003-08-13 13:20 UTC (permalink / raw)
To: pgsql-sql
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 ?
-------------------------
SELECT lead. *
FROM lead
LEFT JOIN purchase ON ( lead.id = purchase.lead_id )
LEFT JOIN affiliate_lockout ON ( lead.affiliate_id =
affiliate_lockout.affiliate_locked_id )
WHERE (
exclusive IS NULL OR (
exclusive = 0 AND nb_purchases < 3
)
) AND id NOT
IN (
SELECT lead_id
FROM purchase
INNER JOIN member_exclusion
WHERE purchase.member_id = member_exclusion.member_id_to_exclude AND
purchase.member_id = 21101
) AND (
affiliate_lockout.member_id <> 21101 OR affiliate_lockout.member_id IS
NULL
) AND purchase.member_id <> 21101
GROUP BY lead.id
^ permalink raw reply [nested|flat] 28+ messages in thread
* Re: How to optimize this query ?
@ 2003-08-13 16:17 Franco Bruno Borghesi <franco@akyasociados.com.ar>
parent: Stephan Szabo <sszabo@megazone.bigpanda.com>
1 sibling, 2 replies; 28+ messages in thread
From: Franco Bruno Borghesi @ 2003-08-13 16:17 UTC (permalink / raw)
To: proghome@silesky.com; pgsql-sql
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=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 WHERE
member_id=21101
) A ON (L.affiliate_id=A.affiliated_locled_id)
WHERE
L.exclusive IS NULL OR
(
L.exclusive=0 AND
L.nb_purchases<3
) AND
(P.lead_id IS NULL OR P.lead_id<>21101) AND
(M.member_id IS NULL) AND
(A.member_id IS NULL)
On Tue, 2003-08-12 at 22:29, Stephan Szabo wrote:
> 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 table which
> the original doesn't.
>
> The second is that if there were two rows in affiliate_lockout
> with the different member_ids but the same affiliate_locked_id
> say, (21101, 10) and (21201, 10) and you were matching a lead row
> with a affiliate_id of 10, the second row would get past the
> condition since it has a member_id!=21101, but the original
> would drop the row because there existed some matching
> affiliate_lockout row where the member_id was 21101.
>
> > 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
> > (P.member_id=M.member_id_to_exclude)
> > WHERE
> > L.exclusive IS NULL OR
> > (
> > L.exclusive=0 AND
> > L.nb_purchases<3
> > ) AND
> > P.lead_id<>21101 AND
> > A.member_id<>21011
> >
> > > 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, member_id_to_exclude)
> > > purchase (10000 rows) (lead_id, member_id, ...)
> > >
> > >
> > > Here is the query:
> > > SELECT *
> > > FROM lead
> > > WHERE
> > > (exclusive IS NULL OR (exclusive = 0 AND nb_purchases < 3)) AND
> > >
> > > id NOT IN (
> > >
> > > SELECT lead_id
> > > FROM purchase
> > > WHERE member_id = 21101
> > > ) AND affiliate_id NOT
> > > IN (
> > >
> > > SELECT affiliate_locked_id
> > > FROM affiliate_lockout
> > > WHERE member_id = 21101
> > > ) AND id NOT
> > > IN (
> > >
> > > SELECT lead_id
> > > FROM purchase
> > > INNER JOIN member_exclusion
> > > WHERE purchase.member_id = member_exclusion.member_id_to_exclude AND
> > > purchase.member_id = 21101
> > > )
> > >
> > >
> > > I wonder the problem is with the subqueries (which are apparently very
> > > slow to run, according to what I read), but I can't figure how to
> > > rewrite this query without any subquery ...
> > >
> > > Maybe the problem comes from the index ... How would you create your
> > > indexes to optimize this query ?
> > >
> > > Could somebody help me ?
> > > Thanks
> > > krystoffff
> > >
> > > ---------------------------(end of
> > > broadcast)--------------------------- TIP 5: Have you checked our
> > > extensive FAQ?
> > >
> > > http://www.postgresql.org/docs/faqs/FAQ.html
> >
> >
> >
> >
> > ---------------------------(end of broadcast)---------------------------
> > TIP 7: don't forget to increase your free space map settings
> >
>
>
>
>
>
>
Attachments:
signature.asc (application/pgp-signature, 186B) download
^ permalink raw reply [nested|flat] 28+ messages in thread
* Re: How to optimize this query ?
@ 2003-08-13 17:10 Stephan Szabo <sszabo@megazone.bigpanda.com>
parent: Franco Bruno Borghesi <franco@akyasociados.com.ar>
1 sibling, 2 replies; 28+ messages in thread
From: Stephan Szabo @ 2003-08-13 17:10 UTC (permalink / raw)
To: Franco Bruno Borghesi <franco@akyasociados.com.ar>; +Cc: proghome@silesky.com; pgsql-sql
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 possible
that NOT EXISTS will work better than left joins even in 7.3 and earlier,
I'm not sure, I think it's probably situational.
I think that you're still going to have a problem in the below if there
are purchase rows with member_id 21101 and some other value that both
match. I think you need to do something like the subselect on
affiliate_lockout in the from on purchase as well.
> 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 WHERE
> member_id=21101
> ) A ON (L.affiliate_id=A.affiliated_locled_id)
> WHERE
> L.exclusive IS NULL OR
> (
> L.exclusive=0 AND
> L.nb_purchases<3
> ) AND
> (P.lead_id IS NULL OR P.lead_id<>21101) AND
[I think this was meant to be member_id from the original query]
> (M.member_id IS NULL) AND
> (A.member_id IS NULL)
^ permalink raw reply [nested|flat] 28+ messages in thread
* Re: How to optimize this query ?
@ 2003-08-13 17:58 ProgHome <proghome@silesky.com>
parent: Stephan Szabo <sszabo@megazone.bigpanda.com>
1 sibling, 1 reply; 28+ messages in thread
From: ProgHome @ 2003-08-13 17:58 UTC (permalink / raw)
To: 'Stephan Szabo' <sszabo@megazone.bigpanda.com>; 'Franco Bruno Borghesi' <franco@akyasociados.com.ar>; +Cc: pgsql-sql
Sorry, I posted the following message on the newsgroups, but it seems
that you didn't see it ...
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'd like to remove the last subquery, to see if it faster ;)
-------------------------
SELECT lead. *
FROM lead
LEFT JOIN purchase ON ( lead.id = purchase.lead_id )
LEFT JOIN affiliate_lockout ON ( lead.affiliate_id =
affiliate_lockout.affiliate_locked_id )
WHERE (
exclusive IS NULL OR (
exclusive = 0 AND nb_purchases < 3
)
) AND id NOT
IN (
SELECT lead_id
FROM purchase
INNER JOIN member_exclusion
WHERE purchase.member_id = member_exclusion.member_id_to_exclude AND
purchase.member_id = 21101
) AND (
affiliate_lockout.member_id <> 21101 OR affiliate_lockout.member_id IS
NULL
) AND purchase.member_id <> 21101
GROUP BY lead.id
-----Original Message-----
From: Stephan Szabo [mailto:sszabo@megazone.bigpanda.com]
Sent: Wednesday, August 13, 2003 1:10 PM
To: Franco Bruno Borghesi
Cc: proghome@silesky.com; pgsql-sql@postgresql.org
Subject: Re: [SQL] How to optimize this query ?
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 possible
that NOT EXISTS will work better than left joins even in 7.3 and
earlier,
I'm not sure, I think it's probably situational.
I think that you're still going to have a problem in the below if there
are purchase rows with member_id 21101 and some other value that both
match. I think you need to do something like the subselect on
affiliate_lockout in the from on purchase as well.
> 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
WHERE
> member_id=21101
> ) A ON (L.affiliate_id=A.affiliated_locled_id)
> WHERE
> L.exclusive IS NULL OR
> (
> L.exclusive=0 AND
> L.nb_purchases<3
> ) AND
> (P.lead_id IS NULL OR P.lead_id<>21101) AND
[I think this was meant to be member_id from the original query]
> (M.member_id IS NULL) AND
> (A.member_id IS NULL)
^ permalink raw reply [nested|flat] 28+ messages in thread
* Re: How to optimize this query ?
@ 2003-08-13 18:29 ProgHome <proghome@silesky.com>
parent: Stephan Szabo <sszabo@megazone.bigpanda.com>
1 sibling, 1 reply; 28+ messages in thread
From: ProgHome @ 2003-08-13 18:29 UTC (permalink / raw)
To: 'Stephan Szabo' <sszabo@megazone.bigpanda.com>; 'Franco Bruno Borghesi' <franco@akyasociados.com.ar>; +Cc: pgsql-sql
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, affiliate_id)
automated (4 rows) (member_id, .)
lead (4490 rows) (id, ...)
member (6 rows) (id, ...)
member_exclusion (3 rows) (member_id, member_id_to_exclude)
purchase (10000 rows) (lead_id, member_id, ...)
----------------------------
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
(unix_timestamp(now())-unix_timestamp(date)) <
(unix_timestamp(now())-'$today_midnight') and type='purchase' and
comment LIKE '%automated%'
and member_id=member.id and comment LIKE '%$type%') < max_$field
and balance_in_points > $price
and credit_ratings_t$n LIKE '%$lead[borrower_credit_rating]%'
and states LIKE '%$lead[prop_state]%'
and ltv_t$n/100 >= (cast($lead[loan_amount] as unsigned) /
cast($lead[current_value] as unsigned))
and amount_t$n < $lead[loan_amount]
AND $id NOT IN (select lead_id from purchase where
member_id=member.id)
AND $aff_id NOT IN (select affiliate_locked_id from affiliate_lockout
where member_id=member.id)
AND $id NOT IN (select lead_id from purchase where member_id IN
(select member_id_to_exclude from member_exclusion where
member_id=member.id))
ORDER BY balance_in_points DESC
--------------------
For this one, I really don't know how to remove the 3 subqueries at the
end because the $id and $aff_id are values external to the query, and
there is no table to join .
I tried to remove the subqueries and to rewrite them to 3 small external
queries that I run for each result given by this first query, but I
guess this will be much longer if the tables are big .
What do you think about ?
^ permalink raw reply [nested|flat] 28+ messages in thread
* Re: How to optimize this query ?
@ 2003-08-13 18:29 Stephan Szabo <sszabo@megazone.bigpanda.com>
parent: ProgHome <proghome@silesky.com>
0 siblings, 2 replies; 28+ messages in thread
From: Stephan Szabo @ 2003-08-13 18:29 UTC (permalink / raw)
To: ProgHome <proghome@silesky.com>; +Cc: Franco Bruno Borghesi <franco@akyasociados.com.ar>; pgsql-sql
On Wed, 13 Aug 2003, ProgHome wrote:
> 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'd like to remove the last subquery, to see if it faster ;)
>
>
> -------------------------
>
> SELECT lead. *
> FROM lead
> LEFT JOIN purchase ON ( lead.id = purchase.lead_id )
> LEFT JOIN affiliate_lockout ON ( lead.affiliate_id =
> affiliate_lockout.affiliate_locked_id )
> WHERE (
> exclusive IS NULL OR (
> exclusive = 0 AND nb_purchases < 3
> )
> ) AND id NOT
> IN (
>
> SELECT lead_id
> FROM purchase
> INNER JOIN member_exclusion
> WHERE purchase.member_id = member_exclusion.member_id_to_exclude AND
> purchase.member_id = 21101
> ) AND (
> affiliate_lockout.member_id <> 21101 OR affiliate_lockout.member_id IS
> NULL
> ) AND purchase.member_id <> 21101
> GROUP BY lead.id
As I replied to Franco for his query below, I believe this query is not
equivalent to your original query for a few cases, but those might not
come up.
If you had a row in lead like
id = 2, affiliate_id = 2
And rows in affiliate_lockout like:
affiliate_locked_id=2, member_id=21101
affiliate_locked_id=2, member_id=31101
should this row in lead be shown or not?
In the original query I think it would not (because lead.affiliate_id was
IN the affiliate_lockout table where member_id=21101). In the above query
I think it will, because one of the joined tables will have the lead
information and a member_id that is not equal to 21101.
> -----Original Message-----
> From: Stephan Szabo [mailto:sszabo@megazone.bigpanda.com]
> Sent: Wednesday, August 13, 2003 1:10 PM
> To: Franco Bruno Borghesi
> Cc: proghome@silesky.com; pgsql-sql@postgresql.org
> Subject: Re: [SQL] How to optimize this query ?
>
> 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 possible
> that NOT EXISTS will work better than left joins even in 7.3 and
> earlier,
> I'm not sure, I think it's probably situational.
>
> I think that you're still going to have a problem in the below if there
> are purchase rows with member_id 21101 and some other value that both
> match. I think you need to do something like the subselect on
> affiliate_lockout in the from on purchase as well.
>
> > 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
> WHERE
> > member_id=21101
> > ) A ON (L.affiliate_id=A.affiliated_locled_id)
> > WHERE
> > L.exclusive IS NULL OR
> > (
> > L.exclusive=0 AND
> > L.nb_purchases<3
> > ) AND
> > (P.lead_id IS NULL OR P.lead_id<>21101) AND
>
> [I think this was meant to be member_id from the original query]
>
> > (M.member_id IS NULL) AND
> > (A.member_id IS NULL)
>
>
>
>
>
^ permalink raw reply [nested|flat] 28+ messages in thread
* Re: How to optimize this query ?
@ 2003-08-13 18:39 Stephan Szabo <sszabo@megazone.bigpanda.com>
parent: ProgHome <proghome@silesky.com>
0 siblings, 0 replies; 28+ messages in thread
From: Stephan Szabo @ 2003-08-13 18:39 UTC (permalink / raw)
To: ProgHome <proghome@silesky.com>; +Cc: Franco Bruno Borghesi <franco@akyasociados.com.ar>; pgsql-sql
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
> (unix_timestamp(now())-unix_timestamp(date)) <
> (unix_timestamp(now())-'$today_midnight') and type='purchase' and
> comment LIKE '%automated%'
> and member_id=member.id and comment LIKE '%$type%') < max_$field
> and balance_in_points > $price
> and credit_ratings_t$n LIKE '%$lead[borrower_credit_rating]%'
> and states LIKE '%$lead[prop_state]%'
> and ltv_t$n/100 >= (cast($lead[loan_amount] as unsigned) /
> cast($lead[current_value] as unsigned))
> and amount_t$n < $lead[loan_amount]
>
> AND $id NOT IN (select lead_id from purchase where
> member_id=member.id)
> AND $aff_id NOT IN (select affiliate_locked_id from affiliate_lockout
> where member_id=member.id)
> AND $id NOT IN (select lead_id from purchase where member_id IN
> (select member_id_to_exclude from member_exclusion where
> member_id=member.id))
In these cases I'd suggest trying a NOT EXISTS, maybe something like
(for the first one):
AND NOT EXISTS (select 1 from purchase where member_id=member.id
AND lead_id=$id)
With a two column index on purchase(member_id,lead_id) [or lead_id,
member_id maybe] you might be able to do okay.
^ permalink raw reply [nested|flat] 28+ messages in thread
* Re: How to optimize this query ?
@ 2003-08-13 18:54 krystoffff <proghome@silesky.com>
parent: krystoffff <proghome@silesky.com>
0 siblings, 0 replies; 28+ messages in thread
From: proghome@silesky.com @ 2003-08-13 18:54 UTC (permalink / raw)
To: pgsql-sql
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
WHERE member_id = 21101
) AS A ON ( L.affiliate_id = A.affiliate_locked_id )
WHERE L.exclusive IS NULL OR (
L.exclusive = 0 AND L.nb_purchases < 3
) AND (
P.lead_id IS NULL OR P.lead_id <> 21101
) AND (
M.member_id IS NULL
)
GROUP BY L.id
-----------------------
I've got the same result as before, so it should be correct ;)
By the way, the time seems to be the same (1.41s for the last form,
and 1.44s now) but I think it's because I don't have much stuff in
Member_exclusion (6 rows) so I will keep this query without subquery
...
Thanks, Franco !
PS : definitively, try to avoid the subqueries ! It's easy to program,
but very slow to execute !
^ permalink raw reply [nested|flat] 28+ messages in thread
* Re: How to optimize this query ?
@ 2003-08-14 18:14 krystoffff <proghome@silesky.com>
parent: Stephan Szabo <sszabo@megazone.bigpanda.com>
1 sibling, 0 replies; 28+ messages in thread
From: proghome@silesky.com @ 2003-08-14 18:14 UTC (permalink / raw)
To: pgsql-sql
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 same query is calculated twice
...
Second problem, the most important :
The A.id should be for each result returned in A.*, and there should
be a join to calculate the query "nb_bogus_leads" (for instance) about
the A.id currently processed by the query.
But it seems that this join doesn't work, because I have the same
"nb_bogus_leads" and same "nb_leads_submitted" for each A.id returned
(they should be different !)
How can you make this query work ?
Thanks
SELECT A. * , (
SELECT CAST( count( * ) AS UNSIGNED )
FROM request
INNER JOIN lead ON ( lead_id = lead.id )
WHERE allowed = 1 AND lead.affiliate_id = A.id
) AS nb_bogus_leads, (
SELECT CAST( count( * ) AS UNSIGNED )
FROM lead
WHERE affiliate_id = A.id
) AS nb_leads_submitted, (
CASE WHEN (
SELECT CAST( count( * ) AS UNSIGNED )
FROM lead
WHERE affiliate_id = A.id
) <> 0
THEN (
SELECT CAST( count( * ) AS UNSIGNED )
FROM request
INNER JOIN lead ON ( lead_id = lead.id )
WHERE allowed = 1 AND lead.affiliate_id = A.id
) / (
SELECT CAST( count( * ) AS UNSIGNED )
FROM lead
WHERE affiliate_id = A.id ) * 100
WHEN (
SELECT CAST( count( * ) AS UNSIGNED )
FROM lead
WHERE affiliate_id = A.id
) = 0
THEN 0
END
) AS percentage_bogus_leads
FROM affiliate A
WHERE website = 'dev'
^ permalink raw reply [nested|flat] 28+ messages in thread
* Re: How to optimize this query ?
@ 2003-08-15 20:24 ProgHome <proghome@silesky.com>
parent: Franco Bruno Borghesi <franco@akyasociados.com.ar>
1 sibling, 0 replies; 28+ messages in thread
From: ProgHome @ 2003-08-15 20:24 UTC (permalink / raw)
To: 'Franco Bruno Borghesi' <franco@akyasociados.com.ar>; pgsql-sql
As I am using mysql 4.0 right now (we've got a stupid problem with the
4.1 with the authentification protocol we can't figure out) and the last
subquery (the one in the last LEFT JOIN) MUST be removed .
So I tried the following query:
SELECT
L.*
FROM lead L
LEFT JOIN purchase P1 ON ( L.id = P1.lead_id )
LEFT JOIN affiliate_lockout A ON ( L.affiliate_id =
A.affiliate_locked_id )
LEFT JOIN (
purchase P2
INNER JOIN member_exclusion M ON ( P2.member_id =
M.member_id_to_exclude)
) ON ( L.id = P2.lead_id )
WHERE UNIX_TIMESTAMP( now( ) ) - UNIX_TIMESTAMP( date_creation ) <= ( 6
* 24 * 3600 ) AND (
exclusive IS NULL OR (
exclusive = 0 AND nb_purchases < 3
)
) AND (
A.member_id <> 21101 OR A.member_id IS NULL )
AND ( P1.member_id <> 21101 OR P1.member_id IS NULL )
But it seems that the LEFT JOIN doesn't work anymore and are replaced by
OUTER JOIN because the result of the query is (number of rows in Lead *
number of rows in PURCHASE * number of rows in .)
And it seems that the condition L.id = P2.lead_id doesn't work either .
Could you tell me what the problem is ?
Thanks
-----Original Message-----
From: Franco Bruno Borghesi [mailto:franco@akyasociados.com.ar]
Sent: Wednesday, August 13, 2003 12:18 PM
To: proghome@silesky.com; pgsql-sql@postgresql.org
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=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 WHERE
member_id=21101
) A ON (L.affiliate_id=A.affiliated_locled_id)
WHERE
L.exclusive IS NULL OR
(
L.exclusive=0 AND
L.nb_purchases<3
) AND
(P.lead_id IS NULL OR P.lead_id<>21101) AND
(M.member_id IS NULL) AND
(A.member_id IS NULL)
^ permalink raw reply [nested|flat] 28+ messages in thread
* Re: How to optimize this query ?
@ 2003-08-28 01:01 ProgHome <proghome@silesky.com>
parent: Stephan Szabo <sszabo@megazone.bigpanda.com>
1 sibling, 1 reply; 28+ messages in thread
From: ProgHome @ 2003-08-28 01:01 UTC (permalink / raw)
To: 'Stephan Szabo' <sszabo@megazone.bigpanda.com>; +Cc: 'Franco Bruno Borghesi' <franco@akyasociados.com.ar>; pgsql-sql
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 you have an idea ?
-----Original Message-----
From: Stephan Szabo [mailto:sszabo@megazone.bigpanda.com]
Sent: Wednesday, August 13, 2003 2:29 PM
To: ProgHome
Cc: 'Franco Bruno Borghesi'; pgsql-sql@postgresql.org
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 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'd like to remove the last subquery, to see if it faster ;)
>
>
> -------------------------
>
> SELECT lead. *
> FROM lead
> LEFT JOIN purchase ON ( lead.id = purchase.lead_id )
> LEFT JOIN affiliate_lockout ON ( lead.affiliate_id =
> affiliate_lockout.affiliate_locked_id )
> WHERE (
> exclusive IS NULL OR (
> exclusive = 0 AND nb_purchases < 3
> )
> ) AND id NOT
> IN (
>
> SELECT lead_id
> FROM purchase
> INNER JOIN member_exclusion
> WHERE purchase.member_id = member_exclusion.member_id_to_exclude AND
> purchase.member_id = 21101
> ) AND (
> affiliate_lockout.member_id <> 21101 OR affiliate_lockout.member_id IS
> NULL
> ) AND purchase.member_id <> 21101
> GROUP BY lead.id
As I replied to Franco for his query below, I believe this query is not
equivalent to your original query for a few cases, but those might not
come up.
If you had a row in lead like
id = 2, affiliate_id = 2
And rows in affiliate_lockout like:
affiliate_locked_id=2, member_id=21101
affiliate_locked_id=2, member_id=31101
should this row in lead be shown or not?
In the original query I think it would not (because lead.affiliate_id
was
IN the affiliate_lockout table where member_id=21101). In the above
query
I think it will, because one of the joined tables will have the lead
information and a member_id that is not equal to 21101.
^ permalink raw reply [nested|flat] 28+ messages in thread
* Re: How to optimize this query ?
@ 2003-08-28 01:18 Stephan Szabo <sszabo@megazone.bigpanda.com>
parent: ProgHome <proghome@silesky.com>
0 siblings, 0 replies; 28+ messages in thread
From: Stephan Szabo @ 2003-08-28 01:18 UTC (permalink / raw)
To: ProgHome <proghome@silesky.com>; +Cc: Franco Bruno Borghesi <franco@akyasociados.com.ar>; pgsql-sql
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 you have an idea ?
The only ways I can think of are through a subquery (*) or possibly there
might be a way to do it with EXCEPT, but on first blush that seems like it
might be difficult and probably not any better speed wise.
(*) - You were using IN (subquery) which is known to be poorly optimized
for 7.3 and earlier. You might want to see how it performs on your data
under 7.4beta for forward looking, and/or consider converting into a form
using EXISTS rather than IN.
^ permalink raw reply [nested|flat] 28+ messages in thread
* How to optimize this query?
@ 2006-03-22 17:17 Maciej Piekielniak <piechcio@isb.com.pl>
0 siblings, 1 reply; 28+ messages in thread
From: Maciej Piekielniak @ 2006-03-22 17:17 UTC (permalink / raw)
To: pgsql-sql
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
max,towar.ilosc_jed,towar.ilosc_nom,towar.ilosc_paczkowa,dostawcy.id_dostawcy,jednostka_miary.jednostka,0.0
AS ilosc_magazyn,(select sum(zlecenia_towar.ilosc*zlecenia_elementy.ilosc)
from
zlecenia_towar,zlecenia_elementy,zlecenia
where
zlecenia_towar.id_towar=towar.id_towar and zlecenia_towar.id_zlecenia_elementy=zlecenia_elementy.id_zlecenia_elementy
and zlecenia_elementy.id_zlecenia=zlecenia.id_zlecenia
and (zlecenia.id_paczka in (52,50,53))) as z_zamowien,towar.key1
from (towar LEFT JOIN dostawcy ON (towar.id_dostawcy = dostawcy.id_dostawcy) LEFT JOIN jednostka_miary ON
(towar.id_jednostka_miary = jednostka_miary.id_jednostka_miary)) where towar.policzalne=True group by
towar.id_towar,towar.key2,towar.nazwa,towar.min1,towar.max1,towar.ilosc_jed,towar.ilosc_nom,dostawcy.id_dostawcy
, jednostka_miary.jednostka,towar.ilosc_paczkowa,towar.key1 order by id_dostawcy;
--
Best regards,
Maciej mailto:piechcio@isb.com.pl
^ permalink raw reply [nested|flat] 28+ messages in thread
* Re: How to optimize this query?
@ 2006-03-22 17:58 Markus Bertheau <mbertheau.pg@googlemail.com>
parent: Maciej Piekielniak <piechcio@isb.com.pl>
0 siblings, 1 reply; 28+ messages in thread
From: Markus Bertheau @ 2006-03-22 17:58 UTC (permalink / raw)
To: Maciej Piekielniak <piechcio@isb.com.pl>; +Cc: pgsql-sql
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
2006/3/22, Maciej Piekielniak <piechcio@isb.com.pl>:
> 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
> max,towar.ilosc_jed,towar.ilosc_nom,towar.ilosc_paczkowa,dostawcy.id_dostawcy,jednostka_miary.jednostka,0.0
> AS ilosc_magazyn,(select sum(zlecenia_towar.ilosc*zlecenia_elementy.ilosc)
> from
> zlecenia_towar,zlecenia_elementy,zlecenia
> where
> zlecenia_towar.id_towar=towar.id_towar and zlecenia_towar.id_zlecenia_elementy=zlecenia_elementy.id_zlecenia_elementy
> and zlecenia_elementy.id_zlecenia=zlecenia.id_zlecenia
> and (zlecenia.id_paczka in (52,50,53))) as z_zamowien,towar.key1
> from (towar LEFT JOIN dostawcy ON (towar.id_dostawcy = dostawcy.id_dostawcy) LEFT JOIN jednostka_miary ON
> (towar.id_jednostka_miary = jednostka_miary.id_jednostka_miary)) where towar.policzalne=True group by
> towar.id_towar,towar.key2,towar.nazwa,towar.min1,towar.max1,towar.ilosc_jed,towar.ilosc_nom,dostawcy.id_dostawcy
> , jednostka_miary.jednostka,towar.ilosc_paczkowa,towar.key1 order by id_dostawcy;
>
> --
> Best regards,
> Maciej mailto:piechcio@isb.com.pl
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 4: Have you searched our list archives?
>
> http://archives.postgresql.org
>
^ permalink raw reply [nested|flat] 28+ messages in thread
* Re: How to optimize this query?
@ 2006-03-22 18:18 Maciej Piekielniak <piechcio@isb.com.pl>
parent: Markus Bertheau <mbertheau.pg@googlemail.com>
0 siblings, 1 reply; 28+ messages in thread
From: Maciej Piekielniak @ 2006-03-22 18:18 UTC (permalink / raw)
To: pgsql-sql
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> postgresql.conf that you changed from the defaults would be
MB> interesting.
Sort (cost=21413847.71..21413867.37 rows=7864 width=107)
Sort Key: dostawcy.id_dostawcy
-> Group (cost=1360.03..21413073.50 rows=7864 width=107)
-> Sort (cost=1360.03..1379.69 rows=7864 width=107)
Sort Key: towar.id_towar, towar.key2, towar.nazwa, towar.min1, towar.max1, towar.ilosc_jed, towar.ilosc_nom, dostawcy.id_dostawcy, jednostka_miary.jednostka, towar.ilosc_paczkowa, towar.key1
-> Hash Left Join (cost=2.21..585.81 rows=7864 width=107)
Hash Cond: ("outer".id_jednostka_miary = "inner".id_jednostka_miary)
-> Hash Left Join (cost=1.14..466.78 rows=7864 width=103)
Hash Cond: ("outer".id_dostawcy = "inner".id_dostawcy)
-> Seq Scan on towar (cost=0.00..347.68 rows=7864 width=103)
Filter: policzalne
-> Hash (cost=1.11..1.11 rows=11 width=4)
-> Seq Scan on dostawcy (cost=0.00..1.11 rows=11 width=4)
-> Hash (cost=1.06..1.06 rows=6 width=12)
-> Seq Scan on jednostka_miary (cost=0.00..1.06 rows=6 width=12)
SubPlan
-> Aggregate (cost=2722.71..2722.72 rows=1 width=14)
-> Nested Loop (cost=64.33..2722.28 rows=171 width=14)
-> Hash Join (cost=64.33..602.79 rows=368 width=12)
Hash Cond: ("outer".id_zlecenia = "inner".id_zlecenia)
-> Seq Scan on zlecenia_elementy (cost=0.00..488.85 rows=9185 width=20)
-> Hash (cost=63.98..63.98 rows=140 width=8)
-> Bitmap Heap Scan on zlecenia (cost=6.50..63.98 rows=140 width=8)
Recheck Cond: ((id_paczka = 52) OR (id_paczka = 50) OR (id_paczka = 53))
-> BitmapOr (cost=6.50..6.50 rows=142 width=0)
-> Bitmap Index Scan on zlecenia_id_paczka (cost=0.00..2.17 rows=47 width=0)
Index Cond: (id_paczka = 52)
-> Bitmap Index Scan on zlecenia_id_paczka (cost=0.00..2.17 rows=47 width=0)
Index Cond: (id_paczka = 50)
-> Bitmap Index Scan on zlecenia_id_paczka (cost=0.00..2.17 rows=47 width=0)
Index Cond: (id_paczka = 53)
-> Index Scan using zlezenia_towar_elementy_towar on zlecenia_towar (cost=0.00..5.75 rows=1 width=18)
Index Cond: ((zlecenia_towar.id_zlecenia_elementy = "outer".id_zlecenia_elementy) AND (zlecenia_towar.id_towar = $0))
(33 rows)
--
Best regards,
Maciej mailto:piechcio@isb.com.pl
^ permalink raw reply [nested|flat] 28+ messages in thread
* Re: How to optimize this query?
@ 2006-03-22 18:27 Markus Bertheau <mbertheau.pg@googlemail.com>
parent: Maciej Piekielniak <piechcio@isb.com.pl>
0 siblings, 1 reply; 28+ messages in thread
From: Markus Bertheau @ 2006-03-22 18:27 UTC (permalink / raw)
To: Maciej Piekielniak <piechcio@isb.com.pl>; +Cc: pgsql-sql
That's an explain. We need explain analyze.
2006/3/23, Maciej Piekielniak <piechcio@isb.com.pl>:
> 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> postgresql.conf that you changed from the defaults would be
> MB> interesting.
>
>
> Sort (cost=21413847.71..21413867.37 rows=7864 width=107)
> Sort Key: dostawcy.id_dostawcy
> -> Group (cost=1360.03..21413073.50 rows=7864 width=107)
> -> Sort (cost=1360.03..1379.69 rows=7864 width=107)
> Sort Key: towar.id_towar, towar.key2, towar.nazwa, towar.min1, towar.max1, towar.ilosc_jed, towar.ilosc_nom, dostawcy.id_dostawcy, jednostka_miary.jednostka, towar.ilosc_paczkowa, towar.key1
> -> Hash Left Join (cost=2.21..585.81 rows=7864 width=107)
> Hash Cond: ("outer".id_jednostka_miary = "inner".id_jednostka_miary)
> -> Hash Left Join (cost=1.14..466.78 rows=7864 width=103)
> Hash Cond: ("outer".id_dostawcy = "inner".id_dostawcy)
> -> Seq Scan on towar (cost=0.00..347.68 rows=7864 width=103)
> Filter: policzalne
> -> Hash (cost=1.11..1.11 rows=11 width=4)
> -> Seq Scan on dostawcy (cost=0.00..1.11 rows=11 width=4)
> -> Hash (cost=1.06..1.06 rows=6 width=12)
> -> Seq Scan on jednostka_miary (cost=0.00..1.06 rows=6 width=12)
> SubPlan
> -> Aggregate (cost=2722.71..2722.72 rows=1 width=14)
> -> Nested Loop (cost=64.33..2722.28 rows=171 width=14)
> -> Hash Join (cost=64.33..602.79 rows=368 width=12)
> Hash Cond: ("outer".id_zlecenia = "inner".id_zlecenia)
> -> Seq Scan on zlecenia_elementy (cost=0.00..488.85 rows=9185 width=20)
> -> Hash (cost=63.98..63.98 rows=140 width=8)
> -> Bitmap Heap Scan on zlecenia (cost=6.50..63.98 rows=140 width=8)
> Recheck Cond: ((id_paczka = 52) OR (id_paczka = 50) OR (id_paczka = 53))
> -> BitmapOr (cost=6.50..6.50 rows=142 width=0)
> -> Bitmap Index Scan on zlecenia_id_paczka (cost=0.00..2.17 rows=47 width=0)
> Index Cond: (id_paczka = 52)
> -> Bitmap Index Scan on zlecenia_id_paczka (cost=0.00..2.17 rows=47 width=0)
> Index Cond: (id_paczka = 50)
> -> Bitmap Index Scan on zlecenia_id_paczka (cost=0.00..2.17 rows=47 width=0)
> Index Cond: (id_paczka = 53)
> -> Index Scan using zlezenia_towar_elementy_towar on zlecenia_towar (cost=0.00..5.75 rows=1 width=18)
> Index Cond: ((zlecenia_towar.id_zlecenia_elementy = "outer".id_zlecenia_elementy) AND (zlecenia_towar.id_towar = $0))
> (33 rows)
>
> --
> Best regards,
> Maciej mailto:piechcio@isb.com.pl
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 3: Have you checked our extensive FAQ?
>
> http://www.postgresql.org/docs/faq
>
^ permalink raw reply [nested|flat] 28+ messages in thread
* Re: How to optimize this query?
@ 2006-03-22 18:32 Markus Bertheau <mbertheau.pg@googlemail.com>
parent: Markus Bertheau <mbertheau.pg@googlemail.com>
0 siblings, 4 replies; 28+ messages in thread
From: Markus Bertheau @ 2006-03-22 18:32 UTC (permalink / raw)
To: Maciej Piekielniak <piechcio@isb.com.pl>; +Cc: pgsql-sql
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 ilosc_magazyn,
foo.z_zamowien,
towar.key1
FROM
towar
JOIN (
SELECT
zlecenia_towar.id_towar,
SUM(zlecenia_towar.ilosc * zlecenia_elementy.ilosc) as z_zamowien
FROM
zlecenia_towar,
zlecenia_elementy,
zlecenia
WHERE
zlecenia_towar.id_zlecenia_elementy = zlecenia_elementy.id_zlecenia_elementy
AND zlecenia_elementy.id_zlecenia = zlecenia.id_zlecenia
AND zlecenia.id_paczka IN (52,50,53)
GROUP BY zlecenia_towar.id_towar
) AS foo ON (foo.id_towar = towar.id_towar)
LEFT JOIN dostawcy
ON (towar.id_dostawcy = dostawcy.id_dostawcy)
LEFT JOIN jednostka_miary
ON (towar.id_jednostka_miary = jednostka_miary.id_jednostka_miary)
WHERE
towar.policzalne = True
GROUP BY
towar.id_towar,
towar.key2,
towar.nazwa,
towar.min1,
towar.max1,
towar.ilosc_jed,
towar.ilosc_nom,
dostawcy.id_dostawcy,
jednostka_miary.jednostka,
towar.ilosc_paczkowa,
towar.key1
ORDER BY
id_dostawcy;
I basically pulled the subselect from the field list into the from list.
2006/3/23, Markus Bertheau <mbertheau.pg@googlemail.com>:
> That's an explain. We need explain analyze.
>
> 2006/3/23, Maciej Piekielniak <piechcio@isb.com.pl>:
> > 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> postgresql.conf that you changed from the defaults would be
> > MB> interesting.
> >
> >
> > Sort (cost=21413847.71..21413867.37 rows=7864 width=107)
> > Sort Key: dostawcy.id_dostawcy
> > -> Group (cost=1360.03..21413073.50 rows=7864 width=107)
> > -> Sort (cost=1360.03..1379.69 rows=7864 width=107)
> > Sort Key: towar.id_towar, towar.key2, towar.nazwa, towar.min1, towar.max1, towar.ilosc_jed, towar.ilosc_nom, dostawcy.id_dostawcy, jednostka_miary.jednostka, towar.ilosc_paczkowa, towar.key1
> > -> Hash Left Join (cost=2.21..585.81 rows=7864 width=107)
> > Hash Cond: ("outer".id_jednostka_miary = "inner".id_jednostka_miary)
> > -> Hash Left Join (cost=1.14..466.78 rows=7864 width=103)
> > Hash Cond: ("outer".id_dostawcy = "inner".id_dostawcy)
> > -> Seq Scan on towar (cost=0.00..347.68 rows=7864 width=103)
> > Filter: policzalne
> > -> Hash (cost=1.11..1.11 rows=11 width=4)
> > -> Seq Scan on dostawcy (cost=0.00..1.11 rows=11 width=4)
> > -> Hash (cost=1.06..1.06 rows=6 width=12)
> > -> Seq Scan on jednostka_miary (cost=0.00..1.06 rows=6 width=12)
> > SubPlan
> > -> Aggregate (cost=2722.71..2722.72 rows=1 width=14)
> > -> Nested Loop (cost=64.33..2722.28 rows=171 width=14)
> > -> Hash Join (cost=64.33..602.79 rows=368 width=12)
> > Hash Cond: ("outer".id_zlecenia = "inner".id_zlecenia)
> > -> Seq Scan on zlecenia_elementy (cost=0.00..488.85 rows=9185 width=20)
> > -> Hash (cost=63.98..63.98 rows=140 width=8)
> > -> Bitmap Heap Scan on zlecenia (cost=6.50..63.98 rows=140 width=8)
> > Recheck Cond: ((id_paczka = 52) OR (id_paczka = 50) OR (id_paczka = 53))
> > -> BitmapOr (cost=6.50..6.50 rows=142 width=0)
> > -> Bitmap Index Scan on zlecenia_id_paczka (cost=0.00..2.17 rows=47 width=0)
> > Index Cond: (id_paczka = 52)
> > -> Bitmap Index Scan on zlecenia_id_paczka (cost=0.00..2.17 rows=47 width=0)
> > Index Cond: (id_paczka = 50)
> > -> Bitmap Index Scan on zlecenia_id_paczka (cost=0.00..2.17 rows=47 width=0)
> > Index Cond: (id_paczka = 53)
> > -> Index Scan using zlezenia_towar_elementy_towar on zlecenia_towar (cost=0.00..5.75 rows=1 width=18)
> > Index Cond: ((zlecenia_towar.id_zlecenia_elementy = "outer".id_zlecenia_elementy) AND (zlecenia_towar.id_towar = $0))
> > (33 rows)
> >
> > --
> > Best regards,
> > Maciej mailto:piechcio@isb.com.pl
> >
> >
> > ---------------------------(end of broadcast)---------------------------
> > TIP 3: Have you checked our extensive FAQ?
> >
> > http://www.postgresql.org/docs/faq
> >
>
^ permalink raw reply [nested|flat] 28+ messages in thread
* Re: How to optimize this query?
@ 2006-03-22 18:45 Maciej Piekielniak <piechcio@isb.com.pl>
parent: Markus Bertheau <mbertheau.pg@googlemail.com>
3 siblings, 0 replies; 28+ messages in thread
From: Maciej Piekielniak @ 2006-03-22 18:45 UTC (permalink / raw)
To: pgsql-sql
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,
Maciej mailto:piechcio@isb.com.pl
^ permalink raw reply [nested|flat] 28+ messages in thread
* Re: How to optimize this query?
@ 2006-03-22 18:52 Maciej Piekielniak <piechcio@isb.com.pl>
parent: Markus Bertheau <mbertheau.pg@googlemail.com>
3 siblings, 0 replies; 28+ messages in thread
From: Maciej Piekielniak @ 2006-03-22 18:52 UTC (permalink / raw)
To: pgsql-sql
Hello Markus,
ERROR: column "foo.z_zamowien" must appear in the GROUP BY clause or be used in an aggregate function
--
Best regards,
Maciej mailto:piechcio@isb.com.pl
^ permalink raw reply [nested|flat] 28+ messages in thread
* Re: How to optimize this query?
@ 2006-03-22 18:54 Maciej Piekielniak <piechcio@isb.com.pl>
parent: Markus Bertheau <mbertheau.pg@googlemail.com>
3 siblings, 0 replies; 28+ messages in thread
From: Maciej Piekielniak @ 2006-03-22 18:54 UTC (permalink / raw)
To: pgsql-sql
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 AS ilosc_magazyn,
foo.z_zamowien,
towar.key1
FROM
towar
JOIN (
SELECT
zlecenia_towar.id_towar,
SUM(zlecenia_towar.ilosc * zlecenia_elementy.ilosc) as z_zamowien
FROM
zlecenia_towar,
zlecenia_elementy,
zlecenia
WHERE
zlecenia_towar.id_zlecenia_elementy = zlecenia_elementy.id_zlecenia_elementy
AND zlecenia_elementy.id_zlecenia = zlecenia.id_zlecenia
AND zlecenia.id_paczka IN (52,50,53)
GROUP BY zlecenia_towar.id_towar
) AS foo ON (foo.id_towar = towar.id_towar)
LEFT JOIN dostawcy
ON (towar.id_dostawcy = dostawcy.id_dostawcy)
LEFT JOIN jednostka_miary
ON (towar.id_jednostka_miary = jednostka_miary.id_jednostka_miary)
WHERE
towar.policzalne = True
GROUP BY
towar.id_towar,
towar.key2,
towar.nazwa,
towar.min1,
towar.max1,
towar.ilosc_jed,
towar.ilosc_nom,
dostawcy.id_dostawcy,
jednostka_miary.jednostka,
towar.ilosc_paczkowa,
towar.key1,
foo.z_zamowien
ORDER BY
id_dostawcy;
--
Best regards,
Maciej mailto:piechcio@isb.com.pl
^ permalink raw reply [nested|flat] 28+ messages in thread
* Re: How to optimize this query?
@ 2006-03-22 19:07 Maciej Piekielniak <piechcio@isb.com.pl>
parent: Markus Bertheau <mbertheau.pg@googlemail.com>
3 siblings, 2 replies; 28+ messages in thread
From: Maciej Piekielniak @ 2006-03-22 19:07 UTC (permalink / raw)
To: pgsql-sql
Hello Markus,
Oryginal query return 7881 rows , your query only 729 rows.
--
Best regards,
Maciej mailto:piechcio@isb.com.pl
^ permalink raw reply [nested|flat] 28+ messages in thread
* Re: How to optimize this query?
@ 2006-03-22 19:19 Jeffrey Melloy <jmelloy@visualdistortion.org>
parent: Maciej Piekielniak <piechcio@isb.com.pl>
1 sibling, 0 replies; 28+ messages in thread
From: Jeffrey Melloy @ 2006-03-22 19:19 UTC (permalink / raw)
To: Maciej Piekielniak <piechcio@isb.com.pl>; +Cc: pgsql-sql
Maciej Piekielniak wrote:
>Hello Markus,
>
>Oryginal query return 7881 rows , your query only 729 rows.
>
>
>
But it's faster!
^ permalink raw reply [nested|flat] 28+ messages in thread
* Re: How to optimize this query?
@ 2006-03-22 19:25 Maciej Piekielniak <piechcio@isb.com.pl>
parent: Maciej Piekielniak <piechcio@isb.com.pl>
1 sibling, 1 reply; 28+ messages in thread
From: Maciej Piekielniak @ 2006-03-22 19:25 UTC (permalink / raw)
To: pgsql-sql
Hello Markus,
Wednesday, March 22, 2006, 8:12:35 PM, you wrote:
MB> Well, send the table descriptions, please.
\d towar
max3 | smallint | default 0
max4 | smallint | default 0
typik | character varying(1) | default '_'::character varying
id_grupa_rabatowa | integer | not null default 0
id_jednostka_miary | integer | not null default 0
id_vat | integer | not null default 0
id_typ_towaru | integer | not null default 0
id_dostawcy | integer | not null default 0
grupa_produkcji | smallint |
dodatek | boolean | not null default false
policzalne | boolean | not null default true
simport | character varying(50) |
czy_procent | boolean | not null default false
subtyp | character varying(35) |
kontofk | character varying(40) |
typks | character varying(30) |
nazwarodzaju | character varying(50) |
nazwakatalogu | character varying(250) |
waluta | character varying(3) | not null default 'PLN'::character varying
bank | character varying(5) | not null default 'NBP'::character varying
procent_do_wyceny | smallint | not null default 0
waga | numeric(24,4) | not null default 0
cena_z | numeric(24,4) | not null default 0
ilosc_paczkowa | numeric(24,4) | not null default 0
ilosc_jed | numeric(24,4) | not null default 1
ilosc_nom | numeric(24,4) | not null default 1
odpad | numeric(24,4) | not null default 0
cena_jedn | numeric(24,4) | not null default 0
roboczojednostka | numeric(24,4) | not null default 0
Indexes:
"towar_pkey" PRIMARY KEY, btree (id_towar)
"towar_key1" btree (key1)
"towar_key2" btree (key2)
Foreign-key constraints:
"$1" FOREIGN KEY (id_grupa_rabatowa) REFERENCES grupa_rabatowa(id_grupa_rabatowa) ON UPDATE CASCADE ON DELETE SET NULL
"$2" FOREIGN KEY (id_jednostka_miary) REFERENCES jednostka_miary(id_jednostka_miary) ON UPDATE CASCADE ON DELETE SET NULL
"$3" FOREIGN KEY (id_vat) REFERENCES vat(id_vat) ON UPDATE CASCADE ON DELETE SET NULL
"$4" FOREIGN KEY (id_typ_towaru) REFERENCES typ_towaru(id_typ_towaru) ON UPDATE CASCADE ON DELETE SET NULL
"$5" FOREIGN KEY (id_dostawcy) REFERENCES dostawcy(id_dostawcy) ON UPDATE CASCADE ON DELETE SET NULL
Triggers:
towar_domyslne BEFORE INSERT ON towar FOR EACH ROW EXECUTE PROCEDURE domyslne_ustawienia()
zmiana_wagi AFTER UPDATE ON towar FOR EACH ROW EXECUTE PROCEDURE waga_przelicz()
--
Best regards,
Maciej mailto:piechcio@isb.com.pl
^ permalink raw reply [nested|flat] 28+ messages in thread
* Re: How to optimize this query?
@ 2006-03-22 20:01 Maciej Piekielniak <piechcio@isb.com.pl>
parent: Maciej Piekielniak <piechcio@isb.com.pl>
0 siblings, 0 replies; 28+ messages in thread
From: Maciej Piekielniak @ 2006-03-22 20:01 UTC (permalink / raw)
To: pgsql-sql
Hello Markus,
Wednesday, March 22, 2006, 8:35:33 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>postgresql.conf that you changed from the defaults would be
MB>interesting.
Celeron 1200 Tualatin 256kb cache
HD 200GB 7200
512 SDRAM
Postgresql 8.1.3 on debian sarge with standard settings
No other running applications.
EXPLAIN ANALYZE
"Sort (cost=21413847.71..21413867.37 rows=7864 width=107) (actual time=615902.463..615933.049 rows=7881 loops=1)"
" Sort Key: dostawcy.id_dostawcy"
" -> Group (cost=1360.03..21413073.50 rows=7864 width=107) (actual time=473.511..615628.474 rows=7881 loops=1)"
" -> Sort (cost=1360.03..1379.69 rows=7864 width=107) (actual time=324.260..407.732 rows=7881 loops=1)"
" Sort Key: towar.id_towar, towar.key2, towar.nazwa, towar.min1, towar.max1, towar.ilosc_jed, towar.ilosc_nom, dostawcy.id_dostawcy, jednostka_miary.jednostka, towar.ilosc_paczkowa, towar.key1"
" -> Hash Left Join (cost=2.21..585.81 rows=7864 width=107) (actual time=0.607..178.794 rows=7881 loops=1)"
" Hash Cond: ("outer".id_jednostka_miary = "inner".id_jednostka_miary)"
" -> Hash Left Join (cost=1.14..466.78 rows=7864 width=103) (actual time=0.397..121.835 rows=7881 loops=1)"
" Hash Cond: ("outer".id_dostawcy = "inner".id_dostawcy)"
" -> Seq Scan on towar (cost=0.00..347.68 rows=7864 width=103) (actual time=0.160..60.079 rows=7881 loops=1)"
" Filter: policzalne"
" -> Hash (cost=1.11..1.11 rows=11 width=4) (actual time=0.185..0.185 rows=11 loops=1)"
" -> Seq Scan on dostawcy (cost=0.00..1.11 rows=11 width=4) (actual time=0.085..0.126 rows=11 loops=1)"
" -> Hash (cost=1.06..1.06 rows=6 width=12) (actual time=0.173..0.173 rows=6 loops=1)"
" -> Seq Scan on jednostka_miary (cost=0.00..1.06 rows=6 width=12) (actual time=0.117..0.140 rows=6 loops=1)"
" SubPlan"
" -> Aggregate (cost=2722.71..2722.72 rows=1 width=14) (actual time=78.006..78.010 rows=1 loops=7881)"
" -> Nested Loop (cost=64.33..2722.28 rows=171 width=14) (actual time=73.991..77.930 rows=6 loops=7881)"
" -> Hash Join (cost=64.33..602.79 rows=368 width=12) (actual time=3.098..64.518 rows=627 loops=7881)"
" Hash Cond: ("outer".id_zlecenia = "inner".id_zlecenia)"
" -> Seq Scan on zlecenia_elementy (cost=0.00..488.85 rows=9185 width=20) (actual time=0.009..32.216 rows=9185 loops=7881)"
" -> Hash (cost=63.98..63.98 rows=140 width=8) (actual time=4.849..4.849 rows=195 loops=1)"
" -> Bitmap Heap Scan on zlecenia (cost=6.50..63.98 rows=140 width=8) (actual time=0.721..3.772 rows=195 loops=1)"
" Recheck Cond: ((id_paczka = 52) OR (id_paczka = 50) OR (id_paczka = 53))"
" -> BitmapOr (cost=6.50..6.50 rows=142 width=0) (actual time=0.549..0.549 rows=0 loops=1)"
" -> Bitmap Index Scan on zlecenia_id_paczka (cost=0.00..2.17 rows=47 width=0) (actual time=0.427..0.427 rows=73 loops=1)"
" Index Cond: (id_paczka = 52)"
" -> Bitmap Index Scan on zlecenia_id_paczka (cost=0.00..2.17 rows=47 width=0) (actual time=0.059..0.059 rows=49 loops=1)"
" Index Cond: (id_paczka = 50)"
" -> Bitmap Index Scan on zlecenia_id_paczka (cost=0.00..2.17 rows=47 width=0) (actual time=0.039..0.039 rows=73 loops=1)"
" Index Cond: (id_paczka = 53)"
" -> Index Scan using zlezenia_towar_elementy_towar on zlecenia_towar (cost=0.00..5.75 rows=1 width=18) (actual time=0.015..0.015 rows=0 loops=4941387)"
" Index Cond: ((zlecenia_towar.id_zlecenia_elementy = "outer".id_zlecenia_elementy) AND (zlecenia_towar.id_towar = $0))"
"Total runtime: 615962.759 ms"
\d towar
max3 | smallint | default 0
max4 | smallint | default 0
typik | character varying(1) | default '_'::character varying
id_grupa_rabatowa | integer | not null default 0
id_jednostka_miary | integer | not null default 0
id_vat | integer | not null default 0
id_typ_towaru | integer | not null default 0
id_dostawcy | integer | not null default 0
grupa_produkcji | smallint |
dodatek | boolean | not null default false
policzalne | boolean | not null default true
simport | character varying(50) |
czy_procent | boolean | not null default false
subtyp | character varying(35) |
kontofk | character varying(40) |
typks | character varying(30) |
nazwarodzaju | character varying(50) |
nazwakatalogu | character varying(250) |
waluta | character varying(3) | not null default 'PLN'::character varying
bank | character varying(5) | not null default 'NBP'::character varying
procent_do_wyceny | smallint | not null default 0
waga | numeric(24,4) | not null default 0
cena_z | numeric(24,4) | not null default 0
ilosc_paczkowa | numeric(24,4) | not null default 0
ilosc_jed | numeric(24,4) | not null default 1
ilosc_nom | numeric(24,4) | not null default 1
odpad | numeric(24,4) | not null default 0
cena_jedn | numeric(24,4) | not null default 0
roboczojednostka | numeric(24,4) | not null default 0
Indexes:
"towar_pkey" PRIMARY KEY, btree (id_towar)
"towar_key1" btree (key1)
"towar_key2" btree (key2)
Foreign-key constraints:
"$1" FOREIGN KEY (id_grupa_rabatowa) REFERENCES grupa_rabatowa(id_grupa_rabatowa) ON UPDATE CASCADE ON DELETE SET NULL
"$2" FOREIGN KEY (id_jednostka_miary) REFERENCES jednostka_miary(id_jednostka_miary) ON UPDATE CASCADE ON DELETE SET NULL
"$3" FOREIGN KEY (id_vat) REFERENCES vat(id_vat) ON UPDATE CASCADE ON DELETE SET NULL
"$4" FOREIGN KEY (id_typ_towaru) REFERENCES typ_towaru(id_typ_towaru) ON UPDATE CASCADE ON DELETE SET NULL
"$5" FOREIGN KEY (id_dostawcy) REFERENCES dostawcy(id_dostawcy) ON UPDATE CASCADE ON DELETE SET NULL
Triggers:
towar_domyslne BEFORE INSERT ON towar FOR EACH ROW EXECUTE PROCEDURE domyslne_ustawienia()
zmiana_wagi AFTER UPDATE ON towar FOR EACH ROW EXECUTE PROCEDURE waga_przelicz()
\d zlecenia_towar
Table "public.zlecenia_towar"
Column | Type | Modifiers
----------------------+-----------------------+----------------------------------------------------------------------------
id_zlecenia_towar | bigint | not null default nextval('zlecenia_towar_id_zlecenia_towar_seq'::regclass)
id_zlecenia_elementy | bigint |
id_towar | bigint |
serwer | smallint |
gdzie | character varying(1) | not null default 'p'::character varying
opismf | character varying(30) |
waga | numeric(24,4) | not null default 0
sprzedaz_c | numeric(24,4) | not null default 0
zakup_c | numeric(24,4) | not null default 0
ilosc | numeric(48,4) | not null default 0
wysokosc | numeric(48,4) | not null default 0
szerokosc | numeric(48,4) | not null default 0
realizacja | numeric(48,4) | not null default 0
Indexes:
"zlecenia_towar_pkey" PRIMARY KEY, btree (id_zlecenia_towar)
"zlecenia_towar_id_towar" btree (id_towar)
"zlecenia_towar_id_zlecenia_elementy" btree (id_zlecenia_elementy)
"zlecenia_towar_serwer" btree (serwer)
"zlezenia_towar_elementy_towar" btree (id_zlecenia_elementy, id_towar)
Foreign-key constraints:
"$1" FOREIGN KEY (id_zlecenia_elementy) REFERENCES zlecenia_elementy(id_zlecenia_elementy) ON UPDATE CASCADE ON DELETE CASCADE
"$2" FOREIGN KEY (id_towar) REFERENCES towar(id_towar) ON UPDATE CASCADE ON DELETE CASCADE
Triggers:
insert_waga_elementu BEFORE INSERT ON zlecenia_towar FOR EACH ROW EXECUTE PROCEDURE waga_wstaw()
zmiana_waga_elementy AFTER INSERT OR UPDATE ON zlecenia_towar FOR EACH ROW EXECUTE PROCEDURE waga_elementy()
\d zlecenia_elementy
vat | smallint | default 0
serwer | smallint |
wykonane_okna | smallint | not null default 0
ksiegowosc_okna | smallint | not null default 0
figura | character varying(50) |
parametr | character varying(50) |
tx1 | character varying(50) |
tx2 | character varying(50) |
tx3 | character varying(50) |
opis | character varying(255) |
datap | date |
zmiana | smallint | not null default 0
linia | smallint | not null default 0
sz_szwiatlo | numeric(24,4) |
wy_szwiatlo | numeric(24,4) |
sz | numeric(24,4) |
wy | numeric(24,4) |
wartosc_netto_mat | numeric(24,4) | not null default 0
wartosc_netto_mat_jed | numeric(24,4) | not null default 0
waga_all | numeric(24,4) | not null default 0
waga_one | numeric(24,4) | not null default 0
metry_one | numeric(24,4) | not null default 0
metry_all | numeric(24,4) | not null default 0
metryb_one | numeric(24,4) | not null default 0
metryb_all | numeric(24,4) | not null default 0
cena | numeric(48,4) |
cena_netto | numeric(48,4) |
sprzedaz | numeric(48,4) | default 0
zakuppr | numeric(48,4) | not null default 0
Indexes:
"zlecenia_elementy_pkey" PRIMARY KEY, btree (id_zlecenia_elementy)
"zlecenia_elementy_data_p" btree (datap)
"zlecenia_elementy_id_zlecenia" btree (id_zlecenia)
"zlecenia_elementy_nr_w_zleceniu" btree (nr_w_zleceniu)
"zlecenia_elementy_serwer" btree (serwer)
Foreign-key constraints:
"$1" FOREIGN KEY (id_zlecenia) REFERENCES zlecenia(id_zlecenia) ON UPDATE CASCADE ON DELETE CASCADE
Triggers:
ilosc_okien_w_zleceniu AFTER INSERT ON zlecenia_elementy FOR EACH ROW EXECUTE PROCEDURE policz_okna_w_zleceniu()
ilosc_zrobionych_okien_w_zleceniu AFTER UPDATE ON zlecenia_elementy FOR EACH ROW EXECUTE PROCEDURE policz_zrobione_okna_w
_zleceniu()
insert_metry_elementu BEFORE INSERT ON zlecenia_elementy FOR EACH ROW EXECUTE PROCEDURE metry_wstaw()
\d zlecenia
linia | smallint | not null default 0
status | integer | not null default 0
Indexes:
"zlecenia_pkey" PRIMARY KEY, btree (id_zlecenia)
"zlecenia_data" btree (data)
"zlecenia_data_p" btree (data, id_paczka)
"zlecenia_data_pt" btree (data, id_paczka_tir)
"zlecenia_data_zam" btree (data_zam)
"zlecenia_data_zam_p" btree (data_zam, id_paczka)
"zlecenia_data_zam_pt" btree (data_zam, id_paczka_tir)
"zlecenia_id_firmy" btree (id_firmy)
"zlecenia_id_paczka" btree (id_paczka)
"zlecenia_id_paczka_tir" btree (id_paczka_tir)
"zlecenia_ksiegowosc" btree (ksiegowosc)
"zlecenia_ksiegowosc_p" btree (ksiegowosc, id_paczka)
"zlecenia_ksiegowosc_pt" btree (ksiegowosc, id_paczka_tir)
"zlecenia_ok" btree (ok)
"zlecenia_ok_p" btree (ok, id_paczka)
"zlecenia_ok_pt" btree (ok, id_paczka_tir)
"zlecenia_proforma" btree (proforma)
"zlecenia_proforma_p" btree (proforma, id_paczka)
"zlecenia_proforma_pt" btree (proforma, id_paczka_tir)
"zlecenia_serwer" btree (serwer)
"zlecenia_zamkniete" btree (zamkniete)
"zlecenia_zamkniete_czas" btree (zamkniete_czas)
"zlecenia_zamkniete_czas_p" btree (zamkniete_czas, id_paczka)
"zlecenia_zamkniete_czas_pt" btree (zamkniete_czas, id_paczka_tir)
"zlecenia_zamkniete_data" btree (zamkniete_data)
"zlecenia_zamkniete_data_p" btree (zamkniete_data, id_paczka)
"zlecenia_zamkniete_data_pt" btree (zamkniete_data, id_paczka_tir)
"zlecenia_zamkniete_p" btree (zamkniete, id_paczka)
"zlecenia_zamkniete_pt" btree (zamkniete, id_paczka_tir)
"zlecenia_zamowienie" btree (zamowienie)
"zlecenia_zamowienie_p" btree (zamowienie, id_paczka)
"zlecenia_zamowienie_pt" btree (zamowienie, id_paczka_tir)
Foreign-key constraints:
"$1" FOREIGN KEY (id_firmy) REFERENCES firmy(id_firmy) ON UPDATE CASCADE ON DELETE SET NULL
"$2" FOREIGN KEY (id_paczka) REFERENCES paczka(id_paczka) ON UPDATE CASCADE ON DELETE SET NULL
"$3" FOREIGN KEY (id_paczka_tir) REFERENCES paczka_tir(id_paczka_tir) ON UPDATE CASCADE ON DELETE SET NULL
Triggers:
ststus_zlecenia BEFORE UPDATE ON zlecenia FOR EACH ROW EXECUTE PROCEDURE test_ststusu_zlecenia()
synchronizacja_kontrachenta AFTER INSERT OR UPDATE ON zlecenia FOR EACH ROW EXECUTE PROCEDURE synchrinizacja_firm()
\d dostawcy
Table "public.dostawcy"
Column | Type | Modifiers
-------------+------------------------+----------------------------------------------------------------
id_dostawcy | integer | not null default nextval('dostawcy_id_dostawcy_seq'::regclass)
code | integer |
skrot | character varying(50) |
nazwa | character varying(50) |
nip | character varying(20) |
adres | character varying(50) |
miasto | character varying(30) |
kod | character varying(6) |
woj | character varying(20) |
panstwo | character varying(20) |
telefon | character varying(15) |
mobile | character varying(15) |
fax | character varying(15) |
email | character varying(50) |
bank | character varying(50) |
konto | character varying(100) |
regon | character varying(20) |
kk | character varying(50) |
Indexes:
"dostawcy_pkey" PRIMARY KEY, btree (id_dostawcy)
\d jednostka_miary
Table "public.jednostka_miary"
Column | Type | Modifiers
--------------------+-----------------------+------------------------------------------------------------------------------
id_jednostka_miary | integer | not null default nextval('jednostka_miary_id_jednostka_miary_seq'::regclass)
jednostka | character varying(4) | not null
opis | character varying(20) | not null
Indexes:
"jednostka_miary_pkey" PRIMARY KEY, btree (id_jednostka_miary)
--
Best regards,
Maciej mailto:piechcio@isb.com.pl
^ permalink raw reply [nested|flat] 28+ messages in thread
end of thread, other threads:[~2006-03-22 20:01 UTC | newest]
Thread overview: 28+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2003-08-12 18:21 How to optimize this query ? krystoffff <proghome@silesky.com>
2003-08-13 00:52 ` Stephan Szabo <sszabo@megazone.bigpanda.com>
2003-08-13 01:04 ` Franco Bruno Borghesi <franco@akyasociados.com.ar>
2003-08-13 01:29 ` Stephan Szabo <sszabo@megazone.bigpanda.com>
2003-08-13 13:20 ` krystoffff <proghome@silesky.com>
2003-08-13 18:54 ` krystoffff <proghome@silesky.com>
2003-08-13 16:17 ` Franco Bruno Borghesi <franco@akyasociados.com.ar>
2003-08-13 17:10 ` Stephan Szabo <sszabo@megazone.bigpanda.com>
2003-08-13 17:58 ` ProgHome <proghome@silesky.com>
2003-08-13 18:29 ` Stephan Szabo <sszabo@megazone.bigpanda.com>
2003-08-14 18:14 ` krystoffff <proghome@silesky.com>
2003-08-28 01:01 ` ProgHome <proghome@silesky.com>
2003-08-28 01:18 ` Stephan Szabo <sszabo@megazone.bigpanda.com>
2003-08-13 18:29 ` ProgHome <proghome@silesky.com>
2003-08-13 18:39 ` Stephan Szabo <sszabo@megazone.bigpanda.com>
2003-08-15 20:24 ` ProgHome <proghome@silesky.com>
2006-03-22 17:17 How to optimize this query? Maciej Piekielniak <piechcio@isb.com.pl>
2006-03-22 17:58 ` Re: How to optimize this query? Markus Bertheau <mbertheau.pg@googlemail.com>
2006-03-22 18:18 ` Re: How to optimize this query? Maciej Piekielniak <piechcio@isb.com.pl>
2006-03-22 18:27 ` Re: How to optimize this query? Markus Bertheau <mbertheau.pg@googlemail.com>
2006-03-22 18:32 ` Re: How to optimize this query? Markus Bertheau <mbertheau.pg@googlemail.com>
2006-03-22 18:45 ` Re: How to optimize this query? Maciej Piekielniak <piechcio@isb.com.pl>
2006-03-22 18:52 ` Re: How to optimize this query? Maciej Piekielniak <piechcio@isb.com.pl>
2006-03-22 18:54 ` Re: How to optimize this query? Maciej Piekielniak <piechcio@isb.com.pl>
2006-03-22 19:07 ` Re: How to optimize this query? Maciej Piekielniak <piechcio@isb.com.pl>
2006-03-22 19:19 ` Re: How to optimize this query? Jeffrey Melloy <jmelloy@visualdistortion.org>
2006-03-22 19:25 ` Re: How to optimize this query? Maciej Piekielniak <piechcio@isb.com.pl>
2006-03-22 20:01 ` Re: How to optimize this query? Maciej Piekielniak <piechcio@isb.com.pl>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox