PostgreSQL 9.3 - ITEM IS CONTAINED VERSUS BETWEEN
I have a large (30M rows) simple table ...
CREATE TABLE "Foo"."Bar" (
"BarID" BIGSERIAL PRIMARY KEY,
"DateTime" TIMESTAMP NOT NULL,
"Bar" TEXT NOT NULL
);
... a simple index:
CREATE INDEX ON "Foo"."Bar"("DateTime");
... and a simple question:
What "BarID"
are the meanings "DateTime"
in the first hour of 2015?
So, I made this request from # 1:
SELECT
"Bar"."BarID"
FROM
"Foo"."Bar"
WHERE
"Bar"."DateTime" <@ TSRANGE('2015-01-01 00:00:00', '2015-01-01 01:00:00');
... and this query # 2:
SELECT
"Bar"."BarID"
FROM
"Foo"."Bar"
WHERE
"Bar"."DateTime" BETWEEN '2015-01-01 00:00:00' AND '2015-01-01 01:00:00';
results
Request # 1 starts after 60 seconds with a sequence check.
Query # 2 runs in 0.02 seconds on an index scan.
I tried to do another index USING GiST
with no improvement.
What gives?
source to share
Range values are valid. You just need an index that the range expression can use. You have a B-tree index and a GiST index on a "timestamp" column. A label range expression cannot use any of these indices.
Create a GiST index on a time range expression and update the statistics.
create index on "Foo"."Bar"
using gist(tsrange("DateTime"::timestamp, "DateTime"::timestamp, '[]'));
analyze "Foo"."Bar";
Your "DateTime" column represents a point in time, so the timeline range expression must have inclusive upper and lower bounds ( '[]'
).
Rewrite the WHERE clause to use the same expression.
explain analyze
select "BarID"
from "Foo"."Bar"
where tsrange("DateTime"::timestamp, "DateTime"::timestamp, '[]') <@ tsrange('2015-01-01 00:00:00', '2015-01-01 01:00:00');
This query can use an index and it works here in about half a millisecond on a table of about a million rows.
"Bitmap Heap Scan on" Bar "(cost = 10.19..859.53 rows = 246 width = 8) (actual time = 0.195..0.551 rows = 219 loops = 1)"
"Recheck Cond: (tsrange (" DateTime "," DateTime ", '[]' :: text) <@ '[" 2015-01-01 00:00:00 "," 2015-01-01 01:00: 00 ") ':: tsrange)"
"-> Bitmap Index Scan on" Bar_tsrange_idx "(cost = 0.00..10.13 rows = 246 width = 0) (actual time = 0.160..0.160 rows = 219 loops = 1)"
"Index Cond: (tsrange (" DateTime "," DateTime ", '[]' :: text) <@ '[" 2015-01-01 00:00:00 "," 2015-01-01 01:00: 00 ") ':: tsrange)"
"Total runtime: 0.589 ms"
source to share