Hi Gavin
Thanks for the new patch!
I ran some address matching on the patched code and have generated
another "ERROR: out of memory" problem.
The strange thing is that it runs over 150 queries without problem and
then crashes.
I have attached the logfile (well some of it).
If you want I can send you the schema as well....
Finlay
On Thu, 2007-05-03 at 13:51 +1000, Gavin Sherry wrote:
> Hi all,
>
> Attached is an updated bitmap index patch. It contains bug fixes, API
> changes, binary changes (page identifier to distinguish it from other
> indexes) and has been brought up to HEAD.
>
> I worked on a few approaches to VACUUM, none very satisfactory. The
> problem is, breaking a compressed word representing matches can have
> serious consequences -- at the least, creation of new words, at the worst,
> creation of a new page. If a lot of this were to happen, REINDEX would be
> much more efficient (this is what earlier patches did).
>
> One approach I looked at was modifying the existing read API to be able to
> do something like "kill prior tuple". This, I think, made the API quite
> complex and it was hard to implement, since the existing mechanism
> decompresses words on the fly and it would be hard to identify which TID
> is no longer a match. So, I dropped this idea pretty quickly.
>
> The second approach is to just manually traverse each vector and change
> matches to non-matches where necessary. The complexity then is in managing
> the consequences of breaking compressed words, doing WAL (efficiently) and
> calculating free space. I've only partially implemented this approach. At
> this stage, I don't have time to finish it due to other commitments.
>
> Thanks,
>
> Gavin
> ---------------------------(end of broadcast)--------------------------- TIP
> 2: Don't 'kill -9' the postmaster
NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "input_10_pkey"
for table "input_10"
TopMemoryContext: 10951272 total in 335 blocks; 20248 free (388 chunks);
10931024 used
TopTransactionContext: 8192 total in 1 blocks; 6816 free (0 chunks); 1376 used
RI compare cache: 8192 total in 1 blocks; 5904 free (0 chunks); 2288 used
RI query cache: 24576 total in 2 blocks; 14136 free (5 chunks); 10440 used
Local Buffer Lookup Table: 24576 total in 2 blocks; 12984 free (4 chunks);
11592 used
Prepared Queries: 8192 total in 1 blocks; 1800 free (0 chunks); 6392 used
Operator class cache: 8192 total in 1 blocks; 3848 free (0 chunks); 4344 used
Type information cache: 8192 total in 1 blocks; 1800 free (0 chunks); 6392 used
MessageContext: 1048576 total in 8 blocks; 491792 free (4 chunks); 556784 used
smgr relation table: 24576 total in 2 blocks; 16080 free (4 chunks); 8496 used
TransactionAbortContext: 32768 total in 1 blocks; 32752 free (0 chunks); 16 used
Portal hash: 8192 total in 1 blocks; 3912 free (0 chunks); 4280 used
PortalMemory: 8192 total in 1 blocks; 8040 free (0 chunks); 152 used
PortalHeapMemory: 4096 total in 1 blocks; 1912 free (0 chunks); 2184 used
ExecutorState: 2765031488 total in 19271 blocks; 7629976 free (15 chunks);
2757401512 used
HashBitmap: 8192 total in 1 blocks; 3912 free (0 chunks); 4280 used
HashBitmap: 8192 total in 1 blocks; 3912 free (0 chunks); 4280 used
HashBitmap: 8192 total in 1 blocks; 3912 free (0 chunks); 4280 used
HashBitmap: 8192 total in 1 blocks; 3912 free (0 chunks); 4280 used
HashBitmap: 8192 total in 1 blocks; 3912 free (0 chunks); 4280 used
HashBitmap: 8192 total in 1 blocks; 3912 free (0 chunks); 4280 used
-- snip --
19085 more lines like that
-- snip --
HashBitmap: 24576 total in 2 blocks; 14112 free (4 chunks); 10464 used
HashBitmap: 24576 total in 2 blocks; 14112 free (4 chunks); 10464 used
HashBitmap: 24576 total in 2 blocks; 14112 free (4 chunks); 10464 used
HashBitmap: 24576 total in 2 blocks; 14112 free (4 chunks); 10464 used
TupleSort: 1335472 total in 12 blocks; 1014144 free (16387 chunks); 321328 used
TupleSort: 24600 total in 2 blocks; 7520 free (0 chunks); 17080 used
ExprContext: 0 total in 0 blocks; 0 free (0 chunks); 0 used
ExprContext: 0 total in 0 blocks; 0 free (0 chunks); 0 used
ExprContext: 0 total in 0 blocks; 0 free (0 chunks); 0 used
ExprContext: 8192 total in 1 blocks; 8160 free (0 chunks); 32 used
ExprContext: 0 total in 0 blocks; 0 free (0 chunks); 0 used
ExprContext: 0 total in 0 blocks; 0 free (0 chunks); 0 used
ExprContext: 0 total in 0 blocks; 0 free (0 chunks); 0 used
ExprContext: 0 total in 0 blocks; 0 free (0 chunks); 0 used
ExprContext: 0 total in 0 blocks; 0 free (0 chunks); 0 used
ExprContext: 0 total in 0 blocks; 0 free (0 chunks); 0 used
ExprContext: 0 total in 0 blocks; 0 free (0 chunks); 0 used
ExprContext: 8192 total in 1 blocks; 8176 free (0 chunks); 16 used
ExprContext: 0 total in 0 blocks; 0 free (0 chunks); 0 used
ExprContext: 0 total in 0 blocks; 0 free (0 chunks); 0 used
Unique: 0 total in 0 blocks; 0 free (0 chunks); 0 used
Relcache by OID: 8192 total in 1 blocks; 1816 free (0 chunks); 6376 used
CacheMemoryContext: 1191760 total in 21 blocks; 471912 free (43 chunks); 719848
used
pg_bm_24706_index: 1024 total in 1 blocks; 304 free (0 chunks); 720 used
idx_matched_address_10_customer_id: 1024 total in 1 blocks; 344 free (0
chunks); 680 used
idx_matched_address_10_delivery_point_id: 1024 total in 1 blocks; 344 free (0
chunks); 680 used
pg_statistic_relid_att_index: 1024 total in 1 blocks; 240 free (0 chunks); 784
used
pg_bm_24653_index: 1024 total in 1 blocks; 304 free (0 chunks); 720 used
pg_bm_24724_index: 1024 total in 1 blocks; 304 free (0 chunks); 720 used
pg_bm_24716_index: 1024 total in 1 blocks; 304 free (0 chunks); 720 used
pg_bm_24657_index: 1024 total in 1 blocks; 304 free (0 chunks); 720 used
CachedPlan: 3072 total in 2 blocks; 256 free (0 chunks); 2816 used
CachedPlanSource: 3072 total in 2 blocks; 1512 free (1 chunks); 1560 used
SPI Plan: 1024 total in 1 blocks; 912 free (0 chunks); 112 used
CachedPlan: 7168 total in 3 blocks; 3296 free (0 chunks); 3872 used
CachedPlanSource: 3072 total in 2 blocks; 1672 free (1 chunks); 1400 used
SPI Plan: 1024 total in 1 blocks; 912 free (0 chunks); 112 used
v2007q1v01_geodem_address_meshblock_id_idx: 1024 total in 1 blocks; 304 free (0
chunks); 720 used
v2007q1v01_geodem_address_delivery_point_id_idx: 1024 total in 1 blocks; 304
free (0 chunks); 720 used
v2007q1v01_geodem_address_pkey: 1024 total in 1 blocks; 304 free (0 chunks);
720 used
input_10_building_number_idx: 1024 total in 1 blocks; 344 free (0 chunks); 680
used
input_10_rd_number_idx: 1024 total in 1 blocks; 344 free (0 chunks); 680 used
input_10_postcode_idx: 1024 total in 1 blocks; 344 free (0 chunks); 680 used
input_10_town_city_mailtown_idx: 1024 total in 1 blocks; 344 free (0 chunks);
680 used
input_10_suburb_name_idx: 1024 total in 1 blocks; 304 free (0 chunks); 720 used
input_10_box_bag_lobby_name_idx: 1024 total in 1 blocks; 344 free (0 chunks);
680 used
input_10_box_bag_number_idx: 1024 total in 1 blocks; 344 free (0 chunks); 680
used
input_10_delivery_service_type_idx: 1024 total in 1 blocks; 344 free (0
chunks); 680 used
input_10_street_direction_idx: 1024 total in 1 blocks; 344 free (0 chunks); 680
used
input_10_street_type_idx: 1024 total in 1 blocks; 344 free (0 chunks); 680 used
input_10_street_name_idx: 1024 total in 1 blocks; 344 free (0 chunks); 680 used
input_10_building_name_idx: 1024 total in 1 blocks; 344 free (0 chunks); 680
used
input_10_floor_idx: 1024 total in 1 blocks; 344 free (0 chunks); 680 used
input_10_unit_identifier_idx: 1024 total in 1 blocks; 344 free (0 chunks); 680
used
input_10_unit_type_idx: 1024 total in 1 blocks; 344 free (0 chunks); 680 used
input_10_street_alpha_idx: 1024 total in 1 blocks; 344 free (0 chunks); 680 used
input_10_street_number_idx: 1024 total in 1 blocks; 344 free (0 chunks); 680
used
input_10_address_type_idx: 1024 total in 1 blocks; 344 free (0 chunks); 680 used
input_10_meshblock_id_idx: 1024 total in 1 blocks; 344 free (0 chunks); 680 used
input_10_customer_id_idx: 1024 total in 1 blocks; 304 free (0 chunks); 720 used
input_10_pkey: 1024 total in 1 blocks; 344 free (0 chunks); 680 used
v2007q1v01_delivery_addresses_street_name_rd_number_idx: 1024 total in 1
blocks; 240 free (0 chunks); 784 used
v2007q1v01_delivery_addresses_street_number_street_name_idx: 1024 total in 1
blocks; 240 free (0 chunks); 784 used
v2007q1v01_delivery_addresses_old_postcode_street_number_idx: 1024 total in 1
blocks; 280 free (0 chunks); 744 used
v2007q1v01_delivery_addresses_street_number_old_postcode_idx: 1024 total in 1
blocks; 280 free (0 chunks); 744 used
v2007q1v01_delivery_addresses_street_number_postcode_idx: 1024 total in 1
blocks; 280 free (0 chunks); 744 used
v2007q1v01_delivery_addresses_postcode_street_number_idx: 1024 total in 1
blocks; 280 free (0 chunks); 744 used
v2007q1v01_delivery_addresses_street_number_suburb_alias_id_idx: 1024 total in
1 blocks; 280 free (0 chunks); 744 used
v2007q1v01_delivery_addresses_suburb_alias_id_street_number_idx: 1024 total in
1 blocks; 280 free (0 chunks); 744 used
v2007q1v01_delivery_addresses_record_usage_id_idx: 1024 total in 1 blocks; 344
free (0 chunks); 680 used
v2007q1v01_delivery_addresses_old_postcode_idx: 1024 total in 1 blocks; 344
free (0 chunks); 680 used
v2007q1v01_delivery_addresses_rd_number_idx: 1024 total in 1 blocks; 344 free
(0 chunks); 680 used
v2007q1v01_delivery_addresses_postcode_idx: 1024 total in 1 blocks; 344 free (0
chunks); 680 used
v2007q1v01_delivery_addresses_town_city_mailtown_idx: 1024 total in 1 blocks;
344 free (0 chunks); 680 used
v2007q1v01_delivery_addresses_town_city_mailtown_alias_id_idx: 1024 total in 1
blocks; 344 free (0 chunks); 680 used
v2007q1v01_delivery_addresses_suburb_name_idx: 1024 total in 1 blocks; 344 free
(0 chunks); 680 used
v2007q1v01_delivery_addresses_suburb_alias_id_idx: 1024 total in 1 blocks; 344
free (0 chunks); 680 used
v2007q1v01_delivery_addresses_box_bag_lobby_name_idx: 1024 total in 1 blocks;
344 free (0 chunks); 680 used
v2007q1v01_delivery_addresses_box_bag_number_idx: 1024 total in 1 blocks; 344
free (0 chunks); 680 used
v2007q1v01_delivery_addresses_delivery_service_type_idx: 1024 total in 1
blocks; 344 free (0 chunks); 680 used
v2007q1v01_delivery_addresses_street_direction_idx: 1024 total in 1 blocks; 344
free (0 chunks); 680 used
v2007q1v01_delivery_addresses_street_type_idx: 1024 total in 1 blocks; 344 free
(0 chunks); 680 used
v2007q1v01_delivery_addresses_street_name_idx: 1024 total in 1 blocks; 344 free
(0 chunks); 680 used
v2007q1v01_delivery_addresses_street_alias_id_idx: 1024 total in 1 blocks; 344
free (0 chunks); 680 used
v2007q1v01_delivery_addresses_building_name_idx: 1024 total in 1 blocks; 344
free (0 chunks); 680 used
v2007q1v01_delivery_addresses_floor_idx: 1024 total in 1 blocks; 344 free (0
chunks); 680 used
v2007q1v01_delivery_addresses_unit_identifier_idx: 1024 total in 1 blocks; 344
free (0 chunks); 680 used
v2007q1v01_delivery_addresses_unit_type_idx: 1024 total in 1 blocks; 344 free
(0 chunks); 680 used
v2007q1v01_delivery_addresses_street_alpha_idx: 1024 total in 1 blocks; 344
free (0 chunks); 680 used
v2007q1v01_delivery_addresses_street_number_idx: 1024 total in 1 blocks; 344
free (0 chunks); 680 used
v2007q1v01_delivery_addresses_address_type_idx: 1024 total in 1 blocks; 344
free (0 chunks); 680 used
v2007q1v01_delivery_addresses_delivery_point_id_idx: 1024 total in 1 blocks;
344 free (0 chunks); 680 used
v2007q1v01_delivery_addresses_pkey: 1024 total in 1 blocks; 304 free (0
chunks); 720 used
pg_trigger_tgconstrname_index: 1024 total in 1 blocks; 304 free (0 chunks); 720
used
pg_trigger_oid_index: 1024 total in 1 blocks; 304 free (0 chunks); 720 used
pg_tablespace_spcname_index: 1024 total in 1 blocks; 344 free (0 chunks); 680
used
pg_tablespace_oid_index: 1024 total in 1 blocks; 344 free (0 chunks); 680 used
pg_toast_2618_index: 1024 total in 1 blocks; 240 free (0 chunks); 784 used
pg_tables: 15360 total in 4 blocks; 2192 free (0 chunks); 13168 used
v2007q1v01_alternative_town_city_names_record_usage_id_idx: 1024 total in 1
blocks; 344 free (0 chunks); 680 used
v2007q1v01_alternative_street_names_record_usage_id_idx: 1024 total in 1
blocks; 344 free (0 chunks); 680 used
v2007q1v01_alternative_suburb_names_record_usage_id_idx: 1024 total in 1
blocks; 344 free (0 chunks); 680 used
pg_constraint_contypid_index: 1024 total in 1 blocks; 304 free (0 chunks); 720
used
pg_constraint_conrelid_index: 1024 total in 1 blocks; 304 free (0 chunks); 720
used
pg_constraint_conname_nsp_index: 1024 total in 1 blocks; 240 free (0 chunks);
784 used
pg_shdepend_depender_index: 1024 total in 1 blocks; 152 free (0 chunks); 872
used
pg_shdepend_reference_index: 1024 total in 1 blocks; 240 free (0 chunks); 784
used
pg_depend_depender_index: 1024 total in 1 blocks; 152 free (0 chunks); 872 used
pg_depend_reference_index: 1024 total in 1 blocks; 152 free (0 chunks); 872 used
paf_input_file_pk: 1024 total in 1 blocks; 304 free (0 chunks); 720 used
pg_attrdef_adrelid_adnum_index: 1024 total in 1 blocks; 240 free (0 chunks);
784 used
paf_version_pk: 1024 total in 1 blocks; 344 free (0 chunks); 680 used
pg_index_indrelid_index: 1024 total in 1 blocks; 304 free (0 chunks); 720 used
pg_enum_typid_label_index: 1024 total in 1 blocks; 280 free (0 chunks); 744 used
pg_enum_oid_index: 1024 total in 1 blocks; 344 free (0 chunks); 680 used
pg_opfamily_oid_index: 1024 total in 1 blocks; 344 free (0 chunks); 680 used
pg_opfamily_am_name_nsp_index: 1024 total in 1 blocks; 192 free (0 chunks); 832
used
pg_type_typname_nsp_index: 1024 total in 1 blocks; 240 free (0 chunks); 784 used
pg_type_oid_index: 1024 total in 1 blocks; 304 free (0 chunks); 720 used
pg_trigger_tgrelid_tgname_index: 1024 total in 1 blocks; 240 free (0 chunks);
784 used
pg_auth_members_member_role_index: 1024 total in 1 blocks; 280 free (0 chunks);
744 used
pg_auth_members_role_member_index: 1024 total in 1 blocks; 280 free (0 chunks);
744 used
pg_rewrite_rel_rulename_index: 1024 total in 1 blocks; 240 free (0 chunks); 784
used
pg_proc_proname_args_nsp_index: 1024 total in 1 blocks; 152 free (0 chunks);
872 used
pg_proc_oid_index: 1024 total in 1 blocks; 304 free (0 chunks); 720 used
pg_operator_oprname_l_r_n_index: 1024 total in 1 blocks; 88 free (0 chunks);
936 used
pg_operator_oid_index: 1024 total in 1 blocks; 304 free (0 chunks); 720 used
pg_opclass_oid_index: 1024 total in 1 blocks; 304 free (0 chunks); 720 used
pg_opclass_am_name_nsp_index: 1024 total in 1 blocks; 152 free (0 chunks); 872
used
pg_namespace_oid_index: 1024 total in 1 blocks; 304 free (0 chunks); 720 used
pg_namespace_nspname_index: 1024 total in 1 blocks; 304 free (0 chunks); 720
used
pg_language_oid_index: 1024 total in 1 blocks; 344 free (0 chunks); 680 used
pg_language_name_index: 1024 total in 1 blocks; 304 free (0 chunks); 720 used
pg_index_indexrelid_index: 1024 total in 1 blocks; 304 free (0 chunks); 720 used
pg_authid_oid_index: 1024 total in 1 blocks; 304 free (0 chunks); 720 used
pg_authid_rolname_index: 1024 total in 1 blocks; 304 free (0 chunks); 720 used
pg_database_oid_index: 1024 total in 1 blocks; 304 free (0 chunks); 720 used
pg_conversion_oid_index: 1024 total in 1 blocks; 344 free (0 chunks); 680 used
pg_conversion_name_nsp_index: 1024 total in 1 blocks; 280 free (0 chunks); 744
used
pg_conversion_default_index: 1024 total in 1 blocks; 128 free (0 chunks); 896
used
pg_constraint_oid_index: 1024 total in 1 blocks; 304 free (0 chunks); 720 used
pg_class_oid_index: 1024 total in 1 blocks; 304 free (0 chunks); 720 used
pg_cast_source_target_index: 1024 total in 1 blocks; 240 free (0 chunks); 784
used
pg_attribute_relid_attnum_index: 1024 total in 1 blocks; 240 free (0 chunks);
784 used
pg_attribute_relid_attnam_index: 1024 total in 1 blocks; 240 free (0 chunks);
784 used
pg_amproc_fam_proc_index: 1024 total in 1 blocks; 88 free (0 chunks); 936 used
pg_amop_opr_fam_index: 1024 total in 1 blocks; 240 free (0 chunks); 784 used
pg_amop_fam_strat_index: 1024 total in 1 blocks; 88 free (0 chunks); 936 used
pg_aggregate_fnoid_index: 1024 total in 1 blocks; 304 free (0 chunks); 720 used
MdSmgr: 8192 total in 1 blocks; 4648 free (3 chunks); 3544 used
LOCALLOCK hash: 24576 total in 2 blocks; 16168 free (4 chunks); 8408 used
Timezones: 48616 total in 2 blocks; 5968 free (0 chunks); 42648 used
ErrorContext: 8192 total in 1 blocks; 8176 free (4 chunks); 16 used
ERROR: out of memory
DETAIL: Failed on request of size 8232.
STATEMENT: INSERT INTO temporary_matches SELECT DISTINCT
a.delivery_point_id, c.customer_id,
'URBAN:MESHBLOCK:UNIT:ALPHA:STREET_NAME:STREET_TYPE:STREET_DIR:ALT_SUBURB_P:TOWN:POSTCODE'::TEXT
FROM input_10 c
JOIN v2007q1v01_delivery_addresses a
ON ( a.address_type = 'URBAN' )
AND ( UPPER(c.unit_identifier) = a.unit_identifier
OR
both_null(c.unit_identifier,
a.unit_identifier) )
AND ( UPPER(c.street_alpha) = a.street_alpha
OR
both_null(c.street_alpha,
a.street_alpha) )
AND ( UPPER(c.street_name) = a.street_name )
AND ( UPPER(c.street_type) = a.street_type
OR
both_null(c.street_type, a.street_type) )
AND ( UPPER(c.street_direction) = a.street_direction
OR
NULLIF(a.street_direction, '') IS NULL )
AND ( UPPER(c.town_city_mailtown) = a.town_city_mailtown )
AND ( a.postcode = c.postcode )
JOIN v2007q1v01_alternative_suburb_names asb_p
ON ( a.suburb_alias_id = asb_p.suburb_alias_id
AND
asb_p.record_usage_id = 1001 )
AND ( UPPER(c.suburb_name) = asb_p.suburb_name
OR
both_null(c.suburb_name,
asb_p.suburb_name) )
JOIN v2007q1v01_geodem_address gm
ON ( a.delivery_point_id = gm.delivery_point_id )
AND ( c.meshblock_id = gm.meshblock_id )
LEFT JOIN matched_address_10 mc ON c.customer_id = mc.customer_id
WHERE mc.delivery_point_id IS NULL
---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match
