Performance regressions in PG 9.3 vs PG 9.0

Hi,

We recently upgraded from pg 9.0.5 to 9.3.2 and we are observing much
higher load on our hot standbys (we have 3). As you can see from the query
plans below, we have some queries that are running 4-5 times slower now,
many due to what looks like a bad plan in 9.3. Are there any known issues
with query plan regressions in 9.3? Any ideas about how I can get back the
old planning behavior with 9.3.2?

Thanks in advance for any help!

On Production System
----------------------*Postgres 9.3.2*
Intel(R) Xeon(R) CPU E5649 (2.53 Ghz 6-core)
12 GB RAM
Intel 710 SSD

---------------------

explain analyze select distinct on (t1.id) t1.id, t1.hostname as name,
t1.active, t1.domain_id, t1.base, t1.port, t1.inter_domain_flag from
location t1, host t2, container t3, resource_location t4 where t2.id =
34725278 and t3.id = t2.container_id and t4.location_id = t1.id and
t4.parent_id in (select * from parentContainers(t3.id)) and t1.license
is not null and (t1.license_end_date is null or t1.license_end_date >=
current_date) and t1.active <> 0 and t3.active <> 0 and t4.active <> 0
and t1.domain_id = t2.domain_id and t2.domain_id = t3.domain_id and
t3.domain_id = t4.domain_id and (0 = 0 or t1.active <> 0);

QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Unique (cost=313.44..313.45 rows=1 width=35) (actual
time=989.836..989.837 rows=1 loops=1)
-> Sort (cost=313.44..313.44 rows=1 width=35) (actual
time=989.836..989.837 rows=1 loops=1)
Sort Key: t1.id
Sort Method: quicksort Memory: 25kB
-> Nested Loop (cost=1.27..313.43 rows=1 width=35) (actual
time=922.484..989.791 rows=1 loops=1)
Join Filter: (SubPlan 1)
Rows Removed by Join Filter: 742
-> Nested Loop (cost=0.99..33.80 rows=1 width=53)
(actual time=0.174..5.168 rows=934 loops=1)
Join Filter: (t2.domain_id = t1.domain_id)
-> Nested Loop (cost=0.71..11.23 rows=1
width=18) (actual time=0.101..0.103 rows=1 loops=1)
-> Index Scan using host_pkey on host t2
(cost=0.29..5.29 rows=1 width=12) (actual time=0.041..0.042 rows=1
loops=1)
Index Cond: (id = 34725278::numeric)
-> Index Scan using container_pkey on
container t3 (cost=0.42..5.43 rows=1 width=12) (actual
time=0.057..0.058 rows=1 loops=1)
Index Cond: (id = t2.container_id)
Filter: ((active <> 0::numeric) AND
(t2.domain_id = domain_id))
-> Index Scan using idx_location_domain_id on
location t1 (cost=0.28..18.55 rows=8 width=35) (actual
time=0.065..3.768 rows=934 loops=1)
Index Cond: (domain_id = t3.domain_id)
Filter: ((license IS NOT NULL) AND (active
<> 0::numeric) AND ((license_end_date IS NULL) OR (license_end_date >=
('now'::cstring)::date)))
Rows Removed by Filter: 297
-> Index Scan using idx_resource_location_domain_id on
resource_location t4 (cost=0.28..27.63 rows=1 width=21) (actual
time=0.532..0.849 rows=1 loops=934)
Index Cond: (domain_id = t1.domain_id)
Filter: ((active <> 0::numeric) AND (t1.id = location_id))
Rows Removed by Filter: 1003
SubPlan 1
-> Function Scan on parentcontainers
(cost=0.25..500.25 rows=1000 width=32) (actual time=0.253..0.253
rows=2 loops=743)
Total runtime: 990.045 ms
(26 rows)

On test box:
----------------------*Postgres 9.0.2*
Intel(R) Xeon(R) CPU E5345 (2.33 Ghz 4-core)
8 GB RAM
6 x SAS 10K RAID 10

----------------------

explain analyze select distinct on (t1.id) t1.id, t1.hostname as name,
t1.active, t1.domain_id, t1.base, t1.port, t1.inter_domain_flag from
location t1, host t2, container t3, resource_location t4 where t2.id =
34725278 and t3.id = t2.container_id and t4.location_id = t1.id and
t4.parent_id in (select * from parentContainers(t3.id)) and t1.license
is not null and (t1.license_end_date is null or t1.license_end_date >=
current_date) and t1.active <> 0 and t3.active <> 0 and t4.active <> 0
and t1.domain_id = t2.domain_id and t2.domain_id = t3.domain_id and
t3.domain_id = t4.domain_id and (0 = 0 or t1.active <> 0);

QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Unique (cost=389.96..389.97 rows=1 width=1192) (actual
time=217.479..217.480 rows=1 loops=1)
-> Sort (cost=389.96..389.97 rows=1 width=1192) (actual
time=217.477..217.477 rows=1 loops=1)
Sort Key: t1.id
Sort Method: quicksort Memory: 25kB
-> Nested Loop (cost=9.28..389.95 rows=1 width=1192)
(actual time=103.359..217.437 rows=1 loops=1)
Join Filter: ((t1.domain_id = t3.domain_id) AND (SubPlan 1))
-> Nested Loop (cost=9.28..130.66 rows=1 width=1320)
(actual time=18.494..29.577 rows=744 loops=1)
Join Filter: (t2.domain_id = t1.domain_id)
-> Nested Loop (cost=9.28..49.44 rows=12
width=160) (actual time=18.434..21.279 rows=1000 loops=1)
-> Index Scan using host_pkey on host t2
(cost=0.00..7.26 rows=1 width=64) (actual time=0.054..0.055 rows=1
loops=1)
Index Cond: (id = 34725278::numeric)
-> Bitmap Heap Scan on resource_location
t4 (cost=9.28..36.15 rows=12 width=96) (actual time=18.370..20.638
rows=1000 loops=1)
Recheck Cond: (t4.domain_id = t2.domain_id)
Filter: (t4.active <> 0::numeric)
-> Bitmap Index Scan on
idx_resource_location_domain_id (cost=0.00..9.28 rows=12 width=0)
(actual time=10.377..10.377 rows=1004 loops=1)
Index Cond: (t4.domain_id = t2.domain_id)
-> Index Scan using location_pkey on location t1
(cost=0.00..6.26 rows=1 width=1192) (actual time=0.006..0.007 rows=1
loops=1000)
Index Cond: (t1.id = t4.location_id)
Filter: ((t1.license IS NOT NULL) AND
(t1.active <> 0::numeric) AND ((t1.license_end_date IS NULL) OR
(t1.license_end_date >= ('now'::text)::date)))
-> Index Scan using container_pkey on container t3
(cost=0.00..7.29 rows=1 width=64) (actual time=0.005..0.006 rows=1
loops=744)
Index Cond: (t3.id = t2.container_id)
Filter: (t3.active <> 0::numeric)
SubPlan 1
-> Function Scan on parentcontainers
(cost=0.25..500.25 rows=1000 width=32) (actual time=0.243..0.243
rows=2 loops=744)
Total runtime: 217.735 ms