7.0 vs 7.1 running select count(*) FROM table WHERE (SELECT count(*) ) > 0;
От | Rod Taylor |
---|---|
Тема | 7.0 vs 7.1 running select count(*) FROM table WHERE (SELECT count(*) ) > 0; |
Дата | |
Msg-id | 04ff01c0a19d$2dc70910$2205010a@jester обсуждение исходный текст |
Ответы |
Re: 7.0 vs 7.1 running select count(*) FROM table WHERE (SELECT count(*) ) > 0;
|
Список | pgsql-general |
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.
Вложения
В списке pgsql-general по дате отправления: