FAQ
Hello,

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. It does work if we have
an index on just timehit in addition to the above. (of course)

Is this expected?

Joshua D. Drake

P.S. 8.1.9

--

=== 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/

Search Discussions

  • Tom Lane at Jun 1, 2007 at 8:47 pm

    "Joshua D. Drake" <jd@commandprompt.com> 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
  • Joshua D. Drake at Jun 1, 2007 at 9:02 pm

    Tom Lane wrote:
    "Joshua D. Drake" <jd@commandprompt.com> 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/
  • Tom Lane at Jun 1, 2007 at 9:08 pm

    "Joshua D. Drake" <jd@commandprompt.com> writes:
    Tom Lane wrote:
    "Joshua D. Drake" <jd@commandprompt.com> 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
  • Joshua D. Drake at Jun 1, 2007 at 9:54 pm

    Tom Lane wrote:
    "Joshua D. Drake" <jd@commandprompt.com> writes:
    Tom Lane wrote:
    "Joshua D. Drake" <jd@commandprompt.com> 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/
  • Joshua D. Drake at Jun 6, 2007 at 11:34 pm

    Joshua D. Drake wrote:
    Tom Lane wrote:
    "Joshua D. Drake" <jd@commandprompt.com> writes:
    Tom Lane wrote:
    "Joshua D. Drake" <jd@commandprompt.com> 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
  • Tom Lane at Jun 6, 2007 at 11:47 pm

    "Joshua D. Drake" <jd@commandprompt.com> 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
  • Joshua D. Drake at Jun 7, 2007 at 12:01 am

    Tom Lane wrote:
    "Joshua D. Drake" <jd@commandprompt.com> 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
  • Tom Lane at Jun 7, 2007 at 12:26 am

    "Joshua D. Drake" <jd@commandprompt.com> 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
  • Andreas Zeugswetter at Jun 4, 2007 at 10:41 am

    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

Related Discussions

Discussion Navigation
viewthread | post
Discussion Overview
grouppgsql-hackers @
categoriespostgresql
postedJun 1, '07 at 8:06p
activeJun 7, '07 at 12:26a
posts10
users3
websitepostgresql.org...
irc#postgresql

People

Translate

site design / logo © 2022 Grokbase