Skip site navigation (1) Skip section navigation (2)

Seqscan rather than Index

From: Jon Anderson <jonanderson(dot)mn(at)gmail(dot)com>
To: pgsql-performance(at)postgresql(dot)org
Subject: Seqscan rather than Index
Date: 2004-12-16 17:08:20
Message-ID: 9c7da1c904121609081705d8cc@mail.gmail.com (view raw or flat)
Thread:
Lists: pgsql-performance
I have a table 'Alias' with 541162 rows.  It's created as follows:

CREATE TABLE alias
(
  id int4 NOT NULL,
  person_id int4 NOT NULL,
  last_name varchar(30),
  first_name varchar(30),
  middle_name varchar(30),
  questioned_identity_flag varchar,
  CONSTRAINT alias_pkey PRIMARY KEY (id)
) 

After populating the data, (I can provide a data file if necessary)
 I created 2 indexes as follows:
CREATE INDEX "PX_Alias"  ON alias  USING btree  (id);
ALTER TABLE alias CLUSTER ON "PX_Alias";
CREATE INDEX "IX_Alias_Last_Name"  ON alias  USING btree (last_name);
VACUUM FULL ANALYSE Alias

Then I run a query:
SELECT * FROM Alias WHERE last_name = 'ANDERSON'  
This results in a seqscan, rather than an index scan:
   {SEQSCAN
   :startup_cost 0.00 
   :total_cost 11970.53 
   :plan_rows 3608 
   :plan_width 41 
   :targetlist (
      {TARGETENTRY
      :resdom
         {RESDOM
         :resno 1 
         :restype 23 
         :restypmod -1 
         :resname id 
         :ressortgroupref 0 
         :resorigtbl 2780815 
         :resorigcol 1 
         :resjunk false
         }
      :expr
         {VAR 
         :varno 1 
         :varattno 1 
         :vartype 23 
         :vartypmod -1 
         :varlevelsup 0 
         :varnoold 1 
         :varoattno 1
         }
      }
      {TARGETENTRY
      :resdom
         {RESDOM
         :resno 2 
         :restype 23 
         :restypmod -1 
         :resname person_id 
         :ressortgroupref 0 
         :resorigtbl 2780815 
         :resorigcol 2 
         :resjunk false
         }
      :expr
         {VAR 
         :varno 1 
         :varattno 2 
         :vartype 23 
         :vartypmod -1 
         :varlevelsup 0 
         :varnoold 1 
         :varoattno 2
         }
      }
      {TARGETENTRY
      :resdom
         {RESDOM
         :resno 3 
         :restype 1043 
         :restypmod 34 
         :resname last_name 
         :ressortgroupref 0 
         :resorigtbl 2780815 
         :resorigcol 3 
         :resjunk false
         }
      :expr
         {VAR 
         :varno 1 
         :varattno 3 
         :vartype 1043 
         :vartypmod 34 
         :varlevelsup 0 
         :varnoold 1 
         :varoattno 3
         }
      }
      {TARGETENTRY
      :resdom
         {RESDOM
         :resno 4 
         :restype 1043 
         :restypmod 34 
         :resname first_name 
         :ressortgroupref 0 
         :resorigtbl 2780815 
         :resorigcol 4 
         :resjunk false
         }
      :expr
         {VAR 
         :varno 1 
         :varattno 4 
         :vartype 1043 
         :vartypmod 34 
         :varlevelsup 0 
         :varnoold 1 
         :varoattno 4
         }
      }
      {TARGETENTRY
      :resdom
         {RESDOM
         :resno 5 
         :restype 1043 
         :restypmod 34 
         :resname middle_name 
         :ressortgroupref 0 
         :resorigtbl 2780815 
         :resorigcol 5 
         :resjunk false
         }
      :expr
         {VAR 
         :varno 1 
         :varattno 5 
         :vartype 1043 
         :vartypmod 34 
         :varlevelsup 0 
         :varnoold 1 
         :varoattno 5
         }
      }
      {TARGETENTRY
      :resdom
         {RESDOM
         :resno 6 
         :restype 1043 
         :restypmod -1 
         :resname questioned_identity_flag 
         :ressortgroupref 0 
         :resorigtbl 2780815 
         :resorigcol 6 
         :resjunk false
         }
      :expr
         {VAR 
         :varno 1 
         :varattno 6 
         :vartype 1043 
         :vartypmod -1 
         :varlevelsup 0 
         :varnoold 1 
         :varoattno 6
         }
      }
   )
   :qual (
      {OPEXPR 
      :opno 98 
      :opfuncid 67 
      :opresulttype 16 
      :opretset false 
      :args (
         {RELABELTYPE 
         :arg 
            {VAR 
            :varno 1 
            :varattno 3 
            :vartype 1043 
            :vartypmod 34 
            :varlevelsup 0 
            :varnoold 1 
            :varoattno 3
            }
         :resulttype 25 
         :resulttypmod -1 
         :relabelformat 0
         }
         {CONST 
         :consttype 25 
         :constlen -1 
         :constbyval false 
         :constisnull false 
         :constvalue 12 [ 12 0 0 0 65 78 68 69 82 83 79 78 ]
         }
      )
      }
   )
   :lefttree <> 
   :righttree <> 
   :initPlan <> 
   :extParam (b)
   :allParam (b)
   :nParamExec 0 
   :scanrelid 1
   }

Seq Scan on alias  (cost=0.00..11970.53 rows=3608 width=41) (actual
time=0.000..2103.000 rows=4443 loops=1)
  Filter: ((last_name)::text = 'ANDERSON'::text)
Total runtime: 2153.000 ms


If I:
SET enable_seqscan TO off;

Then the query takes about 300 milliseconds, and uses the index scan. 
It seems that the cost estimate is slightly higher for the index scan,
but in reality, it is much faster:


   {INDEXSCAN 
   :startup_cost 0.00 
   :total_cost 12148.18 
   :plan_rows 3608 
   :plan_width 41 
   :targetlist (
      {TARGETENTRY
      :resdom
         {RESDOM
         :resno 1 
         :restype 23 
         :restypmod -1 
         :resname id 
         :ressortgroupref 0 
         :resorigtbl 2780815 
         :resorigcol 1 
         :resjunk false
         }
      :expr
         {VAR 
         :varno 1 
         :varattno 1 
         :vartype 23 
         :vartypmod -1 
         :varlevelsup 0 
         :varnoold 1 
         :varoattno 1
         }
      }
      {TARGETENTRY
      :resdom
         {RESDOM
         :resno 2 
         :restype 23 
         :restypmod -1 
         :resname person_id 
         :ressortgroupref 0 
         :resorigtbl 2780815 
         :resorigcol 2 
         :resjunk false
         }
      :expr
         {VAR 
         :varno 1 
         :varattno 2 
         :vartype 23 
         :vartypmod -1 
         :varlevelsup 0 
         :varnoold 1 
         :varoattno 2
         }
      }
      {TARGETENTRY
      :resdom
         {RESDOM
         :resno 3 
         :restype 1043 
         :restypmod 34 
         :resname last_name 
         :ressortgroupref 0 
         :resorigtbl 2780815 
         :resorigcol 3 
         :resjunk false
         }
      :expr
         {VAR 
         :varno 1 
         :varattno 3 
         :vartype 1043 
         :vartypmod 34 
         :varlevelsup 0 
         :varnoold 1 
         :varoattno 3
         }
      }
      {TARGETENTRY
      :resdom
         {RESDOM
         :resno 4 
         :restype 1043 
         :restypmod 34 
         :resname first_name 
         :ressortgroupref 0 
         :resorigtbl 2780815 
         :resorigcol 4 
         :resjunk false
         }
      :expr
         {VAR 
         :varno 1 
         :varattno 4 
         :vartype 1043 
         :vartypmod 34 
         :varlevelsup 0 
         :varnoold 1 
         :varoattno 4
         }
      }
      {TARGETENTRY
      :resdom
         {RESDOM
         :resno 5 
         :restype 1043 
         :restypmod 34 
         :resname middle_name 
         :ressortgroupref 0 
         :resorigtbl 2780815 
         :resorigcol 5 
         :resjunk false
         }
      :expr
         {VAR 
         :varno 1 
         :varattno 5 
         :vartype 1043 
         :vartypmod 34 
         :varlevelsup 0 
         :varnoold 1 
         :varoattno 5
         }
      }
      {TARGETENTRY
      :resdom
         {RESDOM
         :resno 6 
         :restype 1043 
         :restypmod -1 
         :resname questioned_identity_flag 
         :ressortgroupref 0 
         :resorigtbl 2780815 
         :resorigcol 6 
         :resjunk false
         }
      :expr
         {VAR 
         :varno 1 
         :varattno 6 
         :vartype 1043 
         :vartypmod -1 
         :varlevelsup 0 
         :varnoold 1 
         :varoattno 6
         }
      }
   )
   :qual <> 
   :lefttree <> 
   :righttree <> 
   :initPlan <> 
   :extParam (b)
   :allParam (b)
   :nParamExec 0 
   :scanrelid 1 
   :indxid (o 5117678)
   :indxqual ((
      {OPEXPR 
      :opno 98 
      :opfuncid 67 
      :opresulttype 16 
      :opretset false 
      :args (
         {VAR 
         :varno 1 
         :varattno 1 
         :vartype 1043 
         :vartypmod 34 
         :varlevelsup 0 
         :varnoold 1 
         :varoattno 3
         }
         {CONST 
         :consttype 25 
         :constlen -1 
         :constbyval false 
         :constisnull false 
         :constvalue 12 [ 12 0 0 0 65 78 68 69 82 83 79 78 ]
         }
      )
      }
   ))
   :indxqualorig ((
      {OPEXPR 
      :opno 98 
      :opfuncid 67 
      :opresulttype 16 
      :opretset false 
      :args (
         {RELABELTYPE 
         :arg 
            {VAR 
            :varno 1 
            :varattno 3 
            :vartype 1043 
            :vartypmod 34 
            :varlevelsup 0 
            :varnoold 1 
            :varoattno 3
            }
         :resulttype 25 
         :resulttypmod -1 
         :relabelformat 0
         }
         {CONST 
         :consttype 25 
         :constlen -1 
         :constbyval false 
         :constisnull false 
         :constvalue 12 [ 12 0 0 0 65 78 68 69 82 83 79 78 ]
         }
      )
      }
   ))
   :indxstrategy ((i 3))
   :indxsubtype ((o 0))
   :indxlossy ((i 0))
   :indxorderdir 1
   }

Index Scan using "IX_Alias_Last_Name" on alias  (cost=0.00..12148.18
rows=3608 width=41) (actual time=0.000..200.000 rows=4443 loops=1)
  Index Cond: ((last_name)::text = 'ANDERSON'::text)
Total runtime: 220.000 ms

Dropping the index and cluster on the id doesn't make any difference.

According to the pg_stats table,  'ANDERSON' is one of the most
frequent values; howerver, querying by another 'JACKSON', will use the
index scan.

Any hints on what to do to make PostgreSQL use the index?  This seems
like a fairly simple case, isn't it?  (I'm using 8.0-rc1 on windows.)

Responses

pgsql-performance by date

Next:From: Richard HuxtonDate: 2004-12-16 17:15:29
Subject: Re: Improve performance of query
Previous:From: Stephen FrostDate: 2004-12-16 17:02:20
Subject: Re: Improve performance of query

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group