On Thu, Feb 26, 2015 at 2:30 AM, Jim Nasby <Jim.Nasby@xxxxxxxxxxxxxx> 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 -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
