Re: Question on INDEX and SQL - stalling my database? - Mailing list pgsql-general

Rishi,

I looked up that thread....

1st:) p.s. I am using PostgreSQL 7.4.17

Any reason for that? Actual version is 8.2.4; or at least 8.1.9

2nd) your query is:

SELECT DISTINCT c.*
		FROM customer c
		LEFT OUTER JOIN weborders w

		ON c.username = w.username
		WHERE 	w.username IS NULL	
		AND c.sourceid IS NOT NULL
		AND	c.usertype = 0
		AND	c.emailrestrict = 1
		ORDER BY c.addcountrycode, c.surname, c.initials

so you force the poor database to do an left outer join just to find the outer records? Why?

I would start with:

select c.* from customer c 
where 
c.sourceid IS NOT NULL

		AND	c.usertype = 0
		AND	c.emailrestrict = 1
and 
c.username not in (select username from weborders)

BTW: I have a BAD feeling that your relation seems to be on "USERNAME", which should make it a primary key on "customer" - and a user entered primary key is a bad idea 104% of the time.

you can also save on the distinct, as every customer should have exactly one line in table customer. If not, you should dump that complete database and start from scratch.

Harald




--
GHUM Harald Massa
persuadere et programmare
Harald Armin Massa
Spielberger Straße 49
70435 Stuttgart
0173/9409607
fx 01212-5-13695179
-
EuroPython 2008 will take place in Vilnius, Lithuania - Stay tuned!