X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id 0EE5552952 for ; Fri, 1 Jul 2005 11:58:47 -0300 (ADT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 23235-03 for ; Fri, 1 Jul 2005 14:58:45 +0000 (GMT) Received: from colo.samason.me.uk (unknown [69.55.228.22]) by svr1.postgresql.org (Postfix) with ESMTP id 172855292D for ; Fri, 1 Jul 2005 11:58:44 -0300 (ADT) Received: from colo.samason.me.uk (colo [69.55.228.22]) by colo.samason.me.uk (8.12.11/8.12.11) with ESMTP id j61Ewn2L051935 for ; Fri, 1 Jul 2005 15:58:49 +0100 (BST) (envelope-from sam@colo.samason.me.uk) Received: (from sam@localhost) by colo.samason.me.uk (8.12.11/8.12.11/Submit) id j61EwnaS051895 for pgsql-performance@postgresql.org; Fri, 1 Jul 2005 15:58:49 +0100 (BST) (envelope-from sam) Date: Fri, 1 Jul 2005 15:58:48 +0100 From: Sam Mason To: pgsql-performance@postgresql.org Subject: Re: planner picking more expensive plan Message-ID: <20050701145848.GW62747@colo.samason.me.uk> Mail-Followup-To: Sam Mason , pgsql-performance@postgresql.org References: <20050701133305.GB2623@sam.lan.samason.me.uk> <28888.1120227770@sss.pgh.pa.us> Mime-Version: 1.0 Content-Type: text/plain; charset=us-ascii Content-Disposition: inline In-Reply-To: <28888.1120227770@sss.pgh.pa.us> User-Agent: Mutt/1.4.2.1i X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0 tagged_above=0 required=5 tests= X-Spam-Level: X-Archive-Number: 200507/4 X-Sequence-Number: 13245 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