I think there's 2 things that would help this case. First, partition on
country. You can either do this on a table level or on an index level
by putting where clauses on the indexes (index method would be the
fastest one to test, since it's just new indexes). That should shrink
the size of that index noticably.
The other thing is to try and get the planner to not double-scan the
index. If you add the following, I think it will scan the index once for
the LIKE, and then just filter whatever it finds to match the other
conditions.
and f.default_postal_code LIKE '14224%'
On Mon, Oct 16, 2006 at 01:33:28PM -0400, Carlo Stonebanks wrote:
> INFO: vacuuming "mdx_core.facility"
> INFO: index "facility_pkey" now contains 964123 row versions in 3682 pages
> DETAIL: 0 index pages have been deleted, 0 are currently reusable.
> CPU 0.03s/0.03u sec elapsed 0.18 sec.
> INFO: index "facility_country_state_city_idx" now contains 964188 row
> versions in 7664 pages
> DETAIL: 0 index pages have been deleted, 0 are currently reusable.
> CPU 0.25s/0.17u sec elapsed 84.14 sec.
> INFO: index "facility_country_state_postal_code_idx" now contains 964412
> row versions in 7689 pages
> DETAIL: 0 index pages have been deleted, 0 are currently reusable.
> CPU 0.42s/0.10u sec elapsed 137.12 sec.
> INFO: index "facility_facility_country_state_city_idx" now contains 964493
> row versions in 6420 pages
> DETAIL: 0 index pages have been deleted, 0 are currently reusable.
> CPU 0.17s/0.09u sec elapsed 2.23 sec.
> INFO: index "facility_facility_country_state_postal_code_idx" now contains
> 964494 row versions in 6895 pages
> DETAIL: 0 index pages have been deleted, 0 are currently reusable.
> CPU 0.01s/0.01u sec elapsed 0.95 sec.
> INFO: "facility": found 0 removable, 964123 nonremovable row versions in
> 17398 pages
> DETAIL: 0 dead row versions cannot be removed yet.
> There were 0 unused item pointers.
> 0 pages are entirely empty.
> CPU 0.90s/0.57u sec elapsed 224.80 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.01 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.01 sec.
>
> Query returned successfully with no result in 224903 ms.
>
> INFO: vacuuming "mdx_core.facility_address"
> INFO: index "facility_address_pkey" now contains 893157 row versions in
> 3411 pages
> DETAIL: 0 index pages have been deleted, 0 are currently reusable.
> CPU 0.17s/0.04u sec elapsed 11.10 sec.
> INFO: index "facility_address_address_idx" now contains 893157 row versions
> in 3164 pages
> DETAIL: 0 index pages have been deleted, 0 are currently reusable.
> CPU 0.07s/0.04u sec elapsed 0.61 sec.
> INFO: index "facility_address_facility_address_address_type_idx" now
> contains 893157 row versions in 3797 pages
> DETAIL: 0 index pages have been deleted, 0 are currently reusable.
> CPU 0.01s/0.00u sec elapsed 0.07 sec.
> INFO: "facility_address": found 0 removable, 893139 nonremovable row
> versions in 9210 pages
> DETAIL: 0 dead row versions cannot be removed yet.
> There were 0 unused item pointers.
> 0 pages are entirely empty.
> CPU 0.26s/0.15u sec elapsed 12.14 sec.
>
> Query returned successfully with no result in 12297 ms.
>
>
>
>
>
> 5) Here is the original query, plus original explain analyze. Number of rows
> have increased since this was run, so the costs are lower, but still
> significant:
>
> /*
> Find all facilities that do not have full address information
> but do have default location information that indicates
> its the facilitiy's US zip code.
> NULL values cast as columns are placeholders to allow
> this sub-query to be unioned with another subquery
> that contains full address data
> */
> select
> f.facility_id,
> null as facility_address_id,
> null as address_id,
> f.facility_type_code,
> f.name,
> null as address,
> f.default_city as city,
> f.default_state_code as state_code,
> f.default_postal_code as postal_code,
> f.default_country_code as country_code,
> null as parsed_unit
> from
> mdx_core.facility as f
> left outer join mdx_core.facility_address as fa
> on fa.facility_id = f.facility_id
> where
> facility_address_id is null
> and f.default_country_code = 'US'
> and (f.default_postal_code = '14224-1945' or f.default_postal_code =
> '14224')
>
> "Nested Loop Left Join (cost=22966.70..23594.84 rows=93 width=71) (actual
> time=662.075..662.075 rows=0 loops=1)"
> " Filter: ("inner".facility_address_id IS NULL)"
> " -> Bitmap Heap Scan on facility f (cost=22966.70..23231.79 rows=93
> width=71) (actual time=661.907..661.929 rows=7 loops=1)"
> " Recheck Cond: (((default_country_code = 'US'::bpchar) AND
> ((default_postal_code)::text = '14224-1945'::text)) OR
> ((default_country_code = 'US'::bpchar) AND ((default_postal_code)::text =
> '14224'::text)))"
> " -> BitmapOr (cost=22966.70..22966.70 rows=93 width=0) (actual
> time=661.891..661.891 rows=0 loops=1)"
> " -> Bitmap Index Scan on
> facility_country_state_postal_code_idx (cost=0.00..11483.35 rows=47
> width=0) (actual time=374.284..374.284 rows=7 loops=1)"
> " Index Cond: ((default_country_code = 'US'::bpchar) AND
> ((default_postal_code)::text = '14224-1945'::text))"
> " -> Bitmap Index Scan on
> facility_country_state_postal_code_idx (cost=0.00..11483.35 rows=47
> width=0) (actual time=287.599..287.599 rows=0 loops=1)"
> " Index Cond: ((default_country_code = 'US'::bpchar) AND
> ((default_postal_code)::text = '14224'::text))"
> " -> Index Scan using facility_address_facility_address_address_type_idx
> on facility_address fa (cost=0.00..3.89 rows=1 width=8) (actual
> time=0.014..0.016 rows=1 loops=7)"
> " Index Cond: (fa.facility_id = "outer".facility_id)"
> "Total runtime: 662.203 ms"
> >
>
>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 2: Don't 'kill -9' the postmaster
>
--
Jim Nasby jim@xxxxxxxxx
EnterpriseDB http://enterprisedb.com 512.569.9461 (cell)
