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(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?
--
Jim Nasby, Data Architect, Blue Treble Consulting
Data in Trouble? Get it in Treble! http://BlueTreble.com