Re: [HACKERS] Slow - grindingly slow - query
От | Theo Kramer |
---|---|
Тема | Re: [HACKERS] Slow - grindingly slow - query |
Дата | |
Msg-id | 199911120814.KAA24132@flame.flame.co.za обсуждение исходный текст |
Список | pgsql-hackers |
Vadim wrote: > > I did the same on Informix Online 7 and it took less than two minutes... > > Could you run the query above in Informix? > How long would it take to complete? I include both explain and timing for the queries for both postgres and Informix. Explain from postgres for the two queries. ------------------------------------------ explain select accountdetail.domain from accountdetail where accountdetail.domain not in (select accountmaster.domain from accountmaster); NOTICE: QUERY PLAN: Seq Scan on accounts (cost=3667.89 rows=34958 width=12) SubPlan -> Index Scan using registrationtype_idx on accounts (cost=2444.62 rows=33373 width=12) EXPLAIN explain select accountdetail.domain from accountdetail where not exists ( select accountmaster.domain from accountmaster where accountmaster.domain = accountdetail.domain); NOTICE: QUERY PLAN: Seq Scan on accounts (cost=3667.89 rows=34958 width=12) SubPlan -> Index Scan using domain_type_idx on accounts (cost=2.04 rows=1 width=12) EXPLAIN Explain from informix online 7 for the two queries -------------------------------------------------- QUERY: ------ select accountdetail.domain from accountdetail where accountdetail.domain not in (select accountmaster.domain from accountmaster) Estimated Cost: 8995 Estimated # of Rows Returned: 47652 1) informix.accounts: SEQUENTIAL SCAN Filters: (informix.accounts.domain != ALL <subquery> AND informix.accounts.registrationtype != 'N' ) Subquery: --------- Estimated Cost: 4497 Estimated # of Rows Returned: 5883 1) informix.accounts: SEQUENTIAL SCAN Filters: informix.accounts.registrationtype = 'N' QUERY: ------ select accountdetail.domain from accountdetail where accountdetail.domain not in (select accountmaster.domain from accountmaster) Estimated Cost: 4510 Estimated # of Rows Returned: 58810 1) informix.accounts: SEQUENTIAL SCAN Filters: (informix.accounts.domain != ALL <subquery> AND informix.accounts.registrationtype != 'N' ) Subquery: --------- Estimated Cost: 12 Estimated # of Rows Returned: 10 1) informix.accounts: INDEX PATH (1) Index Keys: registrationtype Lower Index Filter: informix.accounts.registrationtype = 'N' Timing from postgres 6.5.3 for the two queries ---------------------------------------------- explain select accountdetail.domain from accountdetail where accountdetail.domain not in (select accountmaster.domain from accountmaster); Greater than 5 hours and 30 minutes explain select accountdetail.domain from accountdetail where not exists ( select accountmaster.domain from accountmaster where accountmaster.domain = accountdetail.domain); 0.00user 0.01system 0:04.75elapsed 0%CPU (0avgtext+0avgdata 0maxresident)k Timing from Informix Online 7 for the two queries ---------------------------------------------- explain select accountdetail.domain from accountdetail where accountdetail.domain not in (select accountmaster.domain from accountmaster); 0.03user 0.01system 0:10.35elapsed 0%CPU (0avgtext+0avgdata 0maxresident)k explain select accountdetail.domain from accountdetail where not exists ( select accountmaster.domain from accountmaster where accountmaster.domain = accountdetail.domain); 0.03user 0.00system 0:03.56elapsed 0%CPU (0avgtext+0avgdata 0maxresident)k The machine is a Pentium II 400 MHz with Fast Wide SCSI and is the same for both Informix and Postgres. Informix uses Linux I/O ie. it does not use a raw partition. The datasets are the same. Regards Theo
В списке pgsql-hackers по дате отправления: