Re: Slow join on partitioned table

On 04/03/2011 16:07, Robert Haas wrote:
> On Fri, Mar 4, 2011 at 6:40 AM, Mark Thornton<mthornton(at)optrak(dot)co(dot)uk> 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