From: Sam Mason <sam@samason.me.uk>
To: pgsql-performance@postgresql.org
Subject: Re: planner picking more expensive plan
Date: Fri, 1 Jul 2005 15:58:48 +0100
Message-ID: <20050701145848.GW62747@colo.samason.me.uk> (raw)
In-Reply-To: <28888.1120227770@sss.pgh.pa.us>
References: <20050701133305.GB2623@sam.lan.samason.me.uk>
<28888.1120227770@sss.pgh.pa.us>
Tom Lane wrote:
>I fooled around trying to duplicate this behavior, without success.
>Can you create a self-contained test case?
I'll try and see if I can put something together, it's probably
going to be early next week though. I wont be able to give you our
data, so I'll be a bit of a headscratching exercise generating
something that'll provoke the same behaviour.
Not sure if it'll help, but here's what the database schema looks
like at the moment:
Table "public.animals"
Column | Type | Modifiers
-------------+-----------------------+-----------
animalid | integer | not null
sex | character(1) | not null
dob | date | not null
birthlocnid | integer |
breedid | character varying(8) |
eartag_1 | character varying(20) |
eartag_2 | character varying(20) |
eartag_3 | character varying(20) |
Indexes:
"animals_pkey" primary key, btree (animalid)
"animal_birthlocn" btree (birthlocnid)
"animal_breed" btree (breedid)
"animal_eartag" btree (eartag_1)
Check constraints:
"animal_sex" CHECK (sex = 'M'::bpchar OR sex = 'F'::bpchar)
Table "public.movements"
Column | Type | Modifiers
----------+---------+-----------
locnid | integer | not null
animalid | integer | not null
movedate | date | not null
mtypeid | integer | not null
Indexes:
"movement_animal" btree (animalid)
"movement_location" btree (locnid)
"movement_movedate" btree (movedate)
"movement_movetype" btree (mtypeid)
Foreign-key constraints:
"movement_location" FOREIGN KEY (locnid) REFERENCES locations(locnid)
"movement_animal" FOREIGN KEY (animalid) REFERENCES animals(animalid)
"movement_type" FOREIGN KEY (mtypeid) REFERENCES k_movement_type(mtypeid)
Table "public.locations"
Column | Type | Modifiers
--------+-----------------------+-----------
locnid | integer | not null
ptype | character varying(8) |
ltype | character varying(8) | not null
cph | character varying(20) |
unk | integer |
Indexes:
"locations_pkey" primary key, btree (locnid)
"location_cph" btree (cph)
"location_ltype" btree (ltype)
"location_ptype" btree (ptype)
Foreign-key constraints:
"location_ptype" FOREIGN KEY (ptype) REFERENCES k_premise_type(ptypeid)
"location_ltype" FOREIGN KEY (ltype) REFERENCES k_location_type(ltypeid)
As I said, animals contains about 3M rows, movements about 16M rows
and locations about 80K rows. There are about 3 to 8 rows for each
and every animal in the movements table, with at most one entry of
mtypeid=0 for each animal (95% of the animals have an entry).
Not sure if that's going to help making some demo data. It's just
that it took quite a while loading it all here, so coming up with
some code to make demo data may take a while.
Thanks!
Sam
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: sam@samason.me.uk
Subject: Re: planner picking more expensive plan
In-Reply-To: <20050701145848.GW62747@colo.samason.me.uk>
* 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