Re: [GENERAL] Query optimization and indexes
Tom Lane [EMAIL PROTECTED] writes: Before (if memory serves) 8.1, the planner would only consider leading index columns as potential indexscan qualifiers. So given where a = 5 and c = 4; only the a = 5 clause would be used with the index. As of 8.1 it will consider using nonconsecutive index columns Really? Is this the skip scan plan people were pining for? I missed when that happened. -- Gregory Stark EnterpriseDB http://www.enterprisedb.com ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] Query optimization and indexes
Gregory Stark [EMAIL PROTECTED] writes: Tom Lane [EMAIL PROTECTED] writes: only the a = 5 clause would be used with the index. As of 8.1 it will consider using nonconsecutive index columns Really? Is this the skip scan plan people were pining for? No, there's no skip scan, it just applies all the indexable-column checks within the index instead of making a trip to the heap. For instance consider WHERE a = 4 AND a 7 AND c 5 with index entries A B C 3 9 8 3 9 9 4 0 0 - search starts here 4 0 1 reject ... 4 0 5 reject 4 0 6 accept (visit heap) 4 0 9 accept 4 1 0 reject ... 6 9 8 accept 6 9 9 accept 7 0 0 - search ends when we reach here 7 0 1 If the condition on C is very selective then we might find ourselves scanning over a lot of rejected entries within the possible bounds for A. The problem is to guess whether re-descending the search tree will win compared to just slogging forward, and if so to generate a suitable search key for each intermediate descent. regards, tom lane ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
[GENERAL] Query optimization and indexes
Suppose I have an index on 5 columns (A, B, C, D, E). If my WHERE clause is not in that order, will the optimizer reorder them as necessary and possible? WHERE A=1 AND C=3 AND B=2 AND E=5 AND D=4 Obviously it can't reorder them in all cases: WHERE A=1 AND (C=3 OR B=2) AND (E=5 OR D=4) If I don't specify columns in the WHERE clause, how much can it use the index? I think it is smart enough to use beginning columns: WHERE A=1 AND B=2 How about skipping leading columns? WHERE B=2 How about skipping intermediate columns? WHERE A=1 AND C=3 Or both, which is probably the same? WHERE B=2 AND D=4? -- ... _._. ._ ._. . _._. ._. ___ .__ ._. . .__. ._ .. ._. Felix Finch: scarecrow repairman rocket surgeon / [EMAIL PROTECTED] GPG = E987 4493 C860 246C 3B1E 6477 7838 76E9 182E 8151 ITAR license #4933 I've found a solution to Fermat's Last Theorem but I see I've run out of room o ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [GENERAL] Query optimization and indexes
[EMAIL PROTECTED] writes: Suppose I have an index on 5 columns (A, B, C, D, E). If my WHERE clause is not in that order, will the optimizer reorder them as necessary and possible? Yes, the optimizer understands about commutativity/associativity of AND and OR ;-) If I don't specify columns in the WHERE clause, how much can it use the index? Before (if memory serves) 8.1, the planner would only consider leading index columns as potential indexscan qualifiers. So given where a = 5 and c = 4; only the a = 5 clause would be used with the index. As of 8.1 it will consider using nonconsecutive index columns, but if you think for a bit about the storage order of a btree, you'll realize that you really need leading columns to keep down the amount of the index that gets scanned. A lot of the time, such a plan will be rejected as apparently more expensive than a seqscan. (This is for btrees, I don't recall the state of play for GIST indexes exactly.) regards, tom lane ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
