Hi,
This email is picking up a thread from yesterday on INSERTS and INDEXES.
In this case the question is to use and index or a sequential scan.
I have included the db DDL and SELECT query.
For each month I have a csv data dump of council property data.
So the First CD will have almost all unique records.
From there most properties will already be in the db.
Each line of the csv file populates three tables.
gc_prop
gc_value
gc_owner
gc_prop has a one to many relationship with gc_value and gc_owner.
For-each line we first SELECT from the gc_prop table.
If there is no record we INSERT into the gc_prop,gc_value and gc_owner
tables.
If there is a matching property we do not need to INSERT into the
gc_prop table only the gc_value and gc_owner tables.
So each row will require either:
a) One SELECT + three INSERTS
or
b) One SELECT + two INSERTS
CREATE TABLE gc_prop
(
gc_id serial PRIMARY KEY,
acc_no numeric(9,0),
master_code varchar(32),
no_prop numeric(4,0),
lot_no numeric(9,0),
plan_no varchar(32),
volume_no numeric(8,0),
folio_no numeric(8,0),
street_no varchar(32),
street_name varchar(30),
suburb_name varchar(30),
suburb_postcode numeric(4,0),
address1_txt varchar(80),
address2_txt varchar(80),
address3_txt varchar(80),
address4_txt varchar(80),
address5_txt varchar(80),
address6_txt varchar(80),
owner_postcode text,
planning_zone varchar(80),
waterfront_code varchar(80),
vacant_land varchar(32),
property_area text,
improvemeny_type varchar(20),
bup_indicator varchar(4),
property_name varchar(30),
lot_quantity text,
pool_indicator varchar(32),
owner_occupied varchar(32),
prsh_text varchar(30),
vg_no varchar(20),
property_part varchar(20),
lot_no_txt varchar(32),
plan_qualifier1 varchar(80),
plan_qualifier2 varchar(80),
owner_trans_date date,
legal_entity varchar(32),
"1st_surname_com" varchar(80),
"1st_given_name" varchar(64),
"2nd_surname" varchar(64),
"2nd_given_name" varchar(64),
easement_flag varchar(32),
trans_code varchar(32)
);
CREATE TABLE gc_value
(
gc_v_id serial PRIMARY KEY,
gc_id integer NOT NULL, -- foreign key to gc_prop table
current_valuation numeric(10,0),
valuation_date date,
last_sale_date date,
annual_rates numeric(7,0),
sale_amount numeric(9,0),
type_sale varchar(32),
date_sale date,
pre_val_date date,
pre_val_amount numeric(10,0),
pre_vg_no varchar(20),
future_val_date varchar(32),
fut_val_amount numeric(10,0),
fut_val_no varchar(32)
);
CREATE TABLE gc_owner
(
gc_o_id serial PRIMARY KEY,
gc_id integer NOT NULL, -- foreign key to gc_prop table
pre_legal_entity varchar(32),
pre_surname_com varchar(76),
pre_given_name varchar(32),
pre_2nd_surname varchar(40),
pre_2nd_given varchar(32),
orig_lot_no numeric(9,0),
orig_lot_txt varchar(32),
orig_prop_txt text,
orig_plan_qualifier1 varchar(32),
orig_plan_qualifier2 varchar(32),
orig_plan_no varchar(32)
);
CREATE INDEX gc_prop_check ON gc_prop (
acc_no,no_prop,lot_no,plan_no,street_no,street_name,suburb_postcode );
//check if this property already exists using gc_prop_check index
$sql = "
SELECT
gp.gc_id,
gp.acc_no,
gp.no_prop,
gp.lot_no,
gp.plan_no,
gp.street_no,
gp.street_name,
gp.suburb_postcode,
gp.owner_trans_date
FROM gc_prop gp
WHERE
gp.acc_no = $acc_no,
AND
gp.no_prop = $no_prop,
AND
gp.lot_no = $lot_no
AND
gp.plan_no = '$plan_no'
AND
gp.street_no = '$street_no'
AND
gp.street_name = '$street_name'
AND
gp.suburb_postcode = $suburb_postcode
";
Do you think an Index or Seq. scan should be used?
Thanks.
Regards,
Rudi.