Message-ID: <44E9EEF9.7050901@intrusic.com>
Date: Mon, 21 Aug 2006 13:35:53 -0400
From: Scott Matseas <smatseas@intrusic.com>
User-Agent: Thunderbird 1.5.0.5 (Windows/20060719)
MIME-Version: 1.0
To: pgsql-performance@postgresql.org
Subject: Index usage
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

We're having a problem with one of our queries being slow. It appears to be due
to the index being used to go from tableA to tableB.

Here are the tables:
CREATE TABLE tableA
(
  table_idA int8 NOT NULL DEFAULT nextval('tableA_id_seq'::regclass),
  CONSTRAINT table_idA_pk PRIMARY KEY (table_idA),
) 
WITHOUT OIDS;

CREATE TABLE tableB
(
  table_idB int8 NOT NULL DEFAULT nextval('tableB_id_seq'::regclass),
  table_idA int8 NOT NULL,
  direction char NOT NULL,
  CONSTRAINT tableB_pk PRIMARY KEY (table_idB),
  CONSTRAINT tableB_unq UNIQUE (table_idA, direction),
) 
WITHOUT OIDS;

CREATE TABLE last_summarized
(
  summary_name varchar(64) NOT NULL,
  summarized_id int8,
  max_session_id int8,
  CONSTRAINT last_summarized_pk PRIMARY KEY (summary_name)
) 
WITHOUT OIDS;

Here is the query:
explain
 SELECT * FROM 
   last_summarized ls
   JOIN tableA s ON s.table_idA > ls.summarized_id AND s.table_idA
 <= ls.max_session_id
   LEFT JOIN tableB sf ON s.table_idA = sf.table_idA AND sf.direction = 'a'::"char"
   LEFT JOIN tableB sfb ON s.table_idA = sfb.table_idA AND sfb.direction = 'b'::"char"
  WHERE ls.summary_name::text = 'summary'::text 

Size of tables in # of rows
tableA: 9,244,816
tableB: 15,398,497
last_summarized: 1


Explain of the above query:
"Hash Left Join  (cost=1811349.31..18546527.89 rows=1029087 width=294)"
"  Hash Cond: ("outer".table_idA = "inner".table_idA)"
"  ->  Hash Left Join  (cost=915760.88..7519203.61 rows=1029087 width=219)"
"        Hash Cond: ("outer".table_idA = "inner".table_idA)"
"        ->  Nested Loop  (cost=0.00..126328.57 rows=1029087 width=144)"
"              ->  Index Scan using last_summarized_pk on last_summarized ls  (cost=0.00..5.98 rows=1 width=82)"
"                    Index Cond: ((summary_name)::text = 'summary'::text)"
"              ->  Index Scan using table_idA_pk on tableA s  (cost=0.00..110886.29 rows=1029087 width=62)"
"                    Index Cond: ((s.table_idA > "outer".summarized_id) AND (s.table_idA <= "outer".max_session_id))"
"        ->  Hash  (cost=784763.16..784763.16 rows=8100289 width=75)"
"              ->  Bitmap Heap Scan on tableB sf  (cost=216418.55..784763.16 rows=8100289 width=75)"
"                    Recheck Cond: (direction = 'a'::"char")"
"                    ->  Bitmap Index Scan on tableB_unq  (cost=0.00..216418.55 rows=8100289 width=0)"
"                          Index Cond: (direction = 'a'::"char")"     <------ USING part of Index
"  ->  Hash  (cost=775968.61..775968.61 rows=7396725 width=75)"
"        ->  Bitmap Heap Scan on tableB sfb  (cost=216418.55..775968.61 rows=7396725 width=75)"
"              Recheck Cond: (direction = 'b'::"char")"
"              ->  Bitmap Index Scan on tableB_unq  (cost=0.00..216418.55 rows=7396725 width=0)"
"                    Index Cond: (direction = 'b'::"char")"     <------ USING part of Index