Re: Query plan excluding index on view - Mailing list pgsql-performance

I'm sorry for the "fan-dance", it was not my intention to make it difficult but actually simpler in leaving out the finer details - lesson learned.  Below you'll find create scripts for all tables and views invlolved.  Also I've included the explain text for both queries when ran on the 8.3 database where what was included before was from 8.1  (I was incorrect in stating I had tried version 8.2, as I thought the 8.1 install was 8.2 - my apologies).

--Table 1 - (Item A)  ~18M records
CREATE TABLE company
(
  id bigint NOT NULL DEFAULT nextval('global_sequence'::regclass),
  "name" character varying(65) NOT NULL,
  description character varying(100),
  recordid character varying(10),
  full_address character varying(45),
  street_number character varying(10),
  street_directional character(2),
  street_name character varying(20),
  unit_designator character varying(4),
  unit_number character varying(8),
  city_name character varying(20),
  state_code character(2),
  zip character(5),
  zip_extension character(4),
  phone character varying(10),
  phone_code character(1),
  publish_date character varying(6),
  solicitation_restrictions character(1),
  business_flag character(1),
  latitude character varying(11),
  longitude character varying(11),
  precision_code character(1),
  fips character varying(16),
  is_telco_unique boolean,
  vanity_city_name character varying(20),
  book_number character varying(6),
  web_address character varying(50),
  primary_bdc_flag character(1),
  msa character varying(4),
  is_amex_accepted boolean,
  is_mastercard_accepted boolean,
  is_visa_accepted boolean,
  is_discover_accepted boolean,
  is_diners_accepted boolean,
  is_other_cc_accepted boolean,
  fax character varying(10),
  free_eac character(1),
  hours_of_operation character(1),
  is_spanish_spoken boolean,
  is_french_spoken boolean,
  is_german_spoken boolean,
  is_japanese_spoken boolean,
  is_italian_spoken boolean,
  is_korean_spoken boolean,
  is_chinese_spoken boolean,
  senior_discount_key character(1),
  listing_type_fid bigint,
  CONSTRAINT pk_company_id PRIMARY KEY (id)
)
WITH (OIDS=FALSE);

--Table 2 - (Item B)  ~100k records
CREATE TABLE school
(
  id bigint NOT NULL DEFAULT nextval('global_sequence'::regclass),
  "name" character varying(65) NOT NULL,
  description character varying(100),
  address1 character varying(100),
  address2 character varying(100),
  city character varying(50),
  state character(2),
  CONSTRAINT pk_school_id PRIMARY KEY (id)
)
WITH (OIDS=FALSE);

--Joined View:
CREATE OR REPLACE VIEW directory_listing AS
 SELECT school.id, school.name, school.description, 119075291 AS listing_type_fid
   FROM school
UNION ALL
 SELECT company.id, company.name, company.description, 119074833 AS listing_type_fid
   FROM company;

--Listing-Classification  Xref:  ~26M records
CREATE TABLE listing_node_xref
(
  id bigint NOT NULL DEFAULT nextval('global_sequence'::regclass),
  listing_fid bigint NOT NULL,
  node_fid bigint NOT NULL,
  CONSTRAINT pk_listing_node_xref PRIMARY KEY (id),
  CONSTRAINT fk_listing_node_xref_node_fid FOREIGN KEY (node_fid)
      REFERENCES node (id) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE NO ACTION,
  CONSTRAINT uqe_listing_node_xref_listing_fid_node_fid UNIQUE (listing_fid, node_fid)
)
WITH (OIDS=FALSE);
ALTER TABLE listing_node_xref OWNER TO vml;

CREATE INDEX idx_listing_node_xref_listing_fid
  ON listing_node_xref
  USING btree
  (listing_fid);

CREATE INDEX idx_listing_node_xref_node_fid
  ON listing_node_xref
  USING btree
  (node_fid);

Here is the version of Postgres:  PostgreSQL 8.3.1

Query:
SELECT l.id, l.name, l.description, l.listing_type_fid
FROM  directory_listing l
    INNER JOIN listing_node_xref xref  ON  l.id = xref.listing_fid
WHERE xref.node_fid = 173204537

Explain:
Hash Join  (cost=48449.22..1223695.46 rows=11472 width=378)
  Hash Cond: (school.id = xref.listing_fid)
  ->  Append  (cost=0.00..945319.40 rows=18384970 width=378)
        ->  Seq Scan on school  (cost=0.00..10.80 rows=80 width=374)
        ->  Seq Scan on company  (cost=0.00..761458.90 rows=18384890 width=247)
  ->  Hash  (cost=48246.22..48246.22 rows=16240 width=8)
        ->  Bitmap Heap Scan on listing_node_xref xref  (cost=308.96..48246.22 rows=16240 width=8)
              Recheck Cond: (node_fid = 173204537)
              ->  Bitmap Index Scan on idx_listing_node_xref_node_fid  (cost=0.00..304.90 rows=16240 width=0)
                    Index Cond: (node_fid = 173204537)

Query:
select c.*
from company c
    inner join listing_node_xref xref on c.id = xref.listing_fid
where xref.node_fid = 173204537

Explain:
Nested Loop  (cost=308.96..205552.40 rows=11471 width=424)
  ->  Bitmap Heap Scan on listing_node_xref xref  (cost=308.96..48246.22 rows=16240 width=8)
        Recheck Cond: (node_fid = 173204537)
        ->  Bitmap Index Scan on idx_listing_node_xref_node_fid  (cost=0.00..304.90 rows=16240 width=0)
              Index Cond: (node_fid = 173204537)
  ->  Index Scan using pk_company_id on company c  (cost=0.00..9.67 rows=1 width=424)
        Index Cond: (c.id = xref.listing_fid)