Re: Slow join on partitioned table - Mailing list pgsql-performance
From | Mark Thornton |
---|---|
Subject | Re: Slow join on partitioned table |
Date | |
Msg-id | 4D71179B.8040300@optrak.co.uk Whole thread Raw |
In response to | Re: Slow join on partitioned table (Robert Haas <robertmhaas@gmail.com>) |
Responses |
Re: Slow join on partitioned table
|
List | pgsql-performance |
On 04/03/2011 16:07, Robert Haas wrote: > On Fri, Mar 4, 2011 at 6:40 AM, Mark Thornton<mthornton@optrak.co.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
pgsql-performance by date: