From: Carlo Stonebanks <stonec.register@sympatico.ca>
To: pgsql-performance@postgresql.org
Subject: Re: Performance Optimization for Dummies 2 - the SQL
Date: Sun, 15 Oct 2006 18:48:28 -0400
Message-ID: <egudr8$2t0v$1@news.hub.org> (raw)
References: <eft782$5a1$1@news.hub.org>
<b42b73150610041407y3554f311u1329c4f3bdc53999@mail.gmail.com>
<eg1dai$1tfa$1@news.hub.org>
<b42b73150610042326v7fdeed17rf6b96e52a681a6db@mail.gmail.com>
<eg2cds$29en$1@news.hub.org>
<b42b73150610050630t76e9eedeh45a382729d35f2f6@mail.gmail.com>
<eg611q$l0m$1@news.hub.org>
<1160153623.6181.13.camel@state.g2switchworks.com>
<b42b73150610061153i3d11d7a4r23e1d7c5317feb9f@mail.gmail.com>
<eg6f8l$2det$1@news.hub.org>
<b42b73150610090649y74c4dc99g176366921f784e7b@mail.gmail.com>
<egua6n$1fv4$1@news.hub.org>
<2808.1160951238@sss.pgh.pa.us>
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
>
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: stonec.register@sympatico.ca
Subject: Re: Performance Optimization for Dummies 2 - the SQL
In-Reply-To: <egudr8$2t0v$1@news.hub.org>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox