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@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