|
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 |
