Re: [HACKERS] Composite index and min()

On Thu, Feb 26, 2015 at 2:30 AM, Jim Nasby <Jim(dot)Nasby(at)bluetreble(dot)com> wrote:
> On 2/26/15 1:34 AM, James Sewell wrote:
>>
>> Hello,
>
>
> The correct place for this is pgsql-general(at)(dot) -hackers is for development of
> Postgres itself. I'm moving the discussion there.
>
>
>> I have the following table:
>>
>> \d a
>> Table "phxconfig.a"
>> Column | Type | Modifiers
>> ---------------+---------+-----------
>> phx_run_id | integer |
>> cell_id | integer |
>> Indexes:
>> "a_phx_run_id_cell_id_idx" btree (phx_run_id, cell_id)
>>
>> When I use a min() query I get the following plans:
>>
>> test=# explain select min(phx_run_id) from a;
>> QUERY PLAN
>>
>> -------------------------------------------------------------------------------------------------------
>> Result (cost=0.22..0.23 rows=1 width=0)
>> InitPlan 1 (returns $0)
>> -> Limit (cost=0.14..0.22 rows=1 width=4)
>> -> Index Only Scan using a_phx_run_id_cell_id_idx on a
>> (cost=0.14..7.89 rows=100 width=4)
>> Index Cond: (phx_run_id IS NOT NULL)
>>
>> test=# explain select min(cell_id) from a;
>> QUERY PLAN
>> ---------------------------------------------------------
>> Aggregate (cost=2.25..2.26 rows=1 width=4)
>> -> Seq Scan on a (cost=0.00..2.00 rows=100 width=4)
>>
>> Can anyone comment on why this happens?
>
>
> There's very little (if anything) that can be done when referring to the 2nd
> column in an index but not the first. (I think some bitmap stuff may be able
> to do it, but that would be pretty useless here).
>
>> The index kicks in when I do an explicit cell_id comparison.
>
>
> Please post EXPLAIN ANALYZE for that.
>
>> These are
>> large tables, and they are in a partition layout so it really hurts when
>> I do the min call on the parent table.
>
>
> Something doesn't look right in your EXPLAIN output if that table is
> supposed to be partitioned... what version are you on?

also, the planner thinks a only has 100 records which is quite a long
way from 'large tables' by any measure :-). I'm guessing OP made
scratch tables to present the problem. Unfortunately, that prevents
forensic analysis of the solution.

Take a look at 'http://explain.depesz.com/' which has an anonymizing feature.

merlin