Thread: Re: [SQL] SQL Query Optimization

I'm sorry, I realized after posting this that it went to the wrong
list, I resent it to pgsql-sql instead.

But basically I haven't done any ANALYZE or EXPLAIN yet because of the 
fact that the order -is- making a difference so it can't be executing
the same query inside the database engine. Given that, I figured I would 
jsut look for theories on how to rewrite the queries before submitting first.

btw, we are using postgresql 7.1.2 compiled from source on rh linux 7.0.

I also might not have been very clear about the fact that the user is
basically constructing the query dynamically in the application, so it's
not a matter of just optimizing any specifc query, but any possible query.

Tom Lane [tgl@sss.pgh.pa.us] wrote:
> Dav Coleman <dav@serve.com> writes:
> > I have noticed that the semantically equivalent SQL queries can
> > differ
> > vastly in speed performance depending on the order of clauses ANDed
> > together ( "WHERE cond1 AND cond2" takes forever, but  "WHERE cond2
> > AND cond1" comes right back).
> 
> Could we see a specific example?
> 
> It would also be useful to know what PG version you are using, whether
> you've VACUUM ANALYZEd the tables, and what EXPLAIN has to say about
> your query.
> 
>             regards, tom lane

-- 
Dav Coleman
http://www.danger-island.com/dav/

-- 
Dav Coleman
http://www.danger-island.com/dav/

Dav Coleman <dav@danger-island.com> writes:
> But basically I haven't done any ANALYZE or EXPLAIN yet because of the 
> fact that the order -is- making a difference so it can't be executing
> the same query inside the database engine.

If you haven't ever done VACUUM ANALYZE then the planner is flying
completely blind as to table sizes and data distributions.  This would
(among other things) very possibly allow different plans to be estimated
as exactly the same cost --- since all the cost numbers will be based on
exactly the same default statistics.  So it's not surprising that you'd
get an arbitrary choice of plans depending on trivial details like
WHERE clause order.
        regards, tom lane