Results 1 to 6 of 6
  1. #1
    Join Date
    Aug 2010
    Posts
    7

    Unanswered: Postgres is not using indexing when I do '>' or '<'

    I have a query like this:
    Code:
    EXPLAIN SELECT * FROM record WHERE timestamp > xxxx-xx-xx xx:xx:xx.xxxxxx
    The postresql is not using my indexing on column timestamp. If not change the ">" to "=", it is then using my indexing. Why is that? ">" and "<" can not use indexing? Any tweak?

    Thanks.

  2. #2
    Join Date
    Aug 2009
    Location
    Olympia, WA
    Posts
    337
    Most likely your statistics are stale or there are not enough rows in the table to make the planner decide to use the index. Is it a development table with few rows or a production table w/ many rows?

  3. #3
    Join Date
    Nov 2003
    Posts
    2,935
    Provided Answers: 12
    Code:
    xxxx-xx-xx xx:xx:xx.xxxxxx
    Is not a valid timestamp literal for Postgres.

    Please show us the full table definition and the real statement you are executing.

  4. #4
    Join Date
    Aug 2010
    Posts
    7
    @artacus72 I have tons of data like 8 digits of rows.

    @shammat I put xxxx, just to show you an example. It's actually 2010-08-10 00:00:00.000000

    real statement:
    Code:
    EXPLAIN SELECT * FROM record WHERE timestamp > 2010-08-10 00:00:00.000000
    query returns:
    Code:
    "GroupAggregate  (cost=789153.82..848274.01 rows=2627564 width=15)"
    "  ->  Sort  (cost=789153.82..795722.73 rows=2627564 width=15)"
    "        Sort Key: "timestamp", source"
    "        ->  Seq Scan on record  (cost=0.00..374270.94 rows=2627564 width=15)"
    "              Filter: (("timestamp" > '2010-08-07 00:00:00'::timestamp without time zone) AND ("timestamp" < '2010-08-08 23:59:00'::timestamp without time zone))"
    It is doing sorting, not indexing.

    If I do (changed > to =):
    Code:
    EXPLAIN SELECT * FROM record WHERE timestamp = 2010-08-10 00:00:00.000000
    query return:
    Code:
    "GroupAggregate  (cost=0.00..10.32 rows=1 width=15)"
    "  ->  Index Scan using timestamp_source on record  (cost=0.00..10.30 rows=1 width=15)"
    "        Index Cond: ("timestamp" = '2010-08-07 00:00:00'::timestamp without time zone)"

  5. #5
    Join Date
    Nov 2003
    Posts
    2,935
    Provided Answers: 12
    Quote Originally Posted by cpthk View Post
    SELECT * FROM record WHERE timestamp > 2010-08-10 00:00:00.000000
    That is not a valid PostgreSQL statement. It will throw an error.

    How many rows does the table contain?

  6. #6
    Join Date
    Aug 2009
    Location
    Olympia, WA
    Posts
    337
    Try running ANALYZE on your table. Also what type of index is it? Postgres supports a variety of index types. The default is B-tree, and will work fine for < or > type queries. But not all indexes types will.

    Also, your explain doesn't match your sample queries. Your explain plan is from a query with a group by.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •