Re: EOAndQualifier order
Re: EOAndQualifier order
- Subject: Re: EOAndQualifier order
- From: Q <email@hidden>
- Date: Fri, 13 Jul 2007 00:05:23 +1000
On 12/07/2007, at 8:19 PM, Miguel Arroz wrote:
Hi!
PostgreSQL manual, chapter 11, the section about multi-column
indexes. I realized, after further testing, that the planner tries
to re-arrange the order. But I think there are situations where the
planner may choose one or another column because there's no
"optimum" solution, and that choice comes from the order of the
args. But I'm not sure about any os this, I don't know a lot about
DBs, and I hate them more everyday.
Yes, order of multi column index elements can make a big difference
to query performance. However, that's out of EOF's control. The
order of the conditions in the WHERE clause will have no bearing on
the query planner's final cost predictions, or how effective an index
will be at improving performance.
The query planner in PostgreSQL will use the table statistics to try
and predict which index/condition combination will yield the desired
number of rows with the least cost. Table statistics are updated by
periodically running ANALYZE or VACUUM on the table. If you do not do
either of these things regularly the query planner may be unable to
make an accurate prediction.
Yours
Miguel Arroz
On 2007/07/12, at 01:08, Q wrote:
On 12/07/2007, at 7:29 AM, Miguel Arroz wrote:
Hi!
I'm looking and the generated SQL from an EOAndQualifier, and
it looks like the order of the "anded" stuff is the inverse one I
write on the code. If I write a qualifier like "bananas = %@ and
apples = %@ and oranges = %@", the generated SQL will be the
reverse (t0.oranges = (...) AND t0.apples = (...) AND to.bananas
= (...)".
Is this normal? Is the order random? What's happening here? I'm
asking this, because from what I understand form the PostgreSQL
docs, the order of the stuff in ANDs (and ORs) may have cause a
really big difference in performance when indexes (and multi-
indexes) are envolved.
Where exactly did you read this? Order of the conditional
expressions shouldn't make any difference to the query planner.
--
Seeya...Q
Quinton Dolan - email@hidden
Gold Coast, QLD, Australia (GMT+10)
Ph: +61 419 729 806
Miguel Arroz
http://www.terminalapp.net
http://www.ipragma.com
--
Seeya...Q
Quinton Dolan - email@hidden
Gold Coast, QLD, Australia (GMT+10)
Ph: +61 419 729 806
_______________________________________________
Do not post admin requests to the list. They will be ignored.
Webobjects-dev mailing list (email@hidden)
Help/Unsubscribe/Update your Subscription:
This email sent to email@hidden