Re: Composite fields and the query planner

On 05/05/13 15:06, Tom Lane wrote:
> Steve Rogerson <steve(dot)pg(at)yewtc(dot)demon(dot)co(dot)uk> writes:
>> I'm seeing a problem with the query planner not doing what's expected, and I
>> think it is because we are using composite fields. Here is a stripped down
>> example.
>> create type type1 as ( part1 varchar, part2 varchar);
>> create table table1 (field1 type1, field2 varchar);
>> create function get_part1(type1) returns varchar as $$ select ($1).part1 $$
>> language sql;
>> create index i1 on table1 (get_part1(field1));
>> create index i2 on table1 (field2);
> I tested this example in HEAD and 9.0.x and didn't see any particular
> problem with rowcount estimates for the get_part1() expression. You
> do have to have the i1 index in place when the table is analyzed, else
> ANALYZE won't collect any stats about the expression.
>
> regards, tom lane
In the end it turned out to be another issue. As best as I can see, what
happened was that if the value of field2 was not in the common values list,
then it would use the two indexes, otherwise it would use one index and
filter by field2. It takes about 10 times longer to get the two list and "AND"
them than doing one and filtering. I think we've found a work around, using a
combined index.

Having said all that I did discover an unrelated problem with composite
fields. Basically it doesn't seem to consult the pg_stats data to decide if to
do a seq scan or an index scan. Always saying that it should do an index
scan. I'm not quite sure how it could compare the value that is indexed with
the pg_stats data, so I guess it either decides it's all less than or all
greater than the value you are checking for.

Steve