Re: 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(at)pervasive(dot)com]
Sent: Wed 4/26/2006 4:52 PM
To: Benjamin Krajmalnik
Cc: Chris Hoover; pgsql-admin(at)postgresql(dot)org
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(at)gmail(dot)com]
> Sent: Wednesday, April 26, 2006 2:33 PM
> To: Benjamin Krajmalnik
> Cc: pgsql-admin(at)postgresql(dot)org
> 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(at)pervasive(dot)com
Pervasive Software http://pervasive.com work: 512-231-6117
vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461