Обсуждение: 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:
so you force the poor database to do an left outer join just to find the outer records? Why?
I would start with:
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!
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
On 8/18/07, Rishi Daryanani <rishijd@yahoo.com> wrote:
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/
---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend
--
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!