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-05 00: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" );
--
Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
