Tale partitioning - Mailing list pgsql-admin

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=5351703.70..5351703.72 rows=10 width=1186)

  ->  Sort  (cost=5351703.70..5355948.36 rows=1697865 width=1186)

        Sort Key: public.tblksraw.ksrawsysid

        ->  Result  (cost=0.00..108314.65 rows=1697865 width=1186)

              ->  Append  (cost=0.00..108314.65 rows=1697865 width=1186)

                    ->  Seq Scan on tblksraw  (cost=0.00..108187.45 rows=1697145 width=255)

                    ->  Seq Scan on tblksraw01 tblksraw  (cost=0.00..10.60 rows=60 width=1186)

                    ->  Seq Scan on tblksraw02 tblksraw  (cost=0.00..10.60 rows=60 width=1186)

                    ->  Seq Scan on tblksraw03 tblksraw  (cost=0.00..10.60 rows=60 width=1186)

                    ->  Seq Scan on tblksraw04 tblksraw  (cost=0.00..10.60 rows=60 width=1186)

                    ->  Seq Scan on tblksraw05 tblksraw  (cost=0.00..10.60 rows=60 width=1186)

                    ->  Seq Scan on tblksraw06 tblksraw  (cost=0.00..10.60 rows=60 width=1186)

                    ->  Seq Scan on tblksraw07 tblksraw  (cost=0.00..10.60 rows=60 width=1186)

                    ->  Seq Scan on tblksraw08 tblksraw  (cost=0.00..10.60 rows=60 width=1186)

                    ->  Seq Scan on tblksraw09 tblksraw  (cost=0.00..10.60 rows=60 width=1186)

                    ->  Seq Scan on tblksraw10 tblksraw  (cost=0.00..10.60 rows=60 width=1186)

                    ->  Seq Scan on tblksraw11 tblksraw  (cost=0.00..10.60 rows=60 width=1186)

                    ->  Seq Scan on tblksraw12 tblksraw  (cost=0.00..10.60 rows=60 width=1186)

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=142.85..142.87 rows=10 width=1186)

  ->  Sort  (cost=142.85..142.93 rows=31 width=1186)

        Sort Key: public.tblksraw.ksrawsysid

        ->  Result  (cost=0.00..142.08 rows=31 width=1186)

              ->  Append  (cost=0.00..142.08 rows=31 width=1186)

                    ->  Index Scan using pk_tblksraw on tblksraw  (cost=0.00..11.28 rows=19 width=255)

                          Index Cond: ((ksrawsysid >= 1000000) AND (ksrawsysid <= 1000020))

                    ->  Seq Scan on tblksraw01 tblksraw  (cost=0.00..10.90 rows=1 width=1186)

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

                    ->  Seq Scan on tblksraw02 tblksraw  (cost=0.00..10.90 rows=1 width=1186)

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

                    ->  Seq Scan on tblksraw03 tblksraw  (cost=0.00..10.90 rows=1 width=1186)

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

                    ->  Seq Scan on tblksraw04 tblksraw  (cost=0.00..10.90 rows=1 width=1186)

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

                    ->  Seq Scan on tblksraw05 tblksraw  (cost=0.00..10.90 rows=1 width=1186)

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

                    ->  Seq Scan on tblksraw06 tblksraw  (cost=0.00..10.90 rows=1 width=1186)

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

                    ->  Seq Scan on tblksraw07 tblksraw  (cost=0.00..10.90 rows=1 width=1186)

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

                    ->  Seq Scan on tblksraw08 tblksraw  (cost=0.00..10.90 rows=1 width=1186)

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

                    ->  Seq Scan on tblksraw09 tblksraw  (cost=0.00..10.90 rows=1 width=1186)

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

                    ->  Seq Scan on tblksraw10 tblksraw  (cost=0.00..10.90 rows=1 width=1186)

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

                    ->  Seq Scan on tblksraw11 tblksraw  (cost=0.00..10.90 rows=1 width=1186)

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

                    ->  Seq Scan on tblksraw12 tblksraw  (cost=0.00..10.90 rows=1 width=1186)

                          Filter: ((ksrawsysid >= 1000000) AND (ksrawsysid <= 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