Re: planner picking more expensive plan - Mailing list pgsql-performance

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