Re: [ADMIN] SQL-Statement - Mailing list pgsql-novice

From Godshall Michael
Subject Re: [ADMIN] SQL-Statement
Date
Msg-id A596FA3368757645AF862C701495CA0001B449BD@hor1mspmx01.gmachs.com
Whole thread Raw
List pgsql-novice


-----Original Message-----
From: Godshall Michael
Sent: Thursday, November 13, 2003 11:42 AM
To: 'Markus Espenhain'
Subject: RE: [ADMIN] SQL-Statement

How many records are their total in Daten, Monatsbeitrag?  Maybe 99% of the rows in the table have Daten.ewmanfrdat set to NULL.  With all the conditions joined using an AND if one of those fields doesn't match postgresql wouldn't need to look at the rest of the fields before going on to the next row.  That would cause the query to run much faster.

-----Original Message-----
From: Markus Espenhain [mailto:espenhain@etes.de]
Sent: Thursday, November 13, 2003 11:23 AM
To: Postgres
Subject: [ADMIN] SQL-Statement

Hello,

sorry for my previos mail.
This was a mistake...

I have an problem with the following statement.
This one works fine and give me the results in just a few seconds.

SELECT Monatsbeitrag.lfdid, Daten.Kundennummer FROM Daten, Monatsbeitrag WHERE ((((Daten.Vetrag widerufen = 2 ) AND NOT((Daten.ewmanfrdat IS NULL ) ) ) AND (Monatsbeitrag.ID = Daten.Kundennummer ) ) AND (((Monatsbeitrag.Monat + 14 ) =  '2003-10-27 00:00:00'::timestamp ) AND (Monatsbeitrag.Bezahlt = 2 ) ) ) ORDER BY Daten.Name;

When I edit the statement and delete the "NOT" before "Daten.ewmanfrdat IS NULL" the Query works over 15 Minutes.

SELECT Monatsbeitrag.lfdid, Daten.Kundennummer FROM Daten, Monatsbeitrag WHERE ((((Daten.Vetrag widerufen = 2 ) AND ((Daten.ewmanfrdat IS NULL ) ) ) AND (Monatsbeitrag.ID = Daten.Kundennummer )

) AND (((Monatsbeitrag.Monat + 14 ) =  '2003-10-27 00:00:00'::timestamp ) AND (Monatsbeitrag.Bezahlt = 2 ) ) ) ORDER BY Daten.Name;

OK, to select a lot of records it works a time, but for only 350 records?

Where is my problem?

Can anybody help me please.

Regards

Markus

--
Markus Espenhain                         Fon:  +49 (7 11) 48 90 83 - 0
ETES - EDV-Systemhaus GbR                Fax:  +49 (7 11) 48 90 83 - 50
Libanonstrasse 58 A * D-70184 Stuttgart  Web: http://www.etes.de

---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster

pgsql-novice by date:

Previous
From: Noel
Date:
Subject: Re: Java client on local machine update database on server
Next
From: Godshall Michael
Date:
Subject: Re: Stored Procedure/Return Rowset/Crystal