By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,986 Members | 2,086 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,986 IT Pros & Developers. It's quick & easy.

select not using index

P: 2
Hello,

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

I have a query:
telefon=# EXPLAIN SELECT * FROM ext.ext_unit_hier 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_hier_used_from_to" 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?)
Aug 22 '08 #1
Share this Question
Share on Google+
2 Replies


Expert 100+
P: 700
i'm not sure if btree works good with multidimensional indexes. maybe try different type of index (rtree or gist)?
Aug 22 '08 #2

P: 2
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:
http://www.postgresql.org/docs/8.3/i...lticolumn.html
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_hier_used_from" btree (used_from), tablespace "idx_space"
"idx_ext_unit_hier_used_to" btree (used_to), tablespace "idx_space"

telefon=# EXPLAIN SELECT * FROM ext.ext_unit_hier 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_hier_used_from (cost=0.00..4.66 rows=54 width=0)
Index Cond: (used_from = (now())::date)
(4 rows)

telefon=# EXPLAIN SELECT * FROM ext.ext_unit_hier 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_hier 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)
Aug 22 '08 #3

Post your reply

Sign in to post your reply or Sign up for a free account.