sign in | join about | help | sitemap
Connecting Tech Pros Worldwide
vaxx's Avatar

select not using index


Question posted by: vaxx (Newbie) on August 22nd, 2008 08:15 AM
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?)
2 Answers Posted
rski's Avatar
rski August 22nd, 2008 10:23 AM
Moderator - 248 Posts
#2: Re: select not using index

i'm not sure if btree works good with multidimensional indexes. maybe try different type of index (rtree or gist)?
vaxx's Avatar
vaxx August 22nd, 2008 06:41 PM
Newbie - 2 Posts
#3: Re: select not using index

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/...ulticolumn.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)
Reply
Not the answer you were looking for? Post your question . . .
197,047 members ready to help you find a solution.
Join Bytes.com

What is Bytes?

We are a network of experts and professionals in IT and software development that help one another with answers to tough questions and share insights. Get the best answers to your questions from over 197,047 network members.
Post your question now . . .
It's fast and it's free

Popular Articles

Top PostgreSQL Contributors