title: postgreSQL get last ID in partitioned tables /
body: <p>my question is basically the same as this one, but i couldn't find an answer, its also written "to be solved in the next release" and "easy for min/max scans"</p>
  
  <p><a href="https://stackoverflow.com/questions/3874986/postgresqltable-partitioning-inefficient-max-and-min">PostgreSQL+table partitioning: inefficient max() and min()</a></p>
  
  <pre><code>CREATE TABLE mc_handstats
  (
    id integer NOT NULL DEFAULT nextval('mc_handst_id_seq'::regclass),
    playerid integer NOT NULL,
    CONSTRAINT mc_handst_pkey PRIMARY KEY (id),
  );
  </code></pre>
  
  <p>table is partitioned over playerid.</p>
  
  <pre><code>CREATE TABLE mc_handst_0000 ( CHECK ( playerid &gt;= 0 AND playerid &lt; 10000) ) INHERITS (mc_handst) TABLESPACE ssd01;
  CREATE TABLE mc_handst_0010 ( CHECK ( playerid &gt;= 10000 AND playerid &lt; 30000) ) INHERITS (mc_handst) TABLESPACE ssd02;
  CREATE TABLE mc_handst_0030 ( CHECK ( playerid &gt;= 30000 AND playerid &lt; 50000) ) INHERITS (mc_handst) TABLESPACE ssd03;
  ...
  
  CREATE INDEX mc_handst_0000_PlayerID ON mc_handst_0000 (playerid);
  CREATE INDEX mc_handst_0010_PlayerID ON mc_handst_0010 (playerid);
  CREATE INDEX mc_handst_0030_PlayerID ON mc_handst_0030 (playerid);
  ...
  
  plus create trigger on playerID
  </code></pre>
  
  <p>i want to get the last id (i could also get the value for the sequence, but i am used to work with tables/colums), but pSQL seems to be rather stupid scanning the table:</p>
  
  <p>EXPLAIN ANALYZE select max(id) from mc_handstats; (the real query runs forever)</p>
  
  <pre><code>"Aggregate  (cost=9080859.04..9080859.05 rows=1 width=4) (actual time=181867.626..181867.626 rows=1 loops=1)"
  "  -&gt;  Append  (cost=0.00..8704322.43 rows=150614644 width=4) (actual time=2.460..163638.343 rows=151134891 loops=1)"
  "        -&gt;  Seq Scan on mc_handstats  (cost=0.00..0.00 rows=1 width=4) (actual time=0.002..0.002 rows=0 loops=1)"
  "        -&gt;  Seq Scan on mc_handst_0000 mc_handstats  (cost=0.00..728523.69 rows=12580969 width=4) (actual time=2.457..10800.539 rows=12656647 loops=1)"
  ...
  ALL TABLES
  ...
  "Total runtime: 181867.819 ms"
  </code></pre>
  
  <p>EXPLAIN ANALYZE select max(id) from mc_handst_1000</p>
  
  <pre><code>"Aggregate  (cost=83999.50..83999.51 rows=1 width=4) (actual time=1917.933..1917.933 rows=1 loops=1)"
  "  -&gt;  Seq Scan on mc_handst_1000  (cost=0.00..80507.40 rows=1396840 width=4) (actual time=0.007..1728.268 rows=1396717 loops=1)"
  "Total runtime: 1918.494 ms"
  </code></pre>
  
  <p>the runtime for the partitioned table is 'snap', and completely off the record over the master table. (postgreSQL 9.2)</p>
  
  <hr>
  
  <p>\d mc_handstats (only the indexes)</p>
  
  <pre><code>Indexes:
      "mc_handst_pkey" PRIMARY KEY, btree (id)
  Foreign-key constraints:
      "mc_handst_playerid_fkey" FOREIGN KEY (playerid) REFERENCES mc_players(id)
  Triggers:
      mc_handst_insert_trigger BEFORE INSERT ON mc_handstats FOR EACH ROW EXECUTE PROCEDURE mc_handst_insert_function()
  Number of child tables: 20 (Use \d+ to list them.)
  </code></pre>
  
  <p>\d mc_handst_1000</p>
  
  <pre><code>Indexes:
      "mc_handst_1000_playerid" btree (playerid)
  Check constraints:
      "mc_handst_1000_playerid_check" CHECK (playerid &gt;= 1000000 AND playerid &lt; 1100000)
  </code></pre>
  
  <p>hm, no PK index in the sub tables. while i don't understand why the result for max(id) is pretty fast on the subtables (as there is no index) and slow from the master table, it seems i need to add an index for PK also for all subtables. maybe that solves it.</p>
  
  <hr>
  
  <pre><code>CREATE INDEX mc_handst_0010_ID ON mc_handst_0010 (id);
  ... plus many more ...
  </code></pre>
  
  <p>and everything fine. still strange why it worked fast on the subtables before, that made me think they are indexed, but i also don't care to much.</p>
  
  <p>thanks for this!</p>