On 04/03/2011 16:07, Robert Haas wrote:
On Fri, Mar 4, 2011 at 6:40 AM, Mark Thornton<mthornton@xxxxxxxxxxxx> wrote:
I can achieve this manually by rewriting the query as a union between
queries against each of the child tables. Is there a better way? (I'm using
PostGreSQL 8.4 with PostGIS 1.4).
Can you post the EXPLAIN ANALYZE output of the other formulation of the query?
See below (at bottom)
That seems quite surprising. There are only 14 rows in the table but
PG thinks 2140? Do you have autovacuum turned on? Has this table
been analyzed recently?
It is a temporary table and thus I hadn't thought to analyze it. How
should such tables be treated? Should I analyze it immediately after
creation (i.e. when it is empty), after filling it or ... ? The expected
usage is such that the temporary table will have less than 100 or so rows.
However I now find that if I do analyze it I get the better result (plan
immediatley below). Curiously this result only applies to the inherited
(child table) formulation and not to the apparently equivalent query
over a view of unions. The view of unions is the approach used with SQL
Server 2008 .
Thanks for your help,
Mark Thornton
QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Nested Loop (cost=0.00..1442.57 rows=218 width=683) (actual
time=0.193..1.287 rows=14 loops=1)
Join Filter: ("RoadLinkInformation".roadlinkid = links.featureid)
-> Nested Loop (cost=0.00..118.19 rows=14 width=116) (actual
time=0.044..0.200 rows=14 loops=1)
-> Seq Scan on linkids (cost=0.00..1.14 rows=14 width=8)
(actual time=0.011..0.020 rows=14 loops=1)
-> Index Scan using "RoadLinkInformation_pkey" on
"RoadLinkInformation" (cost=0.00..8.35 rows=1 width=116) (actual
time=0.009..0.010 rows=1 loops=14)
Index Cond: ("RoadLinkInformation".rriid =
linkids.featureid)
-> Append (cost=0.00..84.03 rows=839 width=46) (actual
time=0.051..0.061 rows=1 loops=14)
-> Seq Scan on "MasterRoadLinks" links (cost=0.00..18.30
rows=830 width=40) (actual time=0.000..0.000 rows=0 loops=14)
-> Index Scan using "RoadLinks/A Road_pkey" on "RoadLinks/A
Road" links (cost=0.00..7.36 rows=1 width=519) (actual
time=0.007..0.007 rows=0 loops=14)
Index Cond: (links.featureid =
"RoadLinkInformation".roadlinkid)
-> Index Scan using "RoadLinks/B Road_pkey" on "RoadLinks/B
Road" links (cost=0.00..7.26 rows=1 width=587) (actual
time=0.006..0.006 rows=0 loops=14)
Index Cond: (links.featureid =
"RoadLinkInformation".roadlinkid)
-> Index Scan using "RoadLinks/Alley_pkey" on
"RoadLinks/Alley" links (cost=0.00..7.24 rows=1 width=353) (actual
time=0.005..0.005 rows=0 loops=14)
Index Cond: (links.featureid =
"RoadLinkInformation".roadlinkid)
-> Index Scan using "RoadLinks/Local Street_pkey" on
"RoadLinks/Local Street" links (cost=0.00..7.67 rows=1 width=450)
(actual time=0.008..0.008 rows=0 loops=14)
Index Cond: (links.featureid =
"RoadLinkInformation".roadlinkid)
-> Index Scan using "RoadLinks/Minor Road_pkey" on
"RoadLinks/Minor Road" links (cost=0.00..7.37 rows=1 width=784) (actual
time=0.007..0.007 rows=0 loops=14)
Index Cond: (links.featureid =
"RoadLinkInformation".roadlinkid)
-> Index Scan using "RoadLinks/Motorway_pkey" on
"RoadLinks/Motorway" links (cost=0.00..7.18 rows=1 width=820) (actual
time=0.005..0.005 rows=0 loops=14)
Index Cond: (links.featureid =
"RoadLinkInformation".roadlinkid)
-> Index Scan using "RoadLinks/Pedestrianised Street_pkey" on
"RoadLinks/Pedestrianised Street" links (cost=0.00..7.08 rows=1
width=399) (actual time=0.004..0.004 rows=0 loops=14)
Index Cond: (links.featureid =
"RoadLinkInformation".roadlinkid)
-> Index Scan using "RoadLinks/Private Road - Publicly
Accessible_pkey" on "RoadLinks/Private Road - Publicly Accessible"
links (cost=0.00..7.23 rows=1 width=662) (actual time=0.005..0.005
rows=0 loops=14)
Index Cond: (links.featureid =
"RoadLinkInformation".roadlinkid)
-> Index Scan using "RoadLinks/Private Road - Restricted
Access_pkey" on "RoadLinks/Private Road - Restricted Access" links
(cost=0.00..7.35 rows=1 width=855) (actual time=0.008..0.009 rows=1
loops=14)
Index Cond: (links.featureid =
"RoadLinkInformation".roadlinkid)
Total runtime: 1.518 ms
(27 rows)
Query plan with alternative query:
QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Append (cost=0.00..168893.14 rows=11237 width=723) (actual
time=0.934..234.609 rows=14 loops=1)
-> Nested Loop (cost=0.00..22222.78 rows=2140 width=619) (actual
time=0.291..0.291 rows=0 loops=1)
-> Nested Loop (cost=0.00..13811.00 rows=2140 width=116)
(actual time=0.049..0.181 rows=14 loops=1)
-> Seq Scan on linkids (cost=0.00..31.40 rows=2140
width=8) (actual time=0.013..0.018 rows=14 loops=1)
-> Index Scan using "RoadLinkInformation_pkey" on
"RoadLinkInformation" (cost=0.00..6.43 rows=1 width=116) (actual
time=0.008..0.009 rows=1 loops=14)
Index Cond: (public."RoadLinkInformation".rriid =
pg_temp_1.linkids.featureid)
-> Index Scan using "RoadLinks/A Road_pkey" on "RoadLinks/A
Road" links (cost=0.00..3.91 rows=1 width=519) (actual
time=0.007..0.007 rows=0 loops=14)
Index Cond: (links.featureid =
public."RoadLinkInformation".roadlinkid)
-> Nested Loop (cost=0.00..14729.95 rows=1472 width=687) (actual
time=0.231..0.231 rows=0 loops=1)
-> Nested Loop (cost=0.00..13895.00 rows=2140 width=116)
(actual time=0.013..0.134 rows=14 loops=1)
-> Seq Scan on linkids (cost=0.00..31.40 rows=2140
width=8) (actual time=0.003..0.011 rows=14 loops=1)
-> Index Scan using "RoadLinkInformation_pkey" on
"RoadLinkInformation" (cost=0.00..6.47 rows=1 width=116) (actual
time=0.006..0.007 rows=1 loops=14)
Index Cond: (public."RoadLinkInformation".rriid =
pg_temp_1.linkids.featureid)
-> Index Scan using "RoadLinks/B Road_pkey" on "RoadLinks/B
Road" links (cost=0.00..0.37 rows=1 width=587) (actual
time=0.006..0.006 rows=0 loops=14)
Index Cond: (links.featureid =
public."RoadLinkInformation".roadlinkid)
-> Nested Loop (cost=0.00..14604.60 rows=811 width=453) (actual
time=0.215..0.215 rows=0 loops=1)
-> Nested Loop (cost=0.00..13895.00 rows=2140 width=116)
(actual time=0.011..0.127 rows=14 loops=1)
-> Seq Scan on linkids (cost=0.00..31.40 rows=2140
width=8) (actual time=0.003..0.009 rows=14 loops=1)
-> Index Scan using "RoadLinkInformation_pkey" on
"RoadLinkInformation" (cost=0.00..6.47 rows=1 width=116) (actual
time=0.006..0.007 rows=1 loops=14)
Index Cond: (public."RoadLinkInformation".rriid =
pg_temp_1.linkids.featureid)
-> Index Scan using "RoadLinks/Alley_pkey" on
"RoadLinks/Alley" links (cost=0.00..0.32 rows=1 width=353) (actual
time=0.005..0.005 rows=0 loops=14)
Index Cond: (links.featureid =
public."RoadLinkInformation".roadlinkid)
-> Nested Loop (cost=0.00..28899.43 rows=2140 width=550) (actual
time=0.194..0.323 rows=4 loops=1)
-> Nested Loop (cost=0.00..14767.00 rows=2140 width=116)
(actual time=0.011..0.136 rows=14 loops=1)
-> Seq Scan on linkids (cost=0.00..31.40 rows=2140
width=8) (actual time=0.003..0.010 rows=14 loops=1)
-> Index Scan using "RoadLinkInformation_pkey" on
"RoadLinkInformation" (cost=0.00..6.87 rows=1 width=116) (actual
time=0.006..0.007 rows=1 loops=14)
Index Cond: (public."RoadLinkInformation".rriid =
pg_temp_1.linkids.featureid)
-> Index Scan using "RoadLinks/Local Street_pkey" on
"RoadLinks/Local Street" links (cost=0.00..6.59 rows=1 width=450)
(actual time=0.007..0.007 rows=0 loops=14)
Index Cond: (links.featureid =
public."RoadLinkInformation".roadlinkid)
-> Nested Loop (cost=0.00..23803.64 rows=2140 width=884) (actual
time=0.228..0.228 rows=0 loops=1)
-> Nested Loop (cost=0.00..13939.00 rows=2140 width=116)
(actual time=0.012..0.126 rows=14 loops=1)
-> Seq Scan on linkids (cost=0.00..31.40 rows=2140
width=8) (actual time=0.004..0.010 rows=14 loops=1)
-> Index Scan using "RoadLinkInformation_pkey" on
"RoadLinkInformation" (cost=0.00..6.49 rows=1 width=116) (actual
time=0.006..0.006 rows=1 loops=14)
Index Cond: (public."RoadLinkInformation".rriid =
pg_temp_1.linkids.featureid)
-> Index Scan using "RoadLinks/Minor Road_pkey" on
"RoadLinks/Minor Road" links (cost=0.00..4.59 rows=1 width=784) (actual
time=0.006..0.006 rows=0 loops=14)
Index Cond: (links.featureid =
public."RoadLinkInformation".roadlinkid)
-> Nested Loop (cost=0.00..14517.88 rows=124 width=920) (actual
time=0.390..0.390 rows=0 loops=1)
-> Nested Loop (cost=0.00..13895.00 rows=2140 width=116)
(actual time=0.011..0.142 rows=14 loops=1)
-> Seq Scan on linkids (cost=0.00..31.40 rows=2140
width=8) (actual time=0.003..0.012 rows=14 loops=1)
-> Index Scan using "RoadLinkInformation_pkey" on
"RoadLinkInformation" (cost=0.00..6.47 rows=1 width=116) (actual
time=0.006..0.007 rows=1 loops=14)
Index Cond: (public."RoadLinkInformation".rriid =
pg_temp_1.linkids.featureid)
-> Index Scan using "RoadLinks/Motorway_pkey" on
"RoadLinks/Motorway" links (cost=0.00..0.28 rows=1 width=820) (actual
time=0.005..0.005 rows=0 loops=14)
Index Cond: (links.featureid =
public."RoadLinkInformation".roadlinkid)
-> Hash Join (cost=12418.00..12457.78 rows=24 width=499) (actual
time=232.495..232.495 rows=0 loops=1)
Hash Cond: (pg_temp_1.linkids.featureid =
public."RoadLinkInformation".rriid)
-> Seq Scan on linkids (cost=0.00..31.40 rows=2140 width=8)
(actual time=0.007..0.012 rows=14 loops=1)
-> Hash (cost=12377.61..12377.61 rows=3231 width=499)
(actual time=232.421..232.421 rows=1125 loops=1)
-> Hash Join (cost=130.34..12377.61 rows=3231
width=499) (actual time=11.572..230.975 rows=1125 loops=1)
Hash Cond:
(public."RoadLinkInformation".roadlinkid = links.featureid)
-> Seq Scan on "RoadLinkInformation"
(cost=0.00..10422.28 rows=286828 width=116) (actual time=4.306..81.587
rows=286828 loops=1)
-> Hash (cost=92.93..92.93 rows=2993 width=399)
(actual time=7.029..7.029 rows=2993 loops=1)
-> Seq Scan on "RoadLinks/Pedestrianised
Street" links (cost=0.00..92.93 rows=2993 width=399) (actual
time=0.014..3.100 rows=2993 loops=1)
-> Nested Loop (cost=0.00..14535.03 rows=246 width=762) (actual
time=0.168..0.168 rows=0 loops=1)
-> Nested Loop (cost=0.00..13895.00 rows=2140 width=116)
(actual time=0.031..0.113 rows=14 loops=1)
-> Seq Scan on linkids (cost=0.00..31.40 rows=2140
width=8) (actual time=0.005..0.007 rows=14 loops=1)
-> Index Scan using "RoadLinkInformation_pkey" on
"RoadLinkInformation" (cost=0.00..6.47 rows=1 width=116) (actual
time=0.005..0.006 rows=1 loops=14)
Index Cond: (public."RoadLinkInformation".rriid =
pg_temp_1.linkids.featureid)
-> Index Scan using "RoadLinks/Private Road - Publicly
Accessible_pkey" on "RoadLinks/Private Road - Publicly Accessible"
links (cost=0.00..0.29 rows=1 width=662) (actual time=0.003..0.003
rows=0 loops=14)
Index Cond: (links.featureid =
public."RoadLinkInformation".roadlinkid)
-> Nested Loop (cost=0.00..23009.69 rows=2140 width=955) (actual
time=0.058..0.256 rows=10 loops=1)
-> Nested Loop (cost=0.00..13871.00 rows=2140 width=116)
(actual time=0.009..0.090 rows=14 loops=1)
-> Seq Scan on linkids (cost=0.00..31.40 rows=2140
width=8) (actual time=0.003..0.006 rows=14 loops=1)
-> Index Scan using "RoadLinkInformation_pkey" on
"RoadLinkInformation" (cost=0.00..6.45 rows=1 width=116) (actual
time=0.004..0.004 rows=1 loops=14)
Index Cond: (public."RoadLinkInformation".rriid =
pg_temp_1.linkids.featureid)
-> Index Scan using "RoadLinks/Private Road - Restricted
Access_pkey" on "RoadLinks/Private Road - Restricted Access" links
(cost=0.00..4.25 rows=1 width=855) (actual time=0.005..0.005 rows=1
loops=14)
Index Cond: (links.featureid =
public."RoadLinkInformation".roadlinkid)
Total runtime: 235.501 ms
(67 rows)
The alternative query
select rriID,ST_AsBinary(centreLine),ST_AsBinary(point)
from "RoadLinkInformation" join LinkIds on
"RoadLinkInformation".rriID=LinkIds.featureid
join "RoadLinks/A Road" as Links on
"RoadLinkInformation".roadLinkID=Links.featureID
union all
select rriID,ST_AsBinary(centreLine),ST_AsBinary(point)
from "RoadLinkInformation" join LinkIds on
"RoadLinkInformation".rriID=LinkIds.featureid
join "RoadLinks/B Road" as Links on
"RoadLinkInformation".roadLinkID=Links.featureID
union all
select rriID,ST_AsBinary(centreLine),ST_AsBinary(point)
from "RoadLinkInformation" join LinkIds on
"RoadLinkInformation".rriID=LinkIds.featureid
join "RoadLinks/Alley" as Links on
"RoadLinkInformation".roadLinkID=Links.featureID
union all
select rriID,ST_AsBinary(centreLine),ST_AsBinary(point)
from "RoadLinkInformation" join LinkIds on
"RoadLinkInformation".rriID=LinkIds.featureid
join "RoadLinks/Local Street" as Links on
"RoadLinkInformation".roadLinkID=Links.featureID
union all
select rriID,ST_AsBinary(centreLine),ST_AsBinary(point)
from "RoadLinkInformation" join LinkIds on
"RoadLinkInformation".rriID=LinkIds.featureid
join "RoadLinks/Minor Road" as Links on
"RoadLinkInformation".roadLinkID=Links.featureID
union all
select rriID,ST_AsBinary(centreLine),ST_AsBinary(point)
from "RoadLinkInformation" join LinkIds on
"RoadLinkInformation".rriID=LinkIds.featureid
join "RoadLinks/Motorway" as Links on
"RoadLinkInformation".roadLinkID=Links.featureID
union all
select rriID,ST_AsBinary(centreLine),ST_AsBinary(point)
from "RoadLinkInformation" join LinkIds on
"RoadLinkInformation".rriID=LinkIds.featureid
join "RoadLinks/Pedestrianised Street" as Links on
"RoadLinkInformation".roadLinkID=Links.featureID
union all
select rriID,ST_AsBinary(centreLine),ST_AsBinary(point)
from "RoadLinkInformation" join LinkIds on
"RoadLinkInformation".rriID=LinkIds.featureid
join "RoadLinks/Private Road - Publicly Accessible" as
Links on "RoadLinkInformation".roadLinkID=Links.featureID
union all
select rriID,ST_AsBinary(centreLine),ST_AsBinary(point)
from "RoadLinkInformation" join LinkIds on
"RoadLinkInformation".rriID=LinkIds.featureid
join "RoadLinks/Private Road - Restricted Access" as
Links on "RoadLinkInformation".roadLinkID=Links.featureID
--
Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
