Postgres Pro
Downloads
Home   >   mailing lists

Thread: SQL Help

SQL Help

From
"Mark A. Summers"
Date:
I am having trouble with the following query taking forever:
-----
SELECT * FROM ret108108_00, product
WHERE ret108108_00."isbn" = product."Item1"

AND   product."SuperCategory" = '1'
AND   product."PublisherCode" = 'ZON'
ORDER BY ret108108_00.qty DESC LIMIT 100
----
The problem is the second AND -- if I take this out it runs fine -- is there
any way to optimize it ?  I had a similar problem with just Category so I
created a new field in the product file called FullCat which combined
SuperCategory and Category and thus eliminating the 2nd AND clause -- and
that fixed it -- I just didn't want to do the same thing with Publisher.
Before I tried to index Category, SuperCategory ... nothing seemed to help

Any ideas

Thanks in advance
Mark Summers

Re: SQL Help

From
Stephan Szabo
Date:
On Fri, 26 Jan 2001, Mark A. Summers wrote:

> I am having trouble with the following query taking forever:
> -----
> SELECT * FROM ret108108_00, product
> WHERE ret108108_00."isbn" = product."Item1"
> 
> AND   product."SuperCategory" = '1'
> AND   product."PublisherCode" = 'ZON'
> ORDER BY ret108108_00.qty DESC LIMIT 100
> ----
> The problem is the second AND -- if I take this out it runs fine -- is there
> any way to optimize it ?  I had a similar problem with just Category so I
> created a new field in the product file called FullCat which combined
> SuperCategory and Category and thus eliminating the 2nd AND clause -- and
> that fixed it -- I just didn't want to do the same thing with Publisher.
> Before I tried to index Category, SuperCategory ... nothing seemed to help

Have you run a VACUUM ANALYZE on the table?  And what does
EXPLAIN show for the query?

Re: SQL Help

From
"Mark A. Summers"
Date:
I am loser -- I didn't realize there was analyze option on vacuum -- I
created another index on Publisher code -- Now it smokes

thanks

Mark Summers

"Stephan Szabo" <sszabo@megazone23.bigpanda.com> wrote in message
news:Pine.BSF.4.21.0101261746560.98620-100000@megazone23.bigpanda.com...
>
> On Fri, 26 Jan 2001, Mark A. Summers wrote:
>
> > I am having trouble with the following query taking forever:
> > -----
> > SELECT * FROM ret108108_00, product
> > WHERE ret108108_00."isbn" = product."Item1"
> >
> > AND   product."SuperCategory" = '1'
> > AND   product."PublisherCode" = 'ZON'
> > ORDER BY ret108108_00.qty DESC LIMIT 100
> > ----
> > The problem is the second AND -- if I take this out it runs fine -- is
there
> > any way to optimize it ?  I had a similar problem with just Category so
I
> > created a new field in the product file called FullCat which combined
> > SuperCategory and Category and thus eliminating the 2nd AND clause --
and
> > that fixed it -- I just didn't want to do the same thing with Publisher.
> > Before I tried to index Category, SuperCategory ... nothing seemed to
help
>
> Have you run a VACUUM ANALYZE on the table?  And what does
> EXPLAIN show for the query?
>