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 F362A11F60B0 for ; Wed, 26 Apr 2006 14:50:41 -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 58138-10 for ; Wed, 26 Apr 2006 14:50:18 -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 1C0E711F60E5 for ; Wed, 26 Apr 2006 14:50:16 -0300 (ADT) Content-class: urn:content-classes:message MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----_=_NextPart_001_01C66959.DE2D27FE" Subject: Tale partitioning X-MimeOLE: Produced By Microsoft Exchange V6.5 Date: Wed, 26 Apr 2006 11:50:12 -0600 Message-ID: <8511B4970E0D124898E973DF496F9B432515C3@stash.stackdump.local> X-MS-Has-Attach: X-MS-TNEF-Correlator: Thread-Topic: Tale partitioning Thread-Index: AcZpWdzvP6z5EUuHSSqkMi53ytWMiw== From: "Benjamin Krajmalnik" To: X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=1.218 tagged_above=0 required=5 tests=BAYES_00, HTML_MESSAGE, SPF_HELO_SOFTFAIL, SPF_SOFTFAIL X-Spam-Level: * X-Archive-Number: 200604/280 X-Sequence-Number: 21449 This is a multi-part message in MIME format. ------_=_NextPart_001_01C66959.DE2D27FE Content-Type: text/plain; charset="us-ascii" Content-Transfer-Encoding: quoted-printable I have created a parent table and inherited into several tables to facilitate table partitioning. I noticed that the inherited tables did not inherit any of the indices, etc. in their DDL. Do these need to be created, or are they actually there via inheritance but only "used" when the parent table is queried directly? =20 Also, I am having what appear to be some performance issues when querying a large table which currently has about 1.8 million rows. It may be an issue of me adjusting to gow PostgreSQL's planner works. =20 =20 ksrawsysid is the primary key. I expected it to perform very quickly, yet it took about 90 seconds to return a recordset. Adding a where clause made the query almost instantaneous. =20 The query which I issued was: =20 select * from tblksraw order by ksrawsysid desc limit 10 =20 QUERY PLAN Limit (cost=3D5351703.70..5351703.72 rows=3D10 width=3D1186) -> Sort (cost=3D5351703.70..5355948.36 rows=3D1697865 width=3D1186) Sort Key: public.tblksraw.ksrawsysid -> Result (cost=3D0.00..108314.65 rows=3D1697865 width=3D1186) -> Append (cost=3D0.00..108314.65 rows=3D1697865 = width=3D1186) -> Seq Scan on tblksraw (cost=3D0.00..108187.45 rows=3D1697145 width=3D255) -> Seq Scan on tblksraw01 tblksraw (cost=3D0.00..10.60 rows=3D60 width=3D1186) -> Seq Scan on tblksraw02 tblksraw (cost=3D0.00..10.60 rows=3D60 width=3D1186) -> Seq Scan on tblksraw03 tblksraw (cost=3D0.00..10.60 rows=3D60 width=3D1186) -> Seq Scan on tblksraw04 tblksraw (cost=3D0.00..10.60 rows=3D60 width=3D1186) -> Seq Scan on tblksraw05 tblksraw (cost=3D0.00..10.60 rows=3D60 width=3D1186) -> Seq Scan on tblksraw06 tblksraw (cost=3D0.00..10.60 rows=3D60 width=3D1186) -> Seq Scan on tblksraw07 tblksraw (cost=3D0.00..10.60 rows=3D60 width=3D1186) -> Seq Scan on tblksraw08 tblksraw (cost=3D0.00..10.60 rows=3D60 width=3D1186) -> Seq Scan on tblksraw09 tblksraw (cost=3D0.00..10.60 rows=3D60 width=3D1186) -> Seq Scan on tblksraw10 tblksraw (cost=3D0.00..10.60 rows=3D60 width=3D1186) -> Seq Scan on tblksraw11 tblksraw (cost=3D0.00..10.60 rows=3D60 width=3D1186) -> Seq Scan on tblksraw12 tblksraw (cost=3D0.00..10.60 rows=3D60 width=3D1186) =20 ksrawsysid is the primary key. I expected it to perform very quickly, yet it took about 90 seconds to return a recordset. Adding a where clause made the query almost instantaneous. =20 select * from tblksraw where ksrawsysid between 1000000 and 1000020 order by ksrawsysid desc limit 10 =20 =20 =20 QUERY PLAN Limit (cost=3D142.85..142.87 rows=3D10 width=3D1186) -> Sort (cost=3D142.85..142.93 rows=3D31 width=3D1186) Sort Key: public.tblksraw.ksrawsysid -> Result (cost=3D0.00..142.08 rows=3D31 width=3D1186) -> Append (cost=3D0.00..142.08 rows=3D31 width=3D1186) -> Index Scan using pk_tblksraw on tblksraw (cost=3D0.00..11.28 rows=3D19 width=3D255) Index Cond: ((ksrawsysid >=3D 1000000) AND (ksrawsysid <=3D 1000020)) -> Seq Scan on tblksraw01 tblksraw (cost=3D0.00..10.90 rows=3D1 width=3D1186) Filter: ((ksrawsysid >=3D 1000000) AND (ksrawsysid <=3D 1000020)) -> Seq Scan on tblksraw02 tblksraw (cost=3D0.00..10.90 rows=3D1 width=3D1186) Filter: ((ksrawsysid >=3D 1000000) AND (ksrawsysid <=3D 1000020)) -> Seq Scan on tblksraw03 tblksraw (cost=3D0.00..10.90 rows=3D1 width=3D1186) Filter: ((ksrawsysid >=3D 1000000) AND (ksrawsysid <=3D 1000020)) -> Seq Scan on tblksraw04 tblksraw (cost=3D0.00..10.90 rows=3D1 width=3D1186) Filter: ((ksrawsysid >=3D 1000000) AND (ksrawsysid <=3D 1000020)) -> Seq Scan on tblksraw05 tblksraw (cost=3D0.00..10.90 rows=3D1 width=3D1186) Filter: ((ksrawsysid >=3D 1000000) AND (ksrawsysid <=3D 1000020)) -> Seq Scan on tblksraw06 tblksraw (cost=3D0.00..10.90 rows=3D1 width=3D1186) Filter: ((ksrawsysid >=3D 1000000) AND (ksrawsysid <=3D 1000020)) -> Seq Scan on tblksraw07 tblksraw (cost=3D0.00..10.90 rows=3D1 width=3D1186) Filter: ((ksrawsysid >=3D 1000000) AND (ksrawsysid <=3D 1000020)) -> Seq Scan on tblksraw08 tblksraw (cost=3D0.00..10.90 rows=3D1 width=3D1186) Filter: ((ksrawsysid >=3D 1000000) AND (ksrawsysid <=3D 1000020)) -> Seq Scan on tblksraw09 tblksraw (cost=3D0.00..10.90 rows=3D1 width=3D1186) Filter: ((ksrawsysid >=3D 1000000) AND (ksrawsysid <=3D 1000020)) -> Seq Scan on tblksraw10 tblksraw (cost=3D0.00..10.90 rows=3D1 width=3D1186) Filter: ((ksrawsysid >=3D 1000000) AND (ksrawsysid <=3D 1000020)) -> Seq Scan on tblksraw11 tblksraw (cost=3D0.00..10.90 rows=3D1 width=3D1186) Filter: ((ksrawsysid >=3D 1000000) AND (ksrawsysid <=3D 1000020)) -> Seq Scan on tblksraw12 tblksraw (cost=3D0.00..10.90 rows=3D1 width=3D1186) Filter: ((ksrawsysid >=3D 1000000) AND (ksrawsysid <=3D 1000020)) =20 Form the query plans, it appears that I have to explicitly create indices on the inherited tables. I would have assumed they would have been inherited as well. I expected that limiting the number of rows would have resulted in an immediate result, whereas in actuality what appears to be happening is that the entire table is being processed as a result set, but only 10 rows being returned. This seems like extraneous. =20 Regards, =20 Benjamin ------_=_NextPart_001_01C66959.DE2D27FE Content-Type: text/html; charset="us-ascii" Content-Transfer-Encoding: quoted-printable

I have created a parent table and inherited into = several tables to facilitate table partitioning.

I noticed that the inherited tables did not inherit = any of the indices, etc. in their DDL.

Do these need to be created, or are they actually = there via inheritance but only “used” when the parent table is queried directly?

 

Also, I am having what appear to be some performance = issues when querying a large table which currently has about 1.8 million = rows.

It may be an issue of me adjusting to gow = PostgreSQL’s planner works.

 

 

ksrawsysid is the primary key.  I expected it to = perform very quickly, yet it took about 90 seconds to return a = recordset.

Adding a where clause made the query almost = instantaneous.

 

The query which I issued = was:

 

select * from tblksraw order by ksrawsysid desc limit = 10

 

QUERY PLAN

Limit  (cost=3D5351703.70..5351703.72 rows=3D10 = width=3D1186)

  ->  Sort  = (cost=3D5351703.70..5355948.36 rows=3D1697865 width=3D1186)

        Sort Key: public.tblksraw.ksrawsysid

        = ->  Result  (cost=3D0.00..108314.65 rows=3D1697865 = width=3D1186)

         =      ->  Append  (cost=3D0.00..108314.65 rows=3D1697865 = width=3D1186)

         =            ->  Seq Scan on tblksraw  (cost=3D0.00..108187.45 = rows=3D1697145 width=3D255)

         =            ->  Seq Scan on tblksraw01 tblksraw  (cost=3D0.00..10.60 = rows=3D60 width=3D1186)

         =            ->  Seq Scan on tblksraw02 tblksraw  (cost=3D0.00..10.60 = rows=3D60 width=3D1186)

         =            ->  Seq Scan on tblksraw03 tblksraw  (cost=3D0.00..10.60 = rows=3D60 width=3D1186)

         =            ->  Seq Scan on tblksraw04 tblksraw  (cost=3D0.00..10.60 = rows=3D60 width=3D1186)

         =            ->  Seq Scan on tblksraw05 tblksraw  (cost=3D0.00..10.60 = rows=3D60 width=3D1186)

         =            ->  Seq Scan on tblksraw06 tblksraw  (cost=3D0.00..10.60 = rows=3D60 width=3D1186)

         =            ->  Seq Scan on tblksraw07 tblksraw  (cost=3D0.00..10.60 = rows=3D60 width=3D1186)

         =            ->  Seq Scan on tblksraw08 tblksraw  (cost=3D0.00..10.60 = rows=3D60 width=3D1186)

        =             -= >  Seq Scan on tblksraw09 tblksraw  (cost=3D0.00..10.60 rows=3D60 = width=3D1186)

         =            ->  Seq Scan on tblksraw10 tblksraw  (cost=3D0.00..10.60 = rows=3D60 width=3D1186)

         =            ->  Seq Scan on tblksraw11 tblksraw  (cost=3D0.00..10.60 = rows=3D60 width=3D1186)

         =            ->  Seq Scan on tblksraw12 tblksraw  (cost=3D0.00..10.60 = rows=3D60 width=3D1186)

 

ksrawsysid is the primary key.  I expected it to = perform very quickly, yet it took about 90 seconds to return a = recordset.

Adding a where clause made the query almost = instantaneous.

 

select * from tblksraw  where ksrawsysid between 1000000 and 1000020 order by ksrawsysid desc limit = 10

 

 

 

QUERY PLAN

Limit  (cost=3D142.85..142.87 rows=3D10 = width=3D1186)

  ->  Sort  (cost=3D142.85..142.93 = rows=3D31 width=3D1186)

        Sort Key: public.tblksraw.ksrawsysid

        = ->  Result  (cost=3D0.00..142.08 rows=3D31 = width=3D1186)

         =      ->  Append  (cost=3D0.00..142.08 rows=3D31 = width=3D1186)

         =            ->  Index Scan using pk_tblksraw on tblksraw  = (cost=3D0.00..11.28 rows=3D19 width=3D255)

         =             &= nbsp;    Index Cond: ((ksrawsysid >=3D 1000000) AND (ksrawsysid <=3D = 1000020))

         =            ->  Seq Scan on tblksraw01 tblksraw  (cost=3D0.00..10.90 = rows=3D1 width=3D1186)

         =             &= nbsp;    Filter: ((ksrawsysid >=3D 1000000) AND (ksrawsysid <=3D = 1000020))

         =            ->  Seq Scan on tblksraw02 tblksraw  (cost=3D0.00..10.90 = rows=3D1 width=3D1186)

         =             &= nbsp;    Filter: ((ksrawsysid >=3D 1000000) AND (ksrawsysid <=3D = 1000020))

         =            ->  Seq Scan on tblksraw03 tblksraw  (cost=3D0.00..10.90 = rows=3D1 width=3D1186)

         =             &= nbsp;    Filter: ((ksrawsysid >=3D 1000000) AND (ksrawsysid <=3D = 1000020))

         =            ->  Seq Scan on tblksraw04 tblksraw  (cost=3D0.00..10.90 = rows=3D1 width=3D1186)

         =             &= nbsp;    Filter: ((ksrawsysid >=3D 1000000) AND (ksrawsysid <=3D = 1000020))

         =            ->  Seq Scan on tblksraw05 tblksraw  (cost=3D0.00..10.90 = rows=3D1 width=3D1186)

         =             &= nbsp;    Filter: ((ksrawsysid >=3D 1000000) AND (ksrawsysid <=3D = 1000020))

         =            ->  Seq Scan on tblksraw06 tblksraw  (cost=3D0.00..10.90 = rows=3D1 width=3D1186)

         =                  Filter: ((ksrawsysid >=3D 1000000) AND (ksrawsysid <=3D = 1000020))

         =            ->  Seq Scan on tblksraw07 tblksraw  (cost=3D0.00..10.90 = rows=3D1 width=3D1186)

         =             &= nbsp;    Filter: ((ksrawsysid >=3D 1000000) AND (ksrawsysid <=3D = 1000020))

  =             &= nbsp;     ->  Seq Scan on tblksraw08 tblksraw  (cost=3D0.00..10.90 rows=3D1 = width=3D1186)

         =             &= nbsp;    Filter: ((ksrawsysid >=3D 1000000) AND (ksrawsysid <=3D = 1000020))

         =            ->  Seq Scan on tblksraw09 tblksraw  (cost=3D0.00..10.90 = rows=3D1 width=3D1186)

         =             &= nbsp;    Filter: ((ksrawsysid >=3D 1000000) AND (ksrawsysid <=3D = 1000020))

         =            ->  Seq Scan on tblksraw10 tblksraw  (cost=3D0.00..10.90 = rows=3D1 width=3D1186)

         =             &= nbsp;    Filter: ((ksrawsysid >=3D 1000000) AND (ksrawsysid <=3D = 1000020))

         =            ->  Seq Scan on tblksraw11 tblksraw  (cost=3D0.00..10.90 = rows=3D1 width=3D1186)

         =             &= nbsp;    Filter: ((ksrawsysid >=3D 1000000) AND (ksrawsysid <=3D = 1000020))

         =            ->  Seq Scan on tblksraw12 tblksraw  (cost=3D0.00..10.90 = rows=3D1 width=3D1186)

         =             &= nbsp;    Filter: ((ksrawsysid >=3D 1000000) AND (ksrawsysid <=3D = 1000020))

 

Form the query plans, it appears that I have to = explicitly create indices on the inherited tables. I would have assumed they would = have been inherited as well.

I expected that limiting the number of rows would = have resulted in an immediate result, whereas in actuality what appears to be happening is that the entire table is being processed as a result set, = but only 10 rows being returned.  This seems like = extraneous.

 

Regards,

 

Benjamin

------_=_NextPart_001_01C66959.DE2D27FE--