select not using index

Collapse
X
 
  • Time
  • Show
Clear All
new posts
  • vaxx
    New Member
    • Aug 2008
    • 2

    select not using index

    Hello,

    My name is Mihaly Fazekas.
    Sorry, my english is not good.

    I have a query:
    telefon=# EXPLAIN SELECT * FROM ext.ext_unit_hi er WHERE now()::date BETWEEN used_from AND used_to;
    QUERY PLAN
    -------------------------------------------------------------------------
    Seq Scan on ext_unit_hier (cost=0.00..242 .58 rows=5808 width=36)
    Filter: (((now())::date >= used_from) AND ((now())::date <= used_to))
    (2 rows)

    One of :) index of the table:
    "idx_ext_unit_h ier_used_from_t o" btree (used_from, used_to), tablespace "idx_space"

    Number of records in this table at moment: 7343

    This table is one element of a "big query".
    Wha can i do? (How can SELECT use already presented index?)
  • rski
    Recognized Expert Contributor
    • Dec 2006
    • 700

    #2
    i'm not sure if btree works good with multidimensiona l indexes. maybe try different type of index (rtree or gist)?

    Comment

    • vaxx
      New Member
      • Aug 2008
      • 2

      #3
      Other version of my problem:
      If WHERE contains used_from=now() ::date, then query can use index(es).
      If WHERE contains "<=" or "<" or ">", then query not use indexes.

      I'm checking the multicolumn indexes+btree:
      On the postgresql page:

      contains: "Currently, only the B-tree and GiST index types support multicolumn indexes. Up to 32 columns can be specified. "


      What can i do?


      Indexes:
      "idx_ext_unit_h ier_used_from" btree (used_from), tablespace "idx_space"
      "idx_ext_unit_h ier_used_to" btree (used_to), tablespace "idx_space"

      telefon=# EXPLAIN SELECT * FROM ext.ext_unit_hi er WHERE used_from=now() ::date;
      QUERY PLAN
      -------------------------------------------------------------------------------------------
      Bitmap Heap Scan on ext_unit_hier (cost=4.67..66. 13 rows=54 width=36)
      Recheck Cond: (used_from = (now())::date)
      -> Bitmap Index Scan on idx_ext_unit_hi er_used_from (cost=0.00..4.6 6 rows=54 width=0)
      Index Cond: (used_from = (now())::date)
      (4 rows)

      telefon=# EXPLAIN SELECT * FROM ext.ext_unit_hi er WHERE used_from<=now( )::date;
      QUERY PLAN
      -------------------------------------------------------------------
      Seq Scan on ext_unit_hier (cost=0.00..187 .50 rows=7343 width=36)
      Filter: (used_from <= (now())::date)
      (2 rows)

      telefon=# EXPLAIN SELECT * FROM ext.ext_unit_hi er WHERE used_from<now() ::date;
      QUERY PLAN
      -------------------------------------------------------------------
      Seq Scan on ext_unit_hier (cost=0.00..187 .50 rows=7343 width=36)
      Filter: (used_from < (now())::date)
      (2 rows)

      Comment

      Working...