Query on 7.0 on Ultra II took over 5 hours. Query on 7.1 on x86 took
under 10 seconds. Same data. Both have been analyzed and vacuum'd
prior to running. Don't know whether to cheer about speed
improvements, or cry because we can't convert the production system
over to 7.1...
7.0 on Ultra 2
control=# explain SELECT count(*) as "Customers using Extra Services "
control-# FROM customer_info WHERE ( SELECT count(*)
control(# FROM domain_info
control(# , domain_services
control(# , services
control(# WHERE domain_services.service_id = services.service_id
control(# AND domain_info.domain_id = domain_services.domain_id
control(# AND customer_info.acct_id = domain_info.acct_id
control(# AND services.is_package IS FALSE
control(# ) > 0;
NOTICE: QUERY PLAN:
Aggregate (cost=21017481.56..21017481.56 rows=1 width=4)
-> Seq Scan on customer_info (cost=0.00..21017463.54 rows=7206
width=4)
SubPlan
-> Aggregate (cost=972.23..972.23 rows=1 width=24)
-> Hash Join (cost=20.83..972.19 rows=15 width=24)
-> Hash Join (cost=19.33..970.02 rows=21 width=20)
-> Seq Scan on domain_services (cost=0.00..495.97 rows=30297 width=12)
-> Hash (cost=19.28..19.28 rows=18 width=8)
-> Index Scan using domain_info_acct_id_idx on domain_info
(cost=0.00..19.28 rows=18 width=8)
-> Hash (cost=1.44..1.44 rows=25 width=4)
-> Seq Scan on services (cost=0.00..1.44 rows=25 width=4)
7.1 on x86 (FreeBSD)
billing=# explain SELECT count(*) as "Customers using Extra Services "
FROM customer_info WHERE ( SELECT count(*)
billing(# FROM domain_info
billing(# , domain_services
billing(# , services
billing(# WHERE domain_services.service_id = services.service_id
billing(# AND domain_info.domain_id = domain_services.domain_id
billing(# AND customer_info.acct_id = domain_info.acct_id
billing(# AND services.is_package IS FALSE
billing(# ) > 0;
NOTICE: QUERY PLAN:
Aggregate (cost=327708.80..327708.80 rows=1 width=0)
-> Seq Scan on customer_info (cost=0.00..327690.77 rows=7212 width=0)
SubPlan
-> Aggregate (cost=15.11..15.11 rows=1 width=24)
-> Merge Join (cost=14.73..15.10 rows=3 width=24)
-> Sort (cost=12.72..12.72 rows=4 width=20)
-> Nested Loop (cost=0.00..12.67 rows=4 width=20)
-> Index Scan using domain_info_acct_id_idx on domain_info
(cost=0.00..5.45 rows=3 width=8)
-> Index Scan using domain_services_domain_id_idx on domain_services
(cost=0.00..2.05 rows=1 width=12)
-> Sort (cost=2.02..2.02 rows=25 width=4)
-> Seq Scan on services (cost=0.00..1.44 rows=25 width=4)
--
Rod Taylor
There are always four sides to every story: your side, their side, the
truth, and what really happened.
