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
Views: Raw Message | Whole Thread | Download mbox | Resend email
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

Browse pgsql-performance by date

  From Date Subject
Next Message Richard Huxton 2004-12-16 17:15:29 Re: Improve performance of query
Previous Message Stephen Frost 2004-12-16 17:02:20 Re: Improve performance of query