Thread: Re: [HACKERS] Composite index and min()

On 2/26/15 1:34 AM, James Sewell wrote:
> Hello,

The correct place for this is pgsql-general@. -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?
--
Jim Nasby, Data Architect, Blue Treble Consulting
Data in Trouble? Get it in Treble! http://BlueTreble.com

On Thu, Feb 26, 2015 at 2:30 AM, Jim Nasby <Jim.Nasby@bluetreble.com> wrote:
> On 2/26/15 1:34 AM, James Sewell wrote:
>>
>> Hello,
>
>
> The correct place for this is pgsql-general@. -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