pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  
help / color / mirror / Atom feed
Have: Seq Scan - Want: Index Scan - what am I doing wrong?
5+ messages / 3 participants
[nested] [flat]

* Have: Seq Scan - Want: Index Scan - what am I doing wrong?
@ 2012-10-16 23:52  Chris Ruprecht <chris@cdrbill.com>
  0 siblings, 1 reply; 5+ messages in thread

From: Chris Ruprecht @ 2012-10-16 23:52 UTC (permalink / raw)
  To: 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-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" );




^ permalink  raw  reply  [nested|flat] 5+ messages in thread

* Re: Have: Seq Scan - Want: Index Scan - what am I doing wrong?
@ 2012-10-17 00:01  Evgeny Shishkin <itparanoia@gmail.com>
  parent: Chris Ruprecht <chris@cdrbill.com>
  0 siblings, 1 reply; 5+ messages in thread

From: Evgeny Shishkin @ 2012-10-17 00:01 UTC (permalink / raw)
  To: Chris Ruprecht <chris@cdrbill.com>; +Cc: pgsql-performance


On Oct 17, 2012, at 3:52 AM, Chris Ruprecht <chris@cdrbill.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@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-performance





^ permalink  raw  reply  [nested|flat] 5+ messages in thread

* Re: Have: Seq Scan - Want: Index Scan - what am I doing wrong?
@ 2012-10-17 00:19  Chris Ruprecht <chris@cdrbill.com>
  parent: Evgeny Shishkin <itparanoia@gmail.com>
  0 siblings, 1 reply; 5+ messages in thread

From: Chris Ruprecht @ 2012-10-17 00:19 UTC (permalink / raw)
  To: Evgeny Shishkin <itparanoia@gmail.com>; +Cc: pgsql-performance


On Oct 16, 2012, at 20:01 , Evgeny Shishkin <itparanoia@gmail.com> wrote:

> 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'; ?
> 

Yes, there is an index:

"Aggregate  (cost=6427.06..6427.07 rows=1 width=0)"
"  ->  Index Scan using patient_pracsortpatientnumber on patient  (cost=0.00..6427.06 rows=1 width=0)"
"        Index Cond: (dnsortpersonnumber = '347450'::text)"


In fact, all the other criteria is picked using an index. I fear that the >= and <= on the timestamp is causing the issue. If I do a "=" of just one of them, I get an index scan. But I need to scan the entire range. I get queries like "give me everything that was entered into the system for this patient between these two dates". A single date wouldn't work.



^ permalink  raw  reply  [nested|flat] 5+ messages in thread

* Re: Have: Seq Scan - Want: Index Scan - what am I doing wrong?
@ 2012-10-17 00:31  Bruce Momjian <bruce@momjian.us>
  parent: Chris Ruprecht <chris@cdrbill.com>
  0 siblings, 1 reply; 5+ messages in thread

From: Bruce Momjian @ 2012-10-17 00:31 UTC (permalink / raw)
  To: Chris Ruprecht <chris@cdrbill.com>; +Cc: Evgeny Shishkin <itparanoia@gmail.com>; pgsql-performance

On Tue, Oct 16, 2012 at 08:19:43PM -0400, Chris Ruprecht wrote:
> 
> On Oct 16, 2012, at 20:01 , Evgeny Shishkin <itparanoia@gmail.com> wrote:
> 
> > 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'; ?
> > 
> 
> Yes, there is an index:
> 
> "Aggregate  (cost=6427.06..6427.07 rows=1 width=0)"
> "  ->  Index Scan using patient_pracsortpatientnumber on patient  (cost=0.00..6427.06 rows=1 width=0)"
> "        Index Cond: (dnsortpersonnumber = '347450'::text)"
> 
> 
> In fact, all the other criteria is picked using an index. I fear that the >= and <= on the timestamp is causing the issue. If I do a "=" of just one of them, I get an index scan. But I need to scan the entire range. I get queries like "give me everything that was entered into the system for this patient between these two dates". A single date wouldn't work.

Have you read our FAQ on this matter?

	http://wiki.postgresql.org/wiki/FAQ#Why_are_my_queries_slow.3F_Why_don.27t_they_use_my_indexes.3F

-- 
  Bruce Momjian  <bruce@momjian.us>        http://momjian.us
  EnterpriseDB                             http://enterprisedb.com

  + It's impossible for everything to be true. +




^ permalink  raw  reply  [nested|flat] 5+ messages in thread

* Re: Have: Seq Scan - Want: Index Scan - what am I doing wrong?
@ 2012-10-17 00:43  Chris Ruprecht <chris@cdrbill.com>
  parent: Bruce Momjian <bruce@momjian.us>
  0 siblings, 0 replies; 5+ messages in thread

From: Chris Ruprecht @ 2012-10-17 00:43 UTC (permalink / raw)
  To: Bruce Momjian <bruce@momjian.us>; +Cc: Evgeny Shishkin <itparanoia@gmail.com>; pgsql-performance

Thanks Bruce, 

I have, and I even thought, I understood it :). 

I just ran an explain analyze on another table - and ever since the query plan changed. It's now using the index as expected. I guess, I have some more reading to do.

On Oct 16, 2012, at 20:31 , Bruce Momjian <bruce@momjian.us> wrote:

> 
> Have you read our FAQ on this matter?
> 





^ permalink  raw  reply  [nested|flat] 5+ messages in thread


end of thread, other threads:[~2012-10-17 00:43 UTC | newest]

Thread overview: 5+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2012-10-16 23:52 Have: Seq Scan - Want: Index Scan - what am I doing wrong? Chris Ruprecht <chris@cdrbill.com>
2012-10-17 00:01 ` Evgeny Shishkin <itparanoia@gmail.com>
2012-10-17 00:19   ` Chris Ruprecht <chris@cdrbill.com>
2012-10-17 00:31     ` Bruce Momjian <bruce@momjian.us>
2012-10-17 00:43       ` Chris Ruprecht <chris@cdrbill.com>

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox