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

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

On 8/18/07, Rishi Daryanani <rishijd(at)yahoo(dot)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!