Re: Performance Optimization for Dummies 2 - the SQL

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(at)sss(dot)pgh(dot)pa(dot)us> wrote in message
news:2808(dot)1160951238(at)sss(dot)pgh(dot)pa(dot)us(dot)(dot)(dot)
> "Carlo Stonebanks" <stonec(dot)register(at)sympatico(dot)ca> 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
>