Thread: Question on INDEX and SQL - stalling my database?

Hi all,

I'm having problems with a query that's just
"stalling" my database. If someone could help me out -
I posted a forum topic on
http://forums.devshed.com/postgresql-help-21/postgresql-new-index-pros-and-cons-467120.html

There's just this one integer field, which when
searched on, "stalls" my query and I don't get any
results. I tried adding an index to that integer, but
that didn't seem to help..

Please let me know if there's any solution to this.
Any help would be really appreciated!

Many thanks!



____________________________________________________________________________________Ready for the edge of your seat?
Check out tonight's top picks on Yahoo! TV.
http://tv.yahoo.com/

Rishi Daryanani wrote:
> I'm having problems with a query that's just
> "stalling" my database. If someone could help me out -
> I posted a forum topic on
> http://forums.devshed.com/postgresql-help-21/postgresql-new-index-pros-and-cons-467120.html

Did you get any advice from that forum? Was it helpful?

> There's just this one integer field, which when
> searched on, "stalls" my query and I don't get any
> results. I tried adding an index to that integer, but
> that didn't seem to help..

Don't get any results? Ever? If so, adding an index wouldn't help.

What you'll need to supply is:
1. The query
2. Details of the table(s) - number of rows, number of distinct values
in columns being matched against
3. Output of EXPLAIN ANALYZE for your query
4. A couple of details of your server (RAM, number of disks, processors etc)

I'm sure someone here will be able to help then.

--
   Richard Huxton
   Archonet Ltd

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!