Re: Table partitioning

 

CREATE TABLE tblksraw
(
  devicename varchar(50) NOT NULL,
  accountno int8 NOT NULL,
  testtime timestamp NOT NULL,
  replytxt varchar(1024),
  replyval float8,
  teststatusid int4 NOT NULL,
  totaltests int8,
  failedcount int8,
  passedcount int8,
  unknowncount int8,
  alivetime varchar(20),
  deadtime varchar(20),
  unknowntime varchar(20),
  aliveratio varchar(10),
  deadratio varchar(10),
  unknownratio varchar(10),
  avgreply float8,
  minreply float8,
  maxreply float8,
  ksrawsysid int8 NOT NULL DEFAULT nextval('tblksraw_ksrawsysid_seq'::regclass),
  priority varchar(25),
  testguid varchar(38),
  testid int8,
  partitionid int2,
  CONSTRAINT pk_tblksraw PRIMARY KEY (ksrawsysid)
)
WITHOUT OIDS;
ALTER TABLE tblksraw OWNER TO postgres;

-- Index: k_account

-- DROP INDEX k_account;

CREATE INDEX k_account
  ON tblksraw
  USING btree
  (accountno, testtime);

-- Index: k_ksrawtestguid

-- DROP INDEX k_ksrawtestguid;

CREATE INDEX k_ksrawtestguid
  ON tblksraw
  USING btree
  (testguid, testid, testtime);

-- Index: k_testtime

-- DROP INDEX k_testtime;

CREATE INDEX k_testtime
  ON tblksraw
  USING btree
  (testtime);

 

The inherited tables do not yet have constraints placed upon them, but currently they do not have any data.  I have not yet created the triggers to insert in the specific partition of the table.

 

The query issued is:

 

explain analyze select * from tblksraw order by ksrawsysid desc limit 10

 

QUERY PLAN
Limit  (cost=5735754.58..5735754.61 rows=10 width=1186) (actual time=110504.991..110505.012 rows=10 loops=1)
  ->  Sort  (cost=5735754.58..5740302.80 rows=1819286 width=1186) (actual time=110504.979..110504.990 rows=10 loops=1)
        Sort Key: public.tblksraw.ksrawsysid
        ->  Result  (cost=0.00..116054.86 rows=1819286 width=1186) (actual time=0.199..18356.729 rows=1908043 loops=1)
              ->  Append  (cost=0.00..116054.86 rows=1819286 width=1186) (actual time=0.188..14210.022 rows=1908043 loops=1)
                    ->  Seq Scan on tblksraw  (cost=0.00..115927.66 rows=1818566 width=255) (actual time=0.186..12560.791 rows=1908043 loops=1)
                    ->  Seq Scan on tblksraw01 tblksraw  (cost=0.00..10.60 rows=60 width=1186) (actual time=0.001..0.001 rows=0 loops=1)
                    ->  Seq Scan on tblksraw02 tblksraw  (cost=0.00..10.60 rows=60 width=1186) (actual time=0.001..0.001 rows=0 loops=1)
                    ->  Seq Scan on tblksraw03 tblksraw  (cost=0.00..10.60 rows=60 width=1186) (actual time=0.002..0.002 rows=0 loops=1)
                    ->  Seq Scan on tblksraw04 tblksraw  (cost=0.00..10.60 rows=60 width=1186) (actual time=0.001..0.001 rows=0 loops=1)
                    ->  Seq Scan on tblksraw05 tblksraw  (cost=0.00..10.60 rows=60 width=1186) (actual time=0.001..0.001 rows=0 loops=1)
                    ->  Seq Scan on tblksraw06 tblksraw  (cost=0.00..10.60 rows=60 width=1186) (actual time=0.001..0.001 rows=0 loops=1)
                    ->  Seq Scan on tblksraw07 tblksraw  (cost=0.00..10.60 rows=60 width=1186) (actual time=0.001..0.001 rows=0 loops=1)
                    ->  Seq Scan on tblksraw08 tblksraw  (cost=0.00..10.60 rows=60 width=1186) (actual time=0.001..0.001 rows=0 loops=1)
                    ->  Seq Scan on tblksraw09 tblksraw  (cost=0.00..10.60 rows=60 width=1186) (actual time=0.001..0.001 rows=0 loops=1)
                    ->  Seq Scan on tblksraw10 tblksraw  (cost=0.00..10.60 rows=60 width=1186) (actual time=0.001..0.001 rows=0 loops=1)
                    ->  Seq Scan on tblksraw11 tblksraw  (cost=0.00..10.60 rows=60 width=1186) (actual time=0.002..0.002 rows=0 loops=1)
                    ->  Seq Scan on tblksraw12 tblksraw  (cost=0.00..10.60 rows=60 width=1186) (actual time=0.001..0.001 rows=0 loops=1)
Total runtime: 112625.246 ms

This could potentiall be a huge issue when paging through result set.  For example, if to retrieve the next 10 records it needs to process a multi-million row result set.

Until now I have been running MS SQL Server 2000.  In it, if a resultset is limited (such as select top 10 * from table), even if there are no where or order by clauses, only the first x rows are processed and the results are immediate.

 

This is on PostgreSQL 8.1.3, Windows native port.