X-Original-To: pgsql-admin-postgresql.org@localhost.postgresql.org Received: from localhost (mx1.hub.org [200.46.208.251]) by postgresql.org (Postfix) with ESMTP id 1555811F624C for ; Wed, 26 Apr 2006 21:46:02 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.208.251]) (amavisd-new, port 10024) with ESMTP id 16907-10 for ; Wed, 26 Apr 2006 21:45:47 -0300 (ADT) X-Greylist: from auto-whitelisted by SQLgrey- Received: from stash.illumen.com (unknown [64.207.29.137]) by postgresql.org (Postfix) with ESMTP id 21FF511F60B1 for ; Wed, 26 Apr 2006 21:45:44 -0300 (ADT) Content-class: urn:content-classes:message MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----_=_NextPart_001_01C66993.EA9368F7" Subject: Re: Table partitioning X-MimeOLE: Produced By Microsoft Exchange V6.5 Date: Wed, 26 Apr 2006 18:45:44 -0600 Message-ID: <8511B4970E0D124898E973DF496F9B4357E11C@stash.stackdump.local> X-MS-Has-Attach: X-MS-TNEF-Correlator: Thread-Topic: [ADMIN] Table partitioning Thread-Index: AcZphAy4OQW7rx+yQ/Svwx8XSGnX4gADZ+tF References: <8511B4970E0D124898E973DF496F9B432515CC@stash.stackdump.local> <20060426225201.GH97354@pervasive.com> From: "Benjamin Krajmalnik" To: "Jim C. Nasby" , X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200604/294 X-Sequence-Number: 21463 This is a multi-part message in MIME format. ------_=_NextPart_001_01C66993.EA9368F7 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable I do not have remote access right now via psql. from pgAdmin, the table definition is as follows: =20 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) )=20 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); =20 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. =20 The query issued is: =20 explain analyze select * from tblksraw order by ksrawsysid desc limit 10 =20 QUERY PLAN Limit (cost=3D5735754.58..5735754.61 rows=3D10 width=3D1186) (actual = time=3D110504.991..110505.012 rows=3D10 loops=3D1) -> Sort (cost=3D5735754.58..5740302.80 rows=3D1819286 width=3D1186) = (actual time=3D110504.979..110504.990 rows=3D10 loops=3D1) Sort Key: public.tblksraw.ksrawsysid -> Result (cost=3D0.00..116054.86 rows=3D1819286 width=3D1186) = (actual time=3D0.199..18356.729 rows=3D1908043 loops=3D1) -> Append (cost=3D0.00..116054.86 rows=3D1819286 = width=3D1186) (actual time=3D0.188..14210.022 rows=3D1908043 loops=3D1) -> Seq Scan on tblksraw (cost=3D0.00..115927.66 = rows=3D1818566 width=3D255) (actual time=3D0.186..12560.791 = rows=3D1908043 loops=3D1) -> Seq Scan on tblksraw01 tblksraw = (cost=3D0.00..10.60 rows=3D60 width=3D1186) (actual time=3D0.001..0.001 = rows=3D0 loops=3D1) -> Seq Scan on tblksraw02 tblksraw = (cost=3D0.00..10.60 rows=3D60 width=3D1186) (actual time=3D0.001..0.001 = rows=3D0 loops=3D1) -> Seq Scan on tblksraw03 tblksraw = (cost=3D0.00..10.60 rows=3D60 width=3D1186) (actual time=3D0.002..0.002 = rows=3D0 loops=3D1) -> Seq Scan on tblksraw04 tblksraw = (cost=3D0.00..10.60 rows=3D60 width=3D1186) (actual time=3D0.001..0.001 = rows=3D0 loops=3D1) -> Seq Scan on tblksraw05 tblksraw = (cost=3D0.00..10.60 rows=3D60 width=3D1186) (actual time=3D0.001..0.001 = rows=3D0 loops=3D1) -> Seq Scan on tblksraw06 tblksraw = (cost=3D0.00..10.60 rows=3D60 width=3D1186) (actual time=3D0.001..0.001 = rows=3D0 loops=3D1) -> Seq Scan on tblksraw07 tblksraw = (cost=3D0.00..10.60 rows=3D60 width=3D1186) (actual time=3D0.001..0.001 = rows=3D0 loops=3D1) -> Seq Scan on tblksraw08 tblksraw = (cost=3D0.00..10.60 rows=3D60 width=3D1186) (actual time=3D0.001..0.001 = rows=3D0 loops=3D1) -> Seq Scan on tblksraw09 tblksraw = (cost=3D0.00..10.60 rows=3D60 width=3D1186) (actual time=3D0.001..0.001 = rows=3D0 loops=3D1) -> Seq Scan on tblksraw10 tblksraw = (cost=3D0.00..10.60 rows=3D60 width=3D1186) (actual time=3D0.001..0.001 = rows=3D0 loops=3D1) -> Seq Scan on tblksraw11 tblksraw = (cost=3D0.00..10.60 rows=3D60 width=3D1186) (actual time=3D0.002..0.002 = rows=3D0 loops=3D1) -> Seq Scan on tblksraw12 tblksraw = (cost=3D0.00..10.60 rows=3D60 width=3D1186) (actual time=3D0.001..0.001 = rows=3D0 loops=3D1) 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. =20 This is on PostgreSQL 8.1.3, Windows native port. =20 =20 =20 =20 =20 =20 =20 ________________________________ From: Jim C. Nasby [mailto:jnasby@pervasive.com] Sent: Wed 4/26/2006 4:52 PM To: Benjamin Krajmalnik Cc: Chris Hoover; pgsql-admin@postgresql.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. > >=20 > >=20 > > ________________________________ > > From: Chris Hoover [mailto:revoohc@gmail.com] > Sent: Wednesday, April 26, 2006 2:33 PM > To: Benjamin Krajmalnik > Cc: pgsql-admin@postgresql.org > Subject: Re: [ADMIN] Tale partitioning > >=20 > > 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@pervasive.com Pervasive Software http://pervasive.com work: 512-231-6117 vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461 ------_=_NextPart_001_01C66993.EA9368F7 Content-Type: text/html; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable =0A= =0A= =0A= =0A= =0A= =0A= Re: [ADMIN] Table partitioning=0A= =0A= =0A=
=0A=
I do not have = remote access =0A= right now via psql.
=0A=
from pgAdmin, the table = definition is as =0A= follows:
=0A=
 
=0A=
CREATE TABLE tblksraw
(
  devicename = varchar(50) NOT =0A= NULL,
  accountno int8 NOT NULL,
  testtime timestamp = NOT =0A= NULL,
  replytxt varchar(1024),
  replyval = float8,
  =0A= teststatusid int4 NOT NULL,
  totaltests int8,
  = failedcount =0A= int8,
  passedcount int8,
  unknowncount int8,
  =0A= alivetime varchar(20),
  deadtime varchar(20),
  = unknowntime =0A= varchar(20),
  aliveratio varchar(10),
  deadratio =0A= varchar(10),
  unknownratio varchar(10),
  avgreply =0A= float8,
  minreply float8,
  maxreply float8,
  =0A= ksrawsysid int8 NOT NULL DEFAULT =0A= nextval('tblksraw_ksrawsysid_seq'::regclass),
  priority =0A= varchar(25),
  testguid varchar(38),
  testid = int8,
  =0A= partitionid int2,
  CONSTRAINT pk_tblksraw PRIMARY KEY = (ksrawsysid)
) =0A=
WITHOUT OIDS;
ALTER TABLE tblksraw OWNER TO postgres;
=0A=

-- Index: k_account
=0A=
-- DROP INDEX k_account;
=0A=
CREATE INDEX k_account
  ON tblksraw
  = USING =0A= btree
  (accountno, testtime);
=0A=
-- Index: k_ksrawtestguid
=0A=
-- DROP INDEX k_ksrawtestguid;
=0A=
CREATE INDEX k_ksrawtestguid
  ON = tblksraw
  USING =0A= btree
  (testguid, testid, testtime);
=0A=
-- Index: k_testtime
=0A=
-- DROP INDEX k_testtime;
=0A=
CREATE INDEX k_testtime
  ON tblksraw
  = USING =0A= btree
  (testtime);
=0A=
 
=0A=
The inherited tables do not yet have constraints placed = upon them, =0A= but currently they do not have any data.  I have not yet created = the =0A= triggers to insert in the specific partition of the table.
=0A=
 
=0A=
The query issued is:
=0A=
 
=0A=
explain analyze select * from tblksraw order by = ksrawsysid desc =0A= limit 10
=0A=
 
=0A=
QUERY PLAN
Limit  (cost=3D5735754.58..5735754.61 = rows=3D10 =0A= width=3D1186) (actual time=3D110504.991..110505.012 rows=3D10 = loops=3D1)
  =0A= ->  Sort  (cost=3D5735754.58..5740302.80 rows=3D1819286 = width=3D1186) =0A= (actual time=3D110504.979..110504.990 rows=3D10 =0A= loops=3D1)
        Sort Key: =0A= public.tblksraw.ksrawsysid
        =0A= ->  Result  (cost=3D0.00..116054.86 rows=3D1819286 = width=3D1186) (actual =0A= time=3D0.199..18356.729 rows=3D1908043 =0A= loops=3D1)
          = ;    =0A= ->  Append  (cost=3D0.00..116054.86 rows=3D1819286 = width=3D1186) (actual =0A= time=3D0.188..14210.022 rows=3D1908043 =0A= loops=3D1)
          = ;          =0A= ->  Seq Scan on tblksraw  (cost=3D0.00..115927.66 = rows=3D1818566 =0A= width=3D255) (actual time=3D0.186..12560.791 rows=3D1908043 =0A= loops=3D1)
          = ;          =0A= ->  Seq Scan on tblksraw01 tblksraw  (cost=3D0.00..10.60 = rows=3D60 =0A= width=3D1186) (actual time=3D0.001..0.001 rows=3D0 =0A= loops=3D1)
          = ;          =0A= ->  Seq Scan on tblksraw02 tblksraw  (cost=3D0.00..10.60 = rows=3D60 =0A= width=3D1186) (actual time=3D0.001..0.001 rows=3D0 =0A= loops=3D1)
          = ;          =0A= ->  Seq Scan on tblksraw03 tblksraw  (cost=3D0.00..10.60 = rows=3D60 =0A= width=3D1186) (actual time=3D0.002..0.002 rows=3D0 =0A= loops=3D1)
          = ;          =0A= ->  Seq Scan on tblksraw04 tblksraw  (cost=3D0.00..10.60 = rows=3D60 =0A= width=3D1186) (actual time=3D0.001..0.001 rows=3D0 =0A= loops=3D1)
          = ;          =0A= ->  Seq Scan on tblksraw05 tblksraw  (cost=3D0.00..10.60 = rows=3D60 =0A= width=3D1186) (actual time=3D0.001..0.001 rows=3D0 =0A= loops=3D1)
          = ;          =0A= ->  Seq Scan on tblksraw06 tblksraw  (cost=3D0.00..10.60 = rows=3D60 =0A= width=3D1186) (actual time=3D0.001..0.001 rows=3D0 =0A= loops=3D1)
          = ;          =0A= ->  Seq Scan on tblksraw07 tblksraw  (cost=3D0.00..10.60 = rows=3D60 =0A= width=3D1186) (actual time=3D0.001..0.001 rows=3D0 =0A= loops=3D1)
          = ;          =0A= ->  Seq Scan on tblksraw08 tblksraw  (cost=3D0.00..10.60 = rows=3D60 =0A= width=3D1186) (actual time=3D0.001..0.001 rows=3D0 =0A= loops=3D1)
          = ;          =0A= ->  Seq Scan on tblksraw09 tblksraw  (cost=3D0.00..10.60 = rows=3D60 =0A= width=3D1186) (actual time=3D0.001..0.001 rows=3D0 =0A= loops=3D1)
          = ;          =0A= ->  Seq Scan on tblksraw10 tblksraw  (cost=3D0.00..10.60 = rows=3D60 =0A= width=3D1186) (actual time=3D0.001..0.001 rows=3D0 =0A= loops=3D1)
          = ;          =0A= ->  Seq Scan on tblksraw11 tblksraw  (cost=3D0.00..10.60 = rows=3D60 =0A= width=3D1186) (actual time=3D0.002..0.002 rows=3D0 =0A= loops=3D1)
          = ;          =0A= ->  Seq Scan on tblksraw12 tblksraw  (cost=3D0.00..10.60 = rows=3D60 =0A= width=3D1186) (actual time=3D0.001..0.001 rows=3D0 loops=3D1)
Total = runtime: =0A= 112625.246 ms
=0A=
This could potentiall be a huge issue when paging through = result =0A= set.  For example, if to retrieve the next 10 records it needs to = process a =0A= multi-million row result set.
=0A=
Until now I have been running MS SQL Server 2000.  = In it, if a =0A= resultset is limited (such as select top 10 * from table), even if there = are no =0A= where or order by clauses, only the first x rows are processed and the = results =0A= are immediate.
=0A=
 
=0A=
This is on PostgreSQL 8.1.3, Windows native port.
=0A=
 
=0A=
 
=0A=
 
=0A=
 
=0A=
 
=0A=
 
=0A=
 
=0A=

=0A=
=0A= From: Jim C. Nasby =0A= [mailto:jnasby@pervasive.com]
Sent: Wed 4/26/2006 4:52 =0A= PM
To: Benjamin Krajmalnik
Cc: Chris Hoover; =0A= pgsql-admin@postgresql.org
Subject: Re: [ADMIN] Table =0A= partitioning

=0A=
=0A=

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

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

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

=0A= =0A= =0A= ------_=_NextPart_001_01C66993.EA9368F7--