Re: Performance Optimization for Dummies 2 - the SQL - Mailing list pgsql-performance

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@sss.pgh.pa.us> wrote in message
news:2808.1160951238@sss.pgh.pa.us...
> "Carlo Stonebanks" <stonec.register@sympatico.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
>