On 5/22/07, Arnau <[EMAIL PROTECTED]> wrote:
On older versions of PostgreSQL, at least in my experience, queries
on timestamps fields even having indexes where performing quite bad
mainly sequential scans where performed.
PostgreSQL uses B-tree indexes for scalar values. For an expression
such as "t between a and b", I believe it's going to match both sides
of the table independently (ie., t >= a and t <= b) and intersect
these subsets. This is inefficient.
You should get better performance by mapping timestamps to a
one-dimensional plane and indexing them using GiST. GiST implements an
R-tree-like structure that supports bounding-box searches.
This involves setting up a functional index:
create index ... on payment_transactions using gist (
box(point(extract(epoch from time), 0), point(extract(epoch from
time), 0)) box_ops)
I'm using box() here because GiST doesn't have a concept of points.
Then insert as usual, and then query with something like:
select ... from payment_transactions
where box(
point(extract(epoch from '2006-04-01'::date), 0),
point(extract(epoch from '2006-08-01'::date), 0)) && box(
point(extract(epoch from time), 0),
point(extract(epoch from time), 0));
PostgreSQL should be able to exploit the GiST index by recognizing
that the result of box() expression operand is already computed in the
index.
This much less inconvenient and portable -- I would love for
PostgreSQL to be provide syntactic sugar and special-casing to make
this transparent -- but worth it if you are dealing with a lot of
range searches.
Now I have a newer version of PostgreSQL and I've done some tests
comparing the performance of an index over a timestamp field with a
numeric field. To do so, I have the following table:
Table "public.payment_transactions"
Column | Type | Modifiers
----------------+-----------------------------+---------------------------------
transaction_id | character varying(32) | not null
timestamp_in | timestamp without time zone | default now()
credits | integer |
epoch_in | bigint |
epoch_in2 | double precision |
[snip]
A timestamp is stored internally as an 8-byte double-precision float.
Therefore, timestamp_in and epoch_in2 should behave identically.
While doing the tests this table has about 100.000 entries.
Make sure PostgreSQL is able to keep the entire table in memory by
setting shared_buffers; you don't want to be hitting to the disk.
Make sure you run "analyze" on the table before you execute the test.
To test the diferent indexes I have executed the following:
Your query plans are roughly identical. The difference in the timings
implies that you only ran the queries once. I suggest you run each
query at least 10 times, and report the individual numbers (the "total
runtime" parts of the output) you get. Arithmetic means are not that
interesting.
Alexander.
---------------------------(end of broadcast)---------------------------
TIP 2: Don't 'kill -9' the postmaster
