Composite index planner issues Was: Re: [HACKERS] Constraint exclusion oddity with composite index
Joshua D. Drake wrote: Tom Lane wrote: Joshua D. Drake [EMAIL PROTECTED] writes: Tom Lane wrote: Joshua D. Drake [EMAIL PROTECTED] writes: Assume the following: index on: (id, adate) constraint CHECK(adate '01-01-2007' AND adate '04-01-2007'); The planner will not use the index listed above. For what? select adate from parent where adate = '01-25-2007' That's unsurprising. Searching with only a lower-order index column value seldom wins, 'cause you've got to scan the entire index. The constraint is irrelevant to this. I guess where I got confused is: http://www.postgresql.org/docs/8.1/static/indexes-multicolumn.html And explicitly: A multicolumn B-tree index can be used with query conditions that involve any subset of the index's columns, but the index is most efficient when there are constraints on the leading (leftmost) columns. Considering the paragraph from the documentation above, should we change the documentation? Joshua D. Drake Sincerely, Joshua D. Drake regards, tom lane ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate
Re: Composite index planner issues Was: Re: [HACKERS] Constraint exclusion oddity with composite index
Joshua D. Drake [EMAIL PROTECTED] writes: I guess where I got confused is: http://www.postgresql.org/docs/8.1/static/indexes-multicolumn.html And explicitly: A multicolumn B-tree index can be used with query conditions that involve any subset of the index's columns, but the index is most efficient when there are constraints on the leading (leftmost) columns. Considering the paragraph from the documentation above, should we change the documentation? That statement seems perfectly accurate to me. regards, tom lane ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: Composite index planner issues Was: Re: [HACKERS] Constraint exclusion oddity with composite index
Tom Lane wrote: Joshua D. Drake [EMAIL PROTECTED] writes: I guess where I got confused is: http://www.postgresql.org/docs/8.1/static/indexes-multicolumn.html And explicitly: A multicolumn B-tree index can be used with query conditions that involve any subset of the index's columns, but the index is most efficient when there are constraints on the leading (leftmost) columns. Considering the paragraph from the documentation above, should we change the documentation? That statement seems perfectly accurate to me. O.k. then perhaps I am being dense, but that statement says to me that the planner should be able to use the right element of a composite index but that it will not always do so. Considering an index of a,b if I search for b I would expect that the planner could use the index. Assuming of course that the planner would use the same index if it was just b. Further, I would expect a smaller chance of it using b if the index was a,c,b but that it might still use it. Is that not the case? Should I expect that even in the simplest of cases that we will not use an index unless it is *the* leftmost element? Sincerely, Joshua D. Drake regards, tom lane ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: Composite index planner issues Was: Re: [HACKERS] Constraint exclusion oddity with composite index
Joshua D. Drake [EMAIL PROTECTED] writes: Tom Lane wrote: That statement seems perfectly accurate to me. Considering an index of a,b if I search for b I would expect that the planner could use the index. It can. Whether it will think that's a good idea is another question entirely, and one that seems a bit beyond the scope of the discussion you're mentioning. Try forcing the issue with enable_seqscan, and see what sort of estimated and actual costs you get ... regards, tom lane ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Constraint exclusion oddity with composite index
Assume the following: index on: (id, adate) constraint CHECK(adate '01-01-2007' AND adate '04-01-2007'); Um, the subject is CE, but the question is about an index ? Those are separate issues. The planner will not use the index listed above. For what? select adate from parent where adate = '01-25-2007' A possibly cheaper plan would be a self join to produce all possible id's and join the index for each (id, adate) pair. Note, that you need not check visibility of the id's you produce (index only access). Is that what you were expecting ? This is not implemented. Andreas ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] Constraint exclusion oddity with composite index
Joshua D. Drake [EMAIL PROTECTED] writes: Assume the following: index on: (id, adate) constraint CHECK(adate '01-01-2007' AND adate '04-01-2007'); The planner will not use the index listed above. For what? regards, tom lane ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [HACKERS] Constraint exclusion oddity with composite index
Tom Lane wrote: Joshua D. Drake [EMAIL PROTECTED] writes: Assume the following: index on: (id, adate) constraint CHECK(adate '01-01-2007' AND adate '04-01-2007'); The planner will not use the index listed above. For what? select adate from parent where adate = '01-25-2007' For example. Joshua D. Drake regards, tom lane ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match -- === The PostgreSQL Company: Command Prompt, Inc. === Sales/Support: +1.503.667.4564 || 24x7/Emergency: +1.800.492.2240 Providing the most comprehensive PostgreSQL solutions since 1997 http://www.commandprompt.com/ Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate PostgreSQL Replication: http://www.commandprompt.com/products/ ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] Constraint exclusion oddity with composite index
Joshua D. Drake [EMAIL PROTECTED] writes: Tom Lane wrote: Joshua D. Drake [EMAIL PROTECTED] writes: Assume the following: index on: (id, adate) constraint CHECK(adate '01-01-2007' AND adate '04-01-2007'); The planner will not use the index listed above. For what? select adate from parent where adate = '01-25-2007' That's unsurprising. Searching with only a lower-order index column value seldom wins, 'cause you've got to scan the entire index. The constraint is irrelevant to this. regards, tom lane ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] Constraint exclusion oddity with composite index
Tom Lane wrote: Joshua D. Drake [EMAIL PROTECTED] writes: Tom Lane wrote: Joshua D. Drake [EMAIL PROTECTED] writes: Assume the following: index on: (id, adate) constraint CHECK(adate '01-01-2007' AND adate '04-01-2007'); The planner will not use the index listed above. For what? select adate from parent where adate = '01-25-2007' That's unsurprising. Searching with only a lower-order index column value seldom wins, 'cause you've got to scan the entire index. The constraint is irrelevant to this. I guess where I got confused is: http://www.postgresql.org/docs/8.1/static/indexes-multicolumn.html And explicitly: A multicolumn B-tree index can be used with query conditions that involve any subset of the index's columns, but the index is most efficient when there are constraints on the leading (leftmost) columns. Sincerely, Joshua D. Drake regards, tom lane ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster -- === The PostgreSQL Company: Command Prompt, Inc. === Sales/Support: +1.503.667.4564 || 24x7/Emergency: +1.800.492.2240 Providing the most comprehensive PostgreSQL solutions since 1997 http://www.commandprompt.com/ Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate PostgreSQL Replication: http://www.commandprompt.com/products/ ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
