Re: Table partitioning

"Benjamin Krajmalnik" <kraj@xxxxxxxxxxx> · Wed, 26 Apr 2006 18:45:44 -0600

Title: Re: [ADMIN] Table partitioning

I do not have remote access 
right now via psql.
from pgAdmin, the table definition is as 
follows:

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.

From: Jim C. Nasby 
[mailto:jnasby@xxxxxxxxxxxxx]
Sent: Wed 4/26/2006 4:52 
PM
To: Benjamin Krajmalnik
Cc: Chris Hoover; 
pgsql-admin@xxxxxxxxxxxxxx
Subject: Re: [ADMIN] Table 
partitioning

Can we see the output of \d tablename as well as EXPLAIN ANALYZE 
of the
select?

On Wed, Apr 26, 2006 at 02:48:50PM -0600, Benjamin 
Krajmalnik wrote:
> Actually, right now there is no data in those 
partitions.
>
> All of the data is currently in the parent table (I 
have not yet created
> the trigger which will route the data to the 
correct partition).
>
> I just found to items intriguing - first, 
that the indices and other
> properties other than the field definition 
were not inherited (is this
> how this is supposed to work?), and second, 
that PG first retrieves the
> entire result set and then limits it (or at 
least that appear to be how
> it is working).
>
> If the order 
by clause were an _expression_, I can understand where it
> would have to 
first retrieve the entire resultset and then limit it.
> However, when we 
are dealing with an order by clause running on an index
> or primary key, 
I would figure that it would only retrieve the number of
> rows limited, 
or if an offset is specified then go to the offset and
> only process the 
"limit" number of 
rows.
>
> 
>
> 
>
> 
________________________________
>
> From: Chris Hoover [mailto:revoohc@xxxxxxxxx]
> Sent: 
Wednesday, April 26, 2006 2:33 PM
> To: Benjamin Krajmalnik
> Cc: 
pgsql-admin@xxxxxxxxxxxxxx
> Subject: Re: [ADMIN] Tale 
partitioning
>
> 
>
> Each of the partition tables 
needs it's own set of indexes.  Build them,
> and see if the does not 
fix your performance issues.  Also, be sure you
> turned on the 
constraint_exclusion parameter, and each table (other than
> the "master") 
has an constraint on it that is unique.
>
> HTH,
>
> 
Chris
>
>
>
>

--
Jim C. Nasby, Sr. Engineering 
Consultant      jnasby@xxxxxxxxxxxxx
Pervasive 
Software      http://pervasive.com    work: 
512-231-6117
vcard: http://jim.nasby.net/pervasive.vcf       
cell: 512-569-9461