pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedBuld Insert and Index use.
2+ messages / 2 participants
[nested] [flat]
* Buld Insert and Index use.
@ 2004-08-12 00:00 Rudi Starcevic <tech@wildcash.com>
2004-08-12 00:25 ` Re: Buld Insert and Index use. Josh Berkus <josh@agliodbs.com>
0 siblings, 1 reply; 2+ messages in thread
From: Rudi Starcevic @ 2004-08-12 00:00 UTC (permalink / raw)
To: pgsql-performance
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.
^ permalink raw reply [nested|flat] 2+ messages in thread
* Re: Buld Insert and Index use.
2004-08-12 00:00 Buld Insert and Index use. Rudi Starcevic <tech@wildcash.com>
@ 2004-08-12 00:25 ` Josh Berkus <josh@agliodbs.com>
0 siblings, 0 replies; 2+ messages in thread
From: Josh Berkus @ 2004-08-12 00:25 UTC (permalink / raw)
To: tech@wildcash.com; pgsql-performance
Rudi,
> Do you think an Index or Seq. scan should be used?
That was way too much data for way too simple a question. ;-)
The answer is: depends on how many rows you have. With any significant
number of rows, yes.
However, you probably only need to index the first 3-5 columns; that's
selective enough.
--
-Josh Berkus
"A developer of Very Little Brain"
Aglio Database Solutions
San Francisco
^ permalink raw reply [nested|flat] 2+ messages in thread
end of thread, other threads:[~2004-08-12 00:25 UTC | newest]
Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2004-08-12 00:00 Buld Insert and Index use. Rudi Starcevic <tech@wildcash.com>
2004-08-12 00:25 ` Josh Berkus <josh@agliodbs.com>
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