Hey Tom, thanks for jumping in. Nothing on TV on a Sunday afternoon? ;-) Appreciate teh input. Here is vacuum verbose output for both the tables in question. Carlo INFO: vacuuming "mdx_core.facility" INFO: index "facility_pkey" now contains 832399 row versions in 3179 pages DETAIL: 0 index pages have been deleted, 0 are currently reusable. CPU 0.09s/0.04u sec elapsed 0.21 sec. INFO: index "facility_country_state_city_idx" now contains 832444 row versions in 6630 pages DETAIL: 0 index pages have been deleted, 0 are currently reusable. CPU 0.15s/0.07u sec elapsed 43.81 sec. INFO: index "facility_country_state_postal_code_idx" now contains 832499 row versions in 6658 pages DETAIL: 0 index pages have been deleted, 0 are currently reusable. CPU 0.23s/0.07u sec elapsed 0.37 sec. INFO: "facility": found 0 removable, 832398 nonremovable row versions in 15029 pages DETAIL: 0 dead row versions cannot be removed yet. There were 0 unused item pointers. 0 pages are entirely empty. CPU 0.67s/0.32u sec elapsed 44.71 sec. INFO: vacuuming "pg_toast.pg_toast_58570311" INFO: index "pg_toast_58570311_index" now contains 0 row versions in 1 pages DETAIL: 0 index pages have been deleted, 0 are currently reusable. CPU 0.00s/0.00u sec elapsed 0.00 sec. INFO: "pg_toast_58570311": found 0 removable, 0 nonremovable row versions in 0 pages DETAIL: 0 dead row versions cannot be removed yet. There were 0 unused item pointers. 0 pages are entirely empty. CPU 0.00s/0.00u sec elapsed 0.00 sec. Query returned successfully with no result in 44875 ms. INFO: vacuuming "mdx_core.facility_address" INFO: index "facility_address_pkey" now contains 772770 row versions in 2951 pages DETAIL: 0 index pages have been deleted, 0 are currently reusable. CPU 0.10s/0.04u sec elapsed 9.73 sec. INFO: index "facility_address_address_idx" now contains 772771 row versions in 2750 pages DETAIL: 0 index pages have been deleted, 0 are currently reusable. CPU 0.04s/0.04u sec elapsed 0.34 sec. INFO: index "facility_address_facility_address_address_type_idx" now contains 772773 row versions in 3154 pages DETAIL: 0 index pages have been deleted, 0 are currently reusable. CPU 0.01s/0.04u sec elapsed 0.06 sec. INFO: "facility_address": found 0 removable, 772747 nonremovable row versions in 7969 pages DETAIL: 0 dead row versions cannot be removed yet. There were 0 unused item pointers. 0 pages are entirely empty. CPU 0.39s/0.18u sec elapsed 10.70 sec. Query returned successfully with no result in 10765 ms. "Tom Lane" <tgl@xxxxxxxxxxxxx> wrote in message news:2808.1160951238@xxxxxxxxxxxxxxxx > "Carlo Stonebanks" <stonec.register@xxxxxxxxxxxx> writes: >> Curiously, it's using index scans, and it really looks like a simple >> query >> to me. I am completely baffled. The two tables in question have about >> 800K >> rows each - not exactly an incredible number. The EXPLAIN is simple, but >> the >> performance is dreadful. All the other queries run much faster than >> this - >> does ANYTHING about this query strike you as odd? > > Lots of dead rows perhaps? The EXPLAIN estimates look a bit out of line > --- 11483 cost units to fetch 47 index entries is an order or two of > magnitude higher than it ought to be. The real time also seems to be > concentrated in that index scan. What are the physical sizes of the > table and index? (VACUUM VERBOSE output for the facility table might > tell something.) > > regards, tom lane > > ---------------------------(end of broadcast)--------------------------- > TIP 2: Don't 'kill -9' the postmaster >
