Re: planner picking more expensive plan

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