Re: Have: Seq Scan - Want: Index Scan - what am I doing wrong?

On Oct 17, 2012, at 3:52 AM, Chris Ruprecht <chris(at)cdrbill(dot)com> wrote:

> 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.
>

Selecting 5 yours of data is not selective at all, so postgres decides it is cheaper to do seqscan.

Do you have an index on patient.dnsortpersonnumber? Can you post a result from
select count(*) from patient where dnsortpersonnumber = '347450'; ?

> 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(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-performance