Extremely long query time after upgrading to Postgres 9.3 from 9.1

We're attempting to upgrade from Postgres 9.1 to Postgres 9.3 on a FreeBSD 9.2 zfs box. Performance is noticeably slower across the board, but we have one query that has gone from 353ms to >200000ms. We did a pg_dump and pg_restore so the data is the same. The query plans are the same. We can't figure out what is causing the time difference.

Query:

```
WITH RECURSIVE sub_products (
sub_product_no
,product_no
,p_type
,p_location
,p2_location
,path
,CYCLE
)
AS (
SELECT p2.product_no sub_product
,p.product_no
,p.type
,p.location_no p_location
,p2.location_no p2_location
,ARRAY [p.product_no]
,FALSE
FROM product p
INNER JOIN section s ON s.product_no = p.product_no
INNER JOIN packet_document pd ON pd.section_no = s.section_no
INNER JOIN product p2 ON p2.doc_id = pd.doc_id
AND (
p2.location_no = p.location_no
OR p.location_no = 0
)
AND CASE
WHEN p.document_type = 'MPCKT'
THEN date_trunc('month', p.product_ts) = date_trunc('month', p2.product_ts)
WHEN p.document_type = 'DPCKT'
THEN p.product_ts::DATE = p2.product_ts::DATE
WHEN p.document_type = 'WPCKT'
THEN date_trunc('week', p.product_ts) = date_trunc('week', p2.product_ts)
WHEN p.document_type = 'PPCKT'
THEN p2.section_no = pd.section_no
WHEN p.document_type = 'CPCKT'
THEN p2.complete != 'true'
END
AND (
p2.product_st != 'del'
OR p2.product_st IS NULL
)
WHERE p.product_no = 37202

UNION ALL

SELECT p2.product_no sub_product
,p.product_no
,p2.type
,p.location_no p_location
,p2.location_no p2_location
,path || p.product_no
,p.product_no = ANY (path)
FROM product p
INNER JOIN section s ON s.product_no = p.pr

The key difference is in 9.3:

"                ->  Sort  (cost=158991.46..162407.52 rows=1366422 width=86) (actual time=73496.575..81944.111 rows=9896192 loops=2)"
"                      Sort Key: p2_1.doc_id, p_1.product_no"
"                      Sort Method: external sort  Disk: 2068272kB"

vs in 9.1:

"                ->  Sort  (cost=305624.35..307335.61 rows=684504 width=60) (actual time=1156.466..1159.650 rows=63702 loops=2)"
"                      Sort Key: pd.doc_id"
"                      Sort Method: quicksort  Memory: 231kB"

probably because 9.3 has underestimated the number of rows output by the nodes underneath by a factor of 7, while 9.1 has overestimated them by a factor of 10.

Consider bumping work_mem up a bunch; throw say 10MB of work_mem at the query.

StackExchange Database Administrators Q#67911, answer score: 2