Composite index and min()
Hello,
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?
The index kicks in when I do an explicit cell_id comparison. 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.
Cheers,
James Sewell,
PostgreSQL Team Lead / Solutions Architect
______________________________________
Level 2, 50 Queen St, Melbourne VIC 3000
*P *(+61) 3 8370 8000 *W* www.lisasoft.com *F *(+61) 3 8370 8099
--
------------------------------
The contents of this email are confidential and may be subject to legal or
professional privilege and copyright. No representation is made that this
email is free of viruses or other defects. If you have received this
communication in error, you may not copy or distribute any part of it or
otherwise disclose its contents to anyone. Please advise the sender of your
incorrect receipt of this correspondence.
On 2/26/15 1:34 AM, James Sewell wrote:
I have the following table:
I'm moving this discussion to -general.
--
Jim Nasby, Data Architect, Blue Treble Consulting
Data in Trouble? Get it in Treble! http://BlueTreble.com
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
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
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
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
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general