Below are the operator definitions for one type. The other types are
exactly the same, just different argument types and procedures. I
noticed when I select the subset of each table (by date) out into a
temporary table and add the indices, joining on those to tables yields
the expected plan (merge join with 2 index scans). I think the planner
believes that the bitmap heap scan, sorted and then merged is a better
plan. What I don't understand is why? It seems to me that it could do a
btree lookup for the given date (or beginning of date range as needed),
for each index, and walk the trees in-order merging.
Run-time-wise, it takes 42 seconds to run this as a single query, vs 18
seconds to split the problem up with temp tables.
CREATE OPERATOR < ( LEFTARG = bucket_t, RIGHTARG = bucket_t, COMMUTATOR = >, NEGATOR = >=,
RESTRICT = scalarltsel, JOIN = scalarltjoinsel, PROCEDURE = bucket_t_lt
);
CREATE OPERATOR <= ( LEFTARG = bucket_t, RIGHTARG = bucket_t, COMMUTATOR = >=, NEGATOR = >,
RESTRICT = scalarltsel, JOIN = scalarltjoinsel, PROCEDURE = bucket_t_le
);
CREATE OPERATOR = ( LEFTARG = bucket_t, RIGHTARG = bucket_t, COMMUTATOR = =, NEGATOR = <>,
RESTRICT = eqsel, JOIN = eqjoinsel, HASHES, MERGES, PROCEDURE = bucket_t_eq
);
CREATE OPERATOR >= ( LEFTARG = bucket_t, RIGHTARG = bucket_t, COMMUTATOR = <=, NEGATOR = <,
RESTRICT = scalargtsel, JOIN = scalargtjoinsel, PROCEDURE = bucket_t_ge
);
CREATE OPERATOR > ( LEFTARG = bucket_t, RIGHTARG = bucket_t, COMMUTATOR = <, NEGATOR = <=,
RESTRICT = scalargtsel, JOIN = scalargtjoinsel, PROCEDURE = bucket_t_gt
);
CREATE OPERATOR <> ( LEFTARG = bucket_t, RIGHTARG = bucket_t, COMMUTATOR = <>, NEGATOR = =,
RESTRICT = neqsel, JOIN = neqjoinsel, PROCEDURE = bucket_t_ne
);
Tom Lane wrote:
> Greg Mitchell <gmitchell@atdesk.com> writes:
>> I don't understand why it re-sorts the data even though the indexes are in
>> the same order?
>
> I'm betting there's something wrong with your custom type definition,
> such that the planner is failing to make any connection between the
> index and the desired sort order. But you've not shown us any details.
>
> regards, tom lane
