Alex Adriaanse <[EMAIL PROTECTED]> writes:
> Its row estimates are still way off. As a matter of fact, it almost seems as
> if the index doesn't affect row estimates at all.
Indexes normally don't affect estimates. Expression indexes do effectively
create a new column to generate stats for, but
Thank you all for your valuable input. I have tried creating a partial
index, a GIST index, and a GIST + partial index, as suggested, but it
does not seem to make a significant difference. For instance:
CREATE INDEX test_table_1_interval_idx ON test_table_1 USING GIST
(box(point(start_ts::
Tom Lane <[EMAIL PROTECTED]> writes:
> Otherwise I think you really need a special datatype for time
> intervals and a GIST or r-tree index on it :-(.
You could actually take short cuts using expression indexes to do this. If it
works out well then you might want to implement a real data type to
Greg Stark <[EMAIL PROTECTED]> writes:
> (I don't think the end_ts in the index is buying you much, despite its
> appearance in the Index Cond in the plan.)
Well, it saves some trips to the heap, but the indexscan is still going
to run from the beginning of the index to start_ts = now(), because
On Sat, Mar 04, 2006 at 02:01:35AM -0500, Greg Stark wrote:
> Alex Adriaanse <[EMAIL PROTECTED]> writes:
>
> > SELECT count(*) FROM test_table_1
> > INNER JOIN test_table_2 ON
> > (test_table_2.s_id = 13300613 AND test_table_1.id =
> > test_table_2.n_id)
> > WHERE now() BETWEEN te
Greg Stark <[EMAIL PROTECTED]> writes:
> The "now() BETWEEN test_table_1.start_ts AND test_table_1.end_ts can't be
> answered completely using a btree index. You could try using a GIST index here
> but I'm not clear how much it would help you (or how much work it would be).
To add to my own comm
Alex Adriaanse <[EMAIL PROTECTED]> writes:
> SELECT count(*) FROM test_table_1
> INNER JOIN test_table_2 ON
> (test_table_2.s_id = 13300613 AND test_table_1.id = test_table_2.n_id)
> WHERE now() BETWEEN test_table_1.start_ts AND test_table_1.end_ts
> AND test_table_1.id = test_
