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
lan_rows 3608
lan_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
pno 98
pfuncid 67
presulttype 16
pretset 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
lan_rows 3608
lan_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
pno 98
pfuncid 67
presulttype 16
pretset 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
pno 98
pfuncid 67
presulttype 16
pretset 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.)

---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://archives.postgresql.org

Jon Anderson <jonanderson.mn@gmail.com> writes:
> Any hints on what to do to make PostgreSQL use the index?

You might want to reduce random_page_cost a little.

Keep in mind that your test case is small enough to fit in RAM and is
probably not reflective of what will happen with larger tables.

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org