It would appear that region_id = parent_id is not internally converted
to region_id = 1129, despite parent_id being enforced to 1129 at the top
level.
In this case, it makes a difference in performance of about 4 (2 minutes
vs 30 second).
The reason I didn't do this myself upfront, is that parent_id is
calculated by a function which I didn't want to call twice. I've split
the query into 2 parts as a result.
Plans attached from PostgreSQL 7.4.5.
QUERY:
SELECT region_id, region_title
FROM bric_extension.region_in_region
WHERE parent_id = 1129
AND class = (SELECT region_class
FROM region_classes
WHERE "order" >
(SELECT "order"
FROM region
JOIN region_classes
ON (region_class = class)
WHERE region_id = parent_id) -- 1129
ORDER BY "order"
LIMIT 1);
QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Subquery Scan region_in_region (cost=1455.18..1455.21 rows=1 width=45) (actual
time=27966.381..27966.400 rows=1 loops=1)
InitPlan
-> Limit (cost=1.65..1.65 rows=1 width=36) (actual time=1.449..1.453 rows=1
loops=1)
InitPlan
-> Hash Join (cost=1.06..1.32 rows=1 width=4) (actual time=0.598..0.931
rows=1 loops=1) Hash Cond: ("outer".region_class =
("inner"."class")::text)
-> Subquery Scan region_classes (cost=0.00..0.21 rows=7 width=36)
(actual time=0.121..0.549 rows=7 loops=1)
-> Append (cost=0.00..0.14 rows=7 width=0) (actual
time=0.103..0.452 rows=7 loops=1)
-> Subquery Scan "*SELECT* 1" (cost=0.00..0.02 rows=1
width=0) (actual time=0.092..0.106 rows=1 loops=1)
-> Result (cost=0.00..0.01 rows=1 width=0)
(actual time=0.072..0.076 rows=1 loops=1)
-> Subquery Scan "*SELECT* 2" (cost=0.00..0.02 rows=1
width=0) (actual time=0.027..0.041 rows=1 loops=1)
-> Result (cost=0.00..0.01 rows=1 width=0)
(actual time=0.012..0.016 rows=1 loops=1)
-> Subquery Scan "*SELECT* 3" (cost=0.00..0.02 rows=1
width=0) (actual time=0.026..0.041 rows=1 loops=1)
-> Result (cost=0.00..0.01 rows=1 width=0)
(actual time=0.010..0.015 rows=1 loops=1)
-> Subquery Scan "*SELECT* 4" (cost=0.00..0.02 rows=1
width=0) (actual time=0.026..0.039 rows=1 loops=1)
-> Result (cost=0.00..0.01 rows=1 width=0)
(actual time=0.011..0.016 rows=1 loops=1)
-> Subquery Scan "*SELECT* 5" (cost=0.00..0.02 rows=1
width=0) (actual time=0.027..0.041 rows=1 loops=1)
-> Result (cost=0.00..0.01 rows=1 width=0)
(actual time=0.012..0.017 rows=1 loops=1)
-> Subquery Scan "*SELECT* 6" (cost=0.00..0.02 rows=1
width=0) (actual time=0.026..0.040 rows=1 loops=1)
-> Result (cost=0.00..0.01 rows=1 width=0)
(actual time=0.012..0.017 rows=1 loops=1)
-> Subquery Scan "*SELECT* 7" (cost=0.00..0.02 rows=1
width=0) (actual time=0.025..0.039 rows=1 loops=1)
-> Result (cost=0.00..0.01 rows=1 width=0)
(actual time=0.012..0.016 rows=1 loops=1)
-> Hash (cost=1.06..1.06 rows=1 width=14) (actual
time=0.125..0.125 rows=0 loops=1)
-> Seq Scan on region (cost=0.00..1.06 rows=1 width=14)
(actual time=0.065..0.097 rows=1 loops=1)
Filter: (region_id = 1129::numeric)
-> Sort (cost=0.33..0.34 rows=7 width=36) (actual time=1.434..1.434
rows=1 loops=1)
Sort Key: "order"
-> Subquery Scan region_classes (cost=0.00..0.23 rows=7 width=36)
(actual time=1.102..1.364 rows=4 loops=1)
-> Append (cost=0.00..0.16 rows=7 width=0) (actual
time=1.083..1.300 rows=4 loops=1)
-> Subquery Scan "*SELECT* 1" (cost=0.00..0.02 rows=1
width=0) (actual
time=0.976..0.976 rows=0 loops=1)
-> Result (cost=0.00..0.01 rows=1 width=0)
(actual time=0.963..0.963 rows=0 loops=1)
One-Time Filter: (10 > $0)
-> Subquery Scan "*SELECT* 2" (cost=0.00..0.02 rows=1
width=0) (actual
time=0.025..0.025 rows=0 loops=1)
-> Result (cost=0.00..0.01 rows=1 width=0)
(actual time=0.014..0.014 rows=0 loops=1)
One-Time Filter: (20 > $0)
-> Subquery Scan "*SELECT* 3" (cost=0.00..0.02 rows=1
width=0) (actual
time=0.021..0.021 rows=0 loops=1)
-> Result (cost=0.00..0.01 rows=1 width=0)
(actual time=0.010..0.010 rows=0 loops=1)
One-Time Filter: (30 > $0)
-> Subquery Scan "*SELECT* 4" (cost=0.00..0.02 rows=1
width=0) (actual
time=0.033..0.047 rows=1 loops=1)
-> Result (cost=0.00..0.01 rows=1 width=0)
(actual time=0.017..0.022 rows=1 loops=1)
One-Time Filter: (40 > $0)
-> Subquery Scan "*SELECT* 5" (cost=0.00..0.02 rows=1
width=0) (actual
time=0.035..0.050 rows=1 loops=1)
-> Result (cost=0.00..0.01 rows=1 width=0)
(actual time=0.019..0.024 rows=1 loops=1)
One-Time Filter: (50 > $0)
-> Subquery Scan "*SELECT* 6" (cost=0.00..0.02 rows=1
width=0) (actual
time=0.033..0.048 rows=1 loops=1)
-> Result (cost=0.00..0.01 rows=1 width=0)
(actual time=0.017..0.022 rows=1 loops=1)
One-Time Filter: (60 > $0)
-> Subquery Scan "*SELECT* 7" (cost=0.00..0.02 rows=1
width=0) (actual
time=0.035..0.049 rows=1 loops=1)
-> Result (cost=0.00..0.01 rows=1 width=0)
(actual time=0.018..0.023 rows=1 loops=1)
One-Time Filter: (70 > $0)
-> Group (cost=1453.53..1453.55 rows=1 width=68) (actual
time=27966.350..27966.356 rows=1 loops=1)
-> Sort (cost=1453.53..1453.54 rows=1 width=68) (actual
time=27965.006..27965.345 rows=67 loops=1)
Sort Key: bric_extension.region.region_id,
bric_extension.region.region_title, (bric_extension.region."class")::text,
bric_extension.region.region_id, bric_extension.region.region_title,
(bric_extension.region."class")::text
-> Nested Loop (cost=0.00..1453.52 rows=1 width=68) (actual
time=126.819..27963.077 rows=67 loops=1)
Join Filter: ((COALESCE("outer".geom, "outer".geom) @
COALESCE("inner".geom, "inner".geom)) AND ("outer".region_id <> "inner".region_id))
-> Nested Loop Left Join (cost=0.00..726.75 rows=1 width=410)
(actual time=3.573..464.620 rows=33 loops=1)
Join Filter: ("outer".region_id = "inner".region_id)
-> Nested Loop Left Join (cost=0.00..725.73 rows=1
width=66) (actual time=3.443..453.466 rows=33 loops=1)
Join Filter: (("outer".name = ("inner".name)::text)
AND (("outer"."class")::text = "inner"."class"))
-> Nested Loop Left Join (cost=0.00..2.15 rows=1
width=43) (actual
time=1.704..1.800 rows=1 loops=1)
Join Filter: ("outer".region_id =
"inner".region_id)
-> Seq Scan on region (cost=0.00..1.06 rows=1
width=34) (actual time=1.557..1.584 rows=1 loops=1)
Filter: (("class")::text = $1)
-> Seq Scan on region_import (cost=0.00..1.04
rows=4 width=19) (actual time=0.022..0.056 rows=4 loops=1)
-> Subquery Scan all_imported (cost=0.00..649.48
rows=4940 width=96) (actual time=0.151..407.063 rows=4920 loops=1)
-> Append (cost=0.00..600.08 rows=4940
width=720) (actual time=0.131..328.703 rows=4920 loops=1)
-> Subquery Scan "*SELECT* 1"
(cost=0.00..194.63 rows=1292 width=639) (actual time=0.121..70.803 rows=1294 loops=1)
-> Seq Scan on "north_am_v1.0"
(cost=0.00..181.71 rows=1292 width=639) (actual time=0.100..47.057 rows=1294 loops=1)
Filter: (((name)::text <>
'water/agua/d\'eau'::text) AND (stateabb IS NOT NULL))
-> Subquery Scan "*SELECT* 2"
(cost=0.00..199.84 rows=1813 width=627) (actual time=0.121..103.195 rows=1802 loops=1)
-> Seq Scan on "north_am_v1.0"
(cost=0.00..181.71 rows=1813 width=627) (actual time=0.096..72.714 rows=1802 loops=1)
Filter: ((name)::text <>
'water/agua/d\'eau'::text)
-> Subquery Scan "*SELECT* 3"
(cost=0.00..199.84 rows=1813 width=621) (actual time=0.122..100.573 rows=1802 loops=1)
-> Seq Scan on "north_am_v1.0"
(cost=0.00..181.71 rows=1813 width=621) (actual time=0.098..69.127 rows=1802 loops=1)
Filter: ((name)::text <>
'water/agua/d\'eau'::text)
-> Subquery Scan "*SELECT* 4"
(cost=0.00..0.02 rows=1 width=0) (actual time=0.100..0.114 rows=1 loops=1)
-> Result (cost=0.00..0.01 rows=1
width=0) (actual time=0.079..0.083 rows=1 loops=1)
-> Subquery Scan "*SELECT* 5"
(cost=0.00..5.74 rows=21
width=720) (actual time=0.100..1.398 rows=21 loops=1)
-> Seq Scan on oncity_2m_prod
(cost=0.00..5.53 rows=21 width=720) (actual time=0.074..0.997 rows=21 loops=1)
Filter: (name IS NOT NULL)
-> Seq Scan on region_lookup (cost=0.00..1.01 rows=1
width=354) (actual time=0.042..0.052 rows=1 loops=33)
-> Nested Loop Left Join (cost=0.00..726.75 rows=1 width=410)
(actual time=118.512..597.403 rows=712 loops=33)
Join Filter: ("outer".region_id = "inner".region_id)
-> Nested Loop Left Join (cost=0.00..725.73 rows=1
width=66) (actual time=118.197..475.212 rows=712 loops=33)
Join Filter: (("outer".name = ("inner".name)::text)
AND (("outer"."class")::text = "inner"."class"))
-> Nested Loop Left Join (cost=0.00..2.15 rows=1
width=43) (actual
time=0.173..0.329 rows=1 loops=33)
Join Filter: ("outer".region_id =
"inner".region_id)
-> Seq Scan on region (cost=0.00..1.06 rows=1
width=34) (actual time=0.042..0.106 rows=1 loops=33)
Filter: (region_id = 1129::numeric)
-> Seq Scan on region_import (cost=0.00..1.04
rows=4 width=19) (actual time=0.016..0.050 rows=4 loops=33)
-> Subquery Scan all_imported (cost=0.00..649.48
rows=4940 width=96) (actual time=0.212..418.636 rows=4920 loops=33)
-> Append (cost=0.00..600.08 rows=4940
width=720) (actual time=0.192..337.362 rows=4920 loops=33)
-> Subquery Scan "*SELECT* 1"
(cost=0.00..194.63 rows=1292 width=639) (actual time=0.182..71.460 rows=1294 loops=33)
-> Seq Scan on "north_am_v1.0"
(cost=0.00..181.71 rows=1292 width=639) (actual time=0.162..48.908 rows=1294 loops=33)
Filter: (((name)::text <>
'water/agua/d\'eau'::text) AND (stateabb IS NOT NULL))
-> Subquery Scan "*SELECT* 2"
(cost=0.00..199.84 rows=1813 width=627) (actual time=0.105..115.662 rows=1802 loops=33)
-> Seq Scan on "north_am_v1.0"
(cost=0.00..181.71 rows=1813 width=627) (actual time=0.077..80.654 rows=1802 loops=33)
Filter: ((name)::text <>
'water/agua/d\'eau'::text)
-> Subquery Scan "*SELECT* 3"
(cost=0.00..199.84 rows=1813 width=621) (actual time=0.109..97.106 rows=1802 loops=33)
-> Seq Scan on "north_am_v1.0"
(cost=0.00..181.71 rows=1813 width=621) (actual time=0.082..67.252 rows=1802 loops=33)
Filter: ((name)::text <>
'water/agua/d\'eau'::text)
-> Subquery Scan "*SELECT* 4"
(cost=0.00..0.02 rows=1 width=0) (actual time=0.251..0.267 rows=1 loops=33)
-> Result (cost=0.00..0.01 rows=1
width=0) (actual time=0.224..0.229 rows=1 loops=33)
-> Subquery Scan "*SELECT* 5"
(cost=0.00..5.74 rows=21
width=720) (actual time=0.111..1.279 rows=21 loops=33)
-> Seq Scan on oncity_2m_prod
(cost=0.00..5.53 rows=21 width=720) (actual time=0.075..0.860 rows=21 loops=33)
Filter: (name IS NOT NULL)
-> Seq Scan on region_lookup (cost=0.00..1.01 rows=1
width=354) (actual time=0.031..0.041 rows=1 loops=23496)
QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Subquery Scan region_in_region (cost=1719.94..1721.62 rows=1 width=45) (actual
time=122063.674..122065.316 rows=1 loops=1)
Filter: ("class" = (subplan))
-> Group (cost=1719.94..1719.96 rows=1 width=68) (actual
time=122057.842..122059.262 rows=3 loops=1)
-> Sort (cost=1719.94..1719.95 rows=1 width=68) (actual
time=122057.667..122058.001 rows=70 loops=1)
Sort Key: bric_extension.region.region_id,
bric_extension.region.region_title, (bric_extension.region."class")::text,
bric_extension.region.region_id, bric_extension.region.region_title,
(bric_extension.region."class")::text
-> Nested Loop (cost=993.06..1719.93 rows=1 width=68) (actual
time=6820.956..122055.657 rows=70 loops=1)
Join Filter: ((COALESCE("inner".geom, "inner".geom) @
COALESCE("outer".geom, "outer".geom)) AND ("inner".region_id <> "outer".region_id))
-> Nested Loop Left Join (cost=0.00..726.75 rows=1 width=410)
(actual time=120.044..815.785 rows=712 loops=1)
Join Filter: ("outer".region_id = "inner".region_id)
-> Nested Loop Left Join (cost=0.00..725.73 rows=1
width=66) (actual time=119.837..592.618 rows=712 loops=1)
Join Filter: (("outer".name = ("inner".name)::text)
AND (("outer"."class")::text = "inner"."class"))
-> Nested Loop Left Join (cost=0.00..2.15 rows=1
width=43) (actual
time=0.189..0.308 rows=1 loops=1)
Join Filter: ("outer".region_id =
"inner".region_id)
-> Seq Scan on region (cost=0.00..1.06 rows=1
width=34) (actual time=0.086..0.120 rows=1 loops=1)
Filter: (region_id = 1129::numeric)
-> Seq Scan on region_import (cost=0.00..1.04
rows=4 width=19) (actual time=0.015..0.047 rows=4 loops=1)
-> Subquery Scan all_imported (cost=0.00..649.48
rows=4940 width=96) (actual time=0.156..521.893 rows=4920 loops=1)
-> Append (cost=0.00..600.08 rows=4940
width=720) (actual time=0.138..431.425 rows=4920 loops=1)
-> Subquery Scan "*SELECT* 1"
(cost=0.00..194.63 rows=1292 width=639) (actual time=0.126..73.643 rows=1294 loops=1)
-> Seq Scan on "north_am_v1.0"
(cost=0.00..181.71 rows=1292 width=639) (actual time=0.104..49.796 rows=1294 loops=1)
Filter: (((name)::text <>
'water/agua/d\'eau'::text) AND (stateabb IS NOT NULL))
-> Subquery Scan "*SELECT* 2"
(cost=0.00..199.84 rows=1813 width=627) (actual time=0.140..209.442 rows=1802 loops=1)
-> Seq Scan on "north_am_v1.0"
(cost=0.00..181.71 rows=1813 width=627) (actual time=0.113..162.602 rows=1802 loops=1)
Filter: ((name)::text <>
'water/agua/d\'eau'::text)
-> Subquery Scan "*SELECT* 3"
(cost=0.00..199.84 rows=1813 width=621) (actual time=0.133..96.078 rows=1802 loops=1)
-> Seq Scan on "north_am_v1.0"
(cost=0.00..181.71 rows=1813 width=621) (actual time=0.109..64.625 rows=1802 loops=1)
Filter: ((name)::text <>
'water/agua/d\'eau'::text)
-> Subquery Scan "*SELECT* 4"
(cost=0.00..0.02 rows=1 width=0) (actual time=0.098..0.113 rows=1 loops=1)
-> Result (cost=0.00..0.01 rows=1
width=0) (actual time=0.077..0.082 rows=1 loops=1)
-> Subquery Scan "*SELECT* 5"
(cost=0.00..5.74 rows=21
width=720) (actual time=0.102..1.182 rows=21 loops=1)
-> Seq Scan on oncity_2m_prod
(cost=0.00..5.53 rows=21 width=720) (actual time=0.075..0.797 rows=21 loops=1)
Filter: (name IS NOT NULL)
-> Seq Scan on region_lookup (cost=0.00..1.01 rows=1
width=354) (actual time=0.055..0.066 rows=1 loops=712)
-> Merge Left Join (cost=993.06..993.10 rows=5 width=410)
(actual time=1.145..24.804 rows=749 loops=712)
Merge Cond: ("outer".region_id = "inner".region_id)
-> Sort (cost=992.04..992.05 rows=5 width=66) (actual
time=1.090..5.550 rows=749 loops=712)
Sort Key: bric_extension.region.region_id
-> Merge Left Join (cost=954.88..991.98 rows=5
width=66) (actual time=686.311..753.800 rows=749 loops=1)
Merge Cond: (("outer"."?column5?" =
"inner"."class") AND ("outer".name = "inner"."?column4?"))
-> Sort (cost=2.33..2.34 rows=5 width=43)
(actual time=0.684..0.713 rows=5 loops=1)
Sort Key:
(bric_extension.region."class")::text, bric_extension.region_import.name
-> Merge Left Join (cost=2.19..2.27
rows=5 width=43) (actual time=0.398..0.599 rows=5 loops=1)
Merge Cond: ("outer".region_id =
"inner".region_id) -> Sort
(cost=1.11..1.12 rows=5 width=34) (actual time=0.191..0.217 rows=5 loops=1)
Sort Key:
bric_extension.region.region_id
-> Seq Scan on region
(cost=0.00..1.05 rows=5 width=34) (actual time=0.038..0.094 rows=5 loops=1)
-> Sort (cost=1.08..1.09 rows=4
width=19) (actual time=0.116..0.137 rows=4 loops=1)
Sort Key:
bric_extension.region_import.region_id
-> Seq Scan on region_import
(cost=0.00..1.04 rows=4 width=19) (actual time=0.021..0.051 rows=4 loops=1)
-> Sort (cost=952.55..964.90 rows=4940
width=96) (actual time=662.723..686.980 rows=4920 loops=1)
Sort Key: all_imported."class",
(all_imported.name)::text -> Subquery
Scan all_imported (cost=0.00..649.48 rows=4940 width=96) (actual time=0.211..508.662
rows=4920 loops=1)
-> Append (cost=0.00..600.08
rows=4940 width=720) (actual time=0.175..361.006 rows=4920 loops=1)
-> Subquery Scan "*SELECT*
1" (cost=0.00..194.63 rows=1292 width=639) (actual time=0.162..80.308 rows=1294
loops=1)
-> Seq Scan on
"north_am_v1.0" (cost=0.00..181.71 rows=1292 width=639) (actual time=0.124..58.949
rows=1294 loops=1)
Filter:
(((name)::text <> 'water/agua/d\'eau'::text) AND (stateabb IS NOT NULL))
-> Subquery Scan "*SELECT*
2" (cost=0.00..199.84 rows=1813 width=627) (actual time=0.123..111.029 rows=1802
loops=1)
-> Seq Scan on
"north_am_v1.0" (cost=0.00..181.71 rows=1813 width=627) (actual time=0.098..77.918
rows=1802 loops=1)
Filter:
((name)::text <> 'water/agua/d\'eau'::text)
-> Subquery Scan "*SELECT*
3" (cost=0.00..199.84 rows=1813 width=621) (actual time=0.169..114.539 rows=1802
loops=1)
-> Seq Scan on
"north_am_v1.0" (cost=0.00..181.71 rows=1813 width=621) (actual time=0.138..81.395
rows=1802 loops=1)
Filter:
((name)::text <> 'water/agua/d\'eau'::text)
-> Subquery Scan "*SELECT*
4" (cost=0.00..0.02 rows=1 width=0) (actual time=0.104..0.118 rows=1 loops=1)
-> Result
(cost=0.00..0.01 rows=1 width=0) (actual time=0.082..0.087 rows=1 loops=1)
-> Subquery Scan "*SELECT*
5" (cost=0.00..5.74 rows=21 width=720) (actual time=0.108..1.314 rows=21 loops=1)
-> Seq Scan on
oncity_2m_prod (cost=0.00..5.53 rows=21 width=720) (actual time=0.078..0.926 rows=21
loops=1)
Filter: (name IS
NOT NULL)
-> Sort (cost=1.02..1.02 rows=1 width=354) (actual
time=0.006..0.012 rows=1 loops=712)
Sort Key: bric_extension.region_lookup.region_id
-> Seq Scan on region_lookup (cost=0.00..1.01
rows=1 width=354) (actual time=0.087..0.097 rows=1 loops=1)
SubPlan
-> Limit (cost=1.65..1.65 rows=1 width=36) (actual time=1.949..1.954 rows=1
loops=3)
InitPlan
-> Hash Join (cost=1.06..1.32 rows=1 width=4) (actual time=0.922..1.266
rows=1 loops=3) Hash Cond: ("outer".region_class =
("inner"."class")::text)
-> Subquery Scan region_classes (cost=0.00..0.21 rows=7 width=36)
(actual time=0.109..0.550 rows=7 loops=3)
-> Append (cost=0.00..0.14 rows=7 width=0) (actual
time=0.091..0.450 rows=7 loops=3)
-> Subquery Scan "*SELECT* 1" (cost=0.00..0.02 rows=1
width=0) (actual time=0.080..0.094 rows=1 loops=3)
-> Result (cost=0.00..0.01 rows=1 width=0)
(actual time=0.061..0.065 rows=1 loops=3)
-> Subquery Scan "*SELECT* 2" (cost=0.00..0.02 rows=1
width=0) (actual time=0.028..0.042 rows=1 loops=3)
-> Result (cost=0.00..0.01 rows=1 width=0)
(actual time=0.012..0.016 rows=1 loops=3)
-> Subquery Scan "*SELECT* 3" (cost=0.00..0.02 rows=1
width=0) (actual time=0.027..0.041 rows=1 loops=3)
-> Result (cost=0.00..0.01 rows=1 width=0)
(actual time=0.012..0.016 rows=1 loops=3)
-> Subquery Scan "*SELECT* 4" (cost=0.00..0.02 rows=1
width=0) (actual time=0.028..0.041 rows=1 loops=3)
-> Result (cost=0.00..0.01 rows=1 width=0)
(actual time=0.012..0.017 rows=1 loops=3)
-> Subquery Scan "*SELECT* 5" (cost=0.00..0.02 rows=1
width=0) (actual time=0.028..0.042 rows=1 loops=3)
-> Result (cost=0.00..0.01 rows=1 width=0)
(actual time=0.012..0.017 rows=1 loops=3)
-> Subquery Scan "*SELECT* 6" (cost=0.00..0.02 rows=1
width=0) (actual time=0.028..0.042 rows=1 loops=3)
-> Result (cost=0.00..0.01 rows=1 width=0)
(actual time=0.012..0.017 rows=1 loops=3)
-> Subquery Scan "*SELECT* 7" (cost=0.00..0.02 rows=1
width=0) (actual time=0.028..0.042 rows=1 loops=3)
-> Result (cost=0.00..0.01 rows=1 width=0)
(actual time=0.012..0.017 rows=1 loops=3)
-> Hash (cost=1.06..1.06 rows=1 width=14) (actual
time=0.412..0.412 rows=0 loops=3)
-> Seq Scan on region (cost=0.00..1.06 rows=1 width=14)
(actual time=0.079..0.124 rows=1 loops=3)
Filter: (region_id = $0)
-> Sort (cost=0.33..0.34 rows=7 width=36) (actual time=1.930..1.930
rows=1 loops=3)
Sort Key: "order"
-> Subquery Scan region_classes (cost=0.00..0.23 rows=7 width=36)
(actual time=1.530..1.834 rows=4 loops=3)
-> Append (cost=0.00..0.16 rows=7 width=0) (actual
time=1.500..1.756 rows=4 loops=3)
-> Subquery Scan "*SELECT* 1" (cost=0.00..0.02 rows=1
width=0) (actual
time=1.342..1.342 rows=0 loops=3)
-> Result (cost=0.00..0.01 rows=1 width=0)
(actual time=1.329..1.329 rows=0 loops=3)
One-Time Filter: (10 > $1)
-> Subquery Scan "*SELECT* 2" (cost=0.00..0.02 rows=1
width=0) (actual
time=0.022..0.022 rows=0 loops=3)
-> Result (cost=0.00..0.01 rows=1 width=0)
(actual time=0.009..0.009 rows=0 loops=3)
One-Time Filter: (20 > $1)
-> Subquery Scan "*SELECT* 3" (cost=0.00..0.02 rows=1
width=0) (actual
time=0.022..0.022 rows=0 loops=3)
-> Result (cost=0.00..0.01 rows=1 width=0)
(actual time=0.009..0.009 rows=0 loops=3)
One-Time Filter: (30 > $1)
-> Subquery Scan "*SELECT* 4" (cost=0.00..0.02 rows=1
width=0) (actual
time=0.066..0.081 rows=1 loops=3)
-> Result (cost=0.00..0.01 rows=1 width=0)
(actual time=0.016..0.020 rows=1 loops=3)
One-Time Filter: (40 > $1)
-> Subquery Scan "*SELECT* 5" (cost=0.00..0.02 rows=1
width=0) (actual
time=0.054..0.074 rows=1 loops=3)
-> Result (cost=0.00..0.01 rows=1 width=0)
(actual time=0.035..0.040 rows=1 loops=3)
One-Time Filter: (50 > $1)
-> Subquery Scan "*SELECT* 6" (cost=0.00..0.02 rows=1
width=0) (actual
time=0.034..0.049 rows=1 loops=3)
-> Result (cost=0.00..0.01 rows=1 width=0)
(actual time=0.017..0.021 rows=1 loops=3)
One-Time Filter: (60 > $1)
-> Subquery Scan "*SELECT* 7" (cost=0.00..0.02 rows=1
width=0) (actual
time=0.033..0.048 rows=1 loops=3)
-> Result (cost=0.00..0.01 rows=1 width=0)
(actual time=0.016..0.021 rows=1 loops=3)
One-Time Filter: (70 > $1)
Total runtime: 122080.977 ms
(120 rows)
---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?
http://archives.postgresql.org
