Re: bad plan with custom data types

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(at)atdesk(dot)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