Have: Seq Scan - Want: Index Scan - what am I doing wrong? - Mailing list pgsql-performance

Hi guys,

PG = 9.1.5
OS = winDOS 2008R8

I have a table that currently has 207 million rows.
there is a timestamp field that contains data.
more data gets copied from another database into this database.
How do I make this do an index scan instead?
I did an "analyze audittrailclinical" to no avail.
I tested different indexes - no same behavior.

The query does this:

SELECT
audittrailclinical.pgid,
audittrailclinical.timestamp,
mmuser.logon,
audittrailclinical.entityname,
audittrailclinical.clinicalactivity,
audittrailclinical.audittraileventcode,
account.accountnumber,
patient.dnsortpersonnumber
FROM
public.account,
public.audittrailclinical,
public.encounter,
public.entity,
public.mmuser,
public.patient,
public.patientaccount
WHERE
     audittrailclinical.encountersid = encounter.encountersid
and   audittrailclinical.timestamp >= '2008-01-01'::timestamp without time zone
and   audittrailclinical.timestamp <= '2012-10-05'::timestamp without time zone
AND  encounter.practiceid = patient.practiceid
AND  encounter.patientid = patient.patientid
AND  encounter.staffid = patient.staffid
AND  entity.entitysid = audittrailclinical.entitysid
AND  mmuser.mmusersid = audittrailclinical.mmusersid
AND  patient.practiceid = patientaccount.practiceid
AND  patient.patientid = patientaccount.patientid
AND  patientaccount.accountsid = account.accountsid
AND  patientaccount.defaultaccount = 'Y'
AND  patient.dnsortpersonnumber = '347450' ;

The query plan says:

"              ->  Seq Scan on audittrailclinical  (cost=0.00..8637598.76 rows=203856829 width=62)"
"                    Filter: (("timestamp" >= '2008-01-01 00:00:00'::timestamp without time zone) AND ("timestamp" <=
'2012-10-0500:00:00'::timestamp without time zone))" 

which takes forever.

How do I make this do an index scan instead?
I did an "analyze audittrailclinical" to no avail.

the table definitions are (the createstamp field is empty - I know, bad data):

CREATE TABLE audittrailclinical
(
 audittrailid text,
 audittraileventcode text,
 clinicalactivity text,
 eventsuccessful text,
 externalunique text,
 recordstamp timestamp without time zone,
 recorddescription text,
 encountersid integer,
 eventuserlogon text,
 computername text,
 applicationcode text,
 practiceid integer,
 mmusersid integer,
 entitysid integer,
 entityname text,
 "timestamp" timestamp without time zone,
 lastuser integer,
 createstamp timestamp without time zone,
 pgid bigint DEFAULT nextval(('"bravepoint_seq"'::text)::regclass)
)
WITH (
 OIDS=FALSE
);
ALTER TABLE audittrailclinical
 OWNER TO intergy;
GRANT ALL ON TABLE audittrailclinical TO intergy;
GRANT SELECT ON TABLE audittrailclinical TO rb;

-- Index: atc_en_time

CREATE INDEX atc_en_time
 ON audittrailclinical
 USING btree
 (entitysid , "timestamp" );

-- Index: atc_id

-- DROP INDEX atc_id;

CREATE INDEX atc_id
 ON audittrailclinical
 USING btree
 (audittrailid COLLATE pg_catalog."default" );