0

Using a btree as my index. From what I understand(http://www.postgresql.org/docs/9.0/static/indexes-types.html), btree works for comparison operators. For equality operator it works fine

explain analyze select latitude, longitude 
from internal.location_log 
WHERE entry_timestamp = 32903023;


Index Scan using internal_location_log_entry_timestamp_idx on location_log  (cost=0.42..8.44 rows=1 width=16) (actual time=0.063..0.063 rows=0 loops=1)
   Index Cond: (entry_timestamp = 32903023)
 Planning time: 0.104 ms
 Execution time: 0.088 ms

however for comparison operator like

explain analyze select latitude, longitude 
from internal.location_log 
WHERE entry_timestamp >= 32903023;

Seq Scan on location_log  (cost=0.00..17969.96 rows=821837 width=16) (actual time=0.012..347.941 rows=821957 loops=1)
   Filter: (entry_timestamp >= 32903023)
 Planning time: 0.209 ms
 Execution time: 537.699 ms

Am I missing something?

Improve this question
2
  • 2
    Because the second query returns so many rows that an index lookup would be slower than just scanning through the whole table. See e.g. here: dba.stackexchange.com/q/93621/1822
    – user1822
    Nov 3, 2015 at 8:13
  • Your right... I would like to close this as the right answer... Can you please answer it so that I can just mark it as answered or should I delete the question?
    – fzaziz
    Nov 3, 2015 at 9:03

0

Reset to default