pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedjoining two tables slow due to sequential scan
14+ messages / 4 participants
[nested] [flat]
* joining two tables slow due to sequential scan
@ 2006-02-10 22:06 Tim Jones <TJones@optio.com>
0 siblings, 2 replies; 14+ messages in thread
From: Tim Jones @ 2006-02-10 22:06 UTC (permalink / raw)
To: pgsql-performance
I am trying to join two tables and keep getting a sequential scan in the
plan even though there is an index on the columns I am joining on.
Basically this the deal ... I have two tables with docid in them which
is what I am using for the join.
ClinicalDocs ... (no primary key) though it does not help if I make
docid primary key
docid integer (index)
patientid integer (index)
visitid integer (index)
...
Documentversions
docid integer (index)
docversionnumber (index)
docversionidentifier (primary key)
It seems to do an index scan if I put the primary key as docid. This is
what occurs when I link on the patid from ClinicalDocs to patient table.
However I can not make the docid primary key because it gets repeated
depending on how may versions of a document I have. I have tried using
a foreign key on documentversions with no sucess.
In addition this query
select * from documentversions join clinicaldocuments on
documentversions.documentidentifier
= clinicaldocuments.dssdocumentidentifier where
documentversions.documentstatus = 'AC';
does index scan
but if I change the order e.g
select * from clinicaldocuments join documentversions on
clinicaldocuments.dssdocumentidentifier
= documentversions .documentidentifier where
clinicaldocuments.patientidentifier= 123;
does sequential scan what I need is bottom query
it is extremely slow ... Any ideas ?
Tim Jones
Healthcare Project Manager
Optio Software, Inc.
(770) 576-3555
^ permalink raw reply [nested|flat] 14+ messages in thread
* Re: joining two tables slow due to sequential scan
@ 2006-02-10 22:14 Dave Dutcher <dave@tridecap.com>
parent: Tim Jones <TJones@optio.com>
1 sibling, 0 replies; 14+ messages in thread
From: Dave Dutcher @ 2006-02-10 22:14 UTC (permalink / raw)
To: 'Tim Jones' <TJones@optio.com>; pgsql-performance
What version of postgres are you using? Can you post the output from
EXPLAIN ANALYZE?
-----Original Message-----
From: pgsql-performance-owner@postgresql.org
[mailto:pgsql-performance-owner@postgresql.org] On Behalf Of Tim Jones
Sent: Friday, February 10, 2006 4:07 PM
To: pgsql-performance@postgresql.org
Subject: [PERFORM] joining two tables slow due to sequential scan
I am trying to join two tables and keep getting a sequential scan in the
plan even though there is an index on the columns I am joining on.
Basically this the deal ... I have two tables with docid in them which
is what I am using for the join.
ClinicalDocs ... (no primary key) though it does not help if I make
docid primary key
docid integer (index)
patientid integer (index)
visitid integer (index)
...
Documentversions
docid integer (index)
docversionnumber (index)
docversionidentifier (primary key)
It seems to do an index scan if I put the primary key as docid. This is
what occurs when I link on the patid from ClinicalDocs to patient table.
However I can not make the docid primary key because it gets repeated
depending on how may versions of a document I have. I have tried using
a foreign key on documentversions with no sucess.
In addition this query
select * from documentversions join clinicaldocuments on
documentversions.documentidentifier
= clinicaldocuments.dssdocumentidentifier where
documentversions.documentstatus = 'AC';
does index scan
but if I change the order e.g
select * from clinicaldocuments join documentversions on
clinicaldocuments.dssdocumentidentifier
= documentversions .documentidentifier where
clinicaldocuments.patientidentifier= 123;
does sequential scan what I need is bottom query
it is extremely slow ... Any ideas ?
Tim Jones
Healthcare Project Manager
Optio Software, Inc.
(770) 576-3555
^ permalink raw reply [nested|flat] 14+ messages in thread
* Re: joining two tables slow due to sequential scan
@ 2006-02-10 22:22 Scott Marlowe <smarlowe@g2switchworks.com>
parent: Tim Jones <TJones@optio.com>
1 sibling, 0 replies; 14+ messages in thread
From: Scott Marlowe @ 2006-02-10 22:22 UTC (permalink / raw)
To: Tim Jones <TJones@optio.com>; +Cc: pgsql-performance
On Fri, 2006-02-10 at 16:06, Tim Jones wrote:
>
> I am trying to join two tables and keep getting a sequential scan in
> the plan even though there is an index on the columns I am joining
> on. Basically this the deal ... I have two tables with docid in them
> which is what I am using for the join.
>
SNIP
> select * from documentversions join clinicaldocuments on
> documentversions.documentidentifier
> = clinicaldocuments.dssdocumentidentifier where
> documentversions.documentstatus = 'AC';
>
> does index scan
> but if I change the order e.g
>
> select * from clinicaldocuments join documentversions on
> clinicaldocuments.dssdocumentidentifier
> = documentversions .documentidentifier where
> clinicaldocuments.patientidentifier= 123;
OK. I'm gonna make a couple of guesses here:
1: clinicaldocuments.patientidentifier is an int8 and you're running
7.4 or before.
2: There are more rows with clinicaldocuments.patientidentifier= 123
than with documentversions.documentstatus = 'AC'.
3: documentversions.documentidentifier and
clinicaldocuments.dssdocumentidentifier are not the same type.
Any of those things true?
^ permalink raw reply [nested|flat] 14+ messages in thread
* Re: joining two tables slow due to sequential scan
@ 2006-02-10 22:35 Tim Jones <TJones@optio.com>
0 siblings, 1 reply; 14+ messages in thread
From: Tim Jones @ 2006-02-10 22:35 UTC (permalink / raw)
To: Scott Marlowe <smarlowe@g2switchworks.com>; +Cc: pgsql-performance
OK. I'm gonna make a couple of guesses here:
1: clinicaldocuments.patientidentifier is an int8 and you're running
7.4 or before.
-- nope int4 and 8.1
2: There are more rows with clinicaldocuments.patientidentifier= 123
than with documentversions.documentstatus = 'AC'.
-- nope generally speaking all statuses are 'AC'
3: documentversions.documentidentifier and
clinicaldocuments.dssdocumentidentifier are not the same type.
-- nope both int4
Any of those things true?
^ permalink raw reply [nested|flat] 14+ messages in thread
* Re: joining two tables slow due to sequential scan
@ 2006-02-10 22:36 Scott Marlowe <smarlowe@g2switchworks.com>
parent: Tim Jones <TJones@optio.com>
0 siblings, 0 replies; 14+ messages in thread
From: Scott Marlowe @ 2006-02-10 22:36 UTC (permalink / raw)
To: Tim Jones <TJones@optio.com>; +Cc: pgsql-performance
On Fri, 2006-02-10 at 16:35, Tim Jones wrote:
> OK. I'm gonna make a couple of guesses here:
>
> 1: clinicaldocuments.patientidentifier is an int8 and you're running
> 7.4 or before.
>
> -- nope int4 and 8.1
>
> 2: There are more rows with clinicaldocuments.patientidentifier= 123
> than with documentversions.documentstatus = 'AC'.
>
> -- nope generally speaking all statuses are 'AC'
>
> 3: documentversions.documentidentifier and
> clinicaldocuments.dssdocumentidentifier are not the same type.
>
> -- nope both int4
OK then, I guess we'll need to see the explain analyze output of both of
those queries.
^ permalink raw reply [nested|flat] 14+ messages in thread
* Re: joining two tables slow due to sequential scan
@ 2006-02-10 22:37 Tim Jones <TJones@optio.com>
0 siblings, 2 replies; 14+ messages in thread
From: Tim Jones @ 2006-02-10 22:37 UTC (permalink / raw)
To: Dave Dutcher <dave@tridecap.com>; pgsql-performance
for first query
QUERY PLAN
'Limit (cost=4.69..88.47 rows=10 width=1350) (actual
time=32.195..32.338 rows=10 loops=1)'
' -> Nested Loop (cost=4.69..4043.09 rows=482 width=1350) (actual
time=32.190..32.316 rows=10 loops=1)'
' -> Bitmap Heap Scan on documentversions (cost=4.69..1139.40
rows=482 width=996) (actual time=32.161..32.171 rows=10 loops=1)'
' Recheck Cond: (documentstatus = ''AC''::bpchar)'
' -> Bitmap Index Scan on ix_docstatus (cost=0.00..4.69
rows=482 width=0) (actual time=31.467..31.467 rows=96368 loops=1)'
' Index Cond: (documentstatus = ''AC''::bpchar)'
' -> Index Scan using ix_cdocdid on clinicaldocuments
(cost=0.00..6.01 rows=1 width=354) (actual time=0.006..0.007 rows=1
loops=10)'
' Index Cond: ("outer".documentidentifier =
clinicaldocuments.dssdocumentidentifier)'
for second query
QUERY PLAN
'Hash Join (cost=899.83..4384.17 rows=482 width=1350)'
' Hash Cond: ("outer".documentidentifier =
"inner".dssdocumentidentifier)'
' -> Seq Scan on documentversions (cost=0.00..2997.68 rows=96368
width=996)'
' -> Hash (cost=898.62..898.62 rows=482 width=354)'
' -> Bitmap Heap Scan on clinicaldocuments (cost=4.69..898.62
rows=482 width=354)'
' Recheck Cond: (patientidentifier = 123)'
' -> Bitmap Index Scan on ix_cdocpid (cost=0.00..4.69
rows=482 width=0)'
' Index Cond: (patientidentifier = 123)'
thnx
Tim Jones
Healthcare Project Manager
Optio Software, Inc.
(770) 576-3555
________________________________
From: Dave Dutcher [mailto:dave@tridecap.com]
Sent: Friday, February 10, 2006 5:15 PM
To: Tim Jones; pgsql-performance@postgresql.org
Subject: RE: [PERFORM] joining two tables slow due to sequential scan
What version of postgres are you using? Can you post the output from
EXPLAIN ANALYZE?
-----Original Message-----
From: pgsql-performance-owner@postgresql.org
[mailto:pgsql-performance-owner@postgresql.org] On Behalf Of Tim Jones
Sent: Friday, February 10, 2006 4:07 PM
To: pgsql-performance@postgresql.org
Subject: [PERFORM] joining two tables slow due to sequential scan
I am trying to join two tables and keep getting a sequential scan in the
plan even though there is an index on the columns I am joining on.
Basically this the deal ... I have two tables with docid in them which
is what I am using for the join.
ClinicalDocs ... (no primary key) though it does not help if I make
docid primary key
docid integer (index)
patientid integer (index)
visitid integer (index)
...
Documentversions
docid integer (index)
docversionnumber (index)
docversionidentifier (primary key)
It seems to do an index scan if I put the primary key as docid. This is
what occurs when I link on the patid from ClinicalDocs to patient table.
However I can not make the docid primary key because it gets repeated
depending on how may versions of a document I have. I have tried using
a foreign key on documentversions with no sucess.
In addition this query
select * from documentversions join clinicaldocuments on
documentversions.documentidentifier
= clinicaldocuments.dssdocumentidentifier where
documentversions.documentstatus = 'AC';
does index scan
but if I change the order e.g
select * from clinicaldocuments join documentversions on
clinicaldocuments.dssdocumentidentifier
= documentversions .documentidentifier where
clinicaldocuments.patientidentifier= 123;
does sequential scan what I need is bottom query
it is extremely slow ... Any ideas ?
Tim Jones
Healthcare Project Manager
Optio Software, Inc.
(770) 576-3555
^ permalink raw reply [nested|flat] 14+ messages in thread
* Re: joining two tables slow due to sequential scan
@ 2006-02-10 22:39 Scott Marlowe <smarlowe@g2switchworks.com>
parent: Tim Jones <TJones@optio.com>
1 sibling, 0 replies; 14+ messages in thread
From: Scott Marlowe @ 2006-02-10 22:39 UTC (permalink / raw)
To: Tim Jones <TJones@optio.com>; +Cc: Dave Dutcher <dave@tridecap.com>; pgsql-performance
On Fri, 2006-02-10 at 16:37, Tim Jones wrote:
> for first query
>
> QUERY PLAN
> 'Limit (cost=4.69..88.47 rows=10 width=1350) (actual
> time=32.195..32.338 rows=10 loops=1)'
> ' -> Nested Loop (cost=4.69..4043.09 rows=482 width=1350) (actual
> time=32.190..32.316 rows=10 loops=1)'
> ' -> Bitmap Heap Scan on documentversions (cost=4.69..1139.40
> rows=482 width=996) (actual time=32.161..32.171 rows=10 loops=1)'
> ' Recheck Cond: (documentstatus = ''AC''::bpchar)'
> ' -> Bitmap Index Scan on ix_docstatus (cost=0.00..4.69
> rows=482 width=0) (actual time=31.467..31.467 rows=96368 loops=1)'
> ' Index Cond: (documentstatus = ''AC''::bpchar)'
> ' -> Index Scan using ix_cdocdid on clinicaldocuments
> (cost=0.00..6.01 rows=1 width=354) (actual time=0.006..0.007 rows=1
> loops=10)'
> ' Index Cond: ("outer".documentidentifier =
> clinicaldocuments.dssdocumentidentifier)'
>
>
> for second query
>
> QUERY PLAN
> 'Hash Join (cost=899.83..4384.17 rows=482 width=1350)'
> ' Hash Cond: ("outer".documentidentifier =
> "inner".dssdocumentidentifier)'
> ' -> Seq Scan on documentversions (cost=0.00..2997.68 rows=96368
> width=996)'
> ' -> Hash (cost=898.62..898.62 rows=482 width=354)'
> ' -> Bitmap Heap Scan on clinicaldocuments (cost=4.69..898.62
> rows=482 width=354)'
> ' Recheck Cond: (patientidentifier = 123)'
> ' -> Bitmap Index Scan on ix_cdocpid (cost=0.00..4.69
> rows=482 width=0)'
> ' Index Cond: (patientidentifier = 123)'
OK, the first one is explain analyze, but the second one is just plain
explain...
^ permalink raw reply [nested|flat] 14+ messages in thread
* Re: joining two tables slow due to sequential scan
@ 2006-02-10 22:43 Tim Jones <TJones@optio.com>
0 siblings, 2 replies; 14+ messages in thread
From: Tim Jones @ 2006-02-10 22:43 UTC (permalink / raw)
To: Scott Marlowe <smarlowe@g2switchworks.com>; +Cc: Dave Dutcher <dave@tridecap.com>; pgsql-performance
oops
QUERY PLAN
'Hash Join (cost=899.83..4384.17 rows=482 width=1350) (actual
time=0.203..0.203 rows=0 loops=1)'
' Hash Cond: ("outer".documentidentifier =
"inner".dssdocumentidentifier)'
' -> Seq Scan on documentversions (cost=0.00..2997.68 rows=96368
width=996) (actual time=0.007..0.007 rows=1 loops=1)'
' -> Hash (cost=898.62..898.62 rows=482 width=354) (actual
time=0.161..0.161 rows=0 loops=1)'
' -> Bitmap Heap Scan on clinicaldocuments (cost=4.69..898.62
rows=482 width=354) (actual time=0.159..0.159 rows=0 loops=1)'
' Recheck Cond: (patientidentifier = 123)'
' -> Bitmap Index Scan on ix_cdocpid (cost=0.00..4.69
rows=482 width=0) (actual time=0.153..0.153 rows=0 loops=1)'
' Index Cond: (patientidentifier = 123)'
'Total runtime: 0.392 ms'
note I have done these on a smaller db than what I am using but the
plans are the same
Tim Jones
Healthcare Project Manager
Optio Software, Inc.
(770) 576-3555
-----Original Message-----
From: Scott Marlowe [mailto:smarlowe@g2switchworks.com]
Sent: Friday, February 10, 2006 5:39 PM
To: Tim Jones
Cc: Dave Dutcher; pgsql-performance@postgresql.org
Subject: Re: [PERFORM] joining two tables slow due to sequential scan
On Fri, 2006-02-10 at 16:37, Tim Jones wrote:
> for first query
>
> QUERY PLAN
> 'Limit (cost=4.69..88.47 rows=10 width=1350) (actual
> time=32.195..32.338 rows=10 loops=1)'
> ' -> Nested Loop (cost=4.69..4043.09 rows=482 width=1350) (actual
> time=32.190..32.316 rows=10 loops=1)'
> ' -> Bitmap Heap Scan on documentversions (cost=4.69..1139.40
> rows=482 width=996) (actual time=32.161..32.171 rows=10 loops=1)'
> ' Recheck Cond: (documentstatus = ''AC''::bpchar)'
> ' -> Bitmap Index Scan on ix_docstatus (cost=0.00..4.69
> rows=482 width=0) (actual time=31.467..31.467 rows=96368 loops=1)'
> ' Index Cond: (documentstatus = ''AC''::bpchar)'
> ' -> Index Scan using ix_cdocdid on clinicaldocuments
> (cost=0.00..6.01 rows=1 width=354) (actual time=0.006..0.007 rows=1
> loops=10)'
> ' Index Cond: ("outer".documentidentifier =
> clinicaldocuments.dssdocumentidentifier)'
>
>
> for second query
>
> QUERY PLAN
> 'Hash Join (cost=899.83..4384.17 rows=482 width=1350)'
> ' Hash Cond: ("outer".documentidentifier =
> "inner".dssdocumentidentifier)'
> ' -> Seq Scan on documentversions (cost=0.00..2997.68 rows=96368
> width=996)'
> ' -> Hash (cost=898.62..898.62 rows=482 width=354)'
> ' -> Bitmap Heap Scan on clinicaldocuments (cost=4.69..898.62
> rows=482 width=354)'
> ' Recheck Cond: (patientidentifier = 123)'
> ' -> Bitmap Index Scan on ix_cdocpid (cost=0.00..4.69
> rows=482 width=0)'
> ' Index Cond: (patientidentifier = 123)'
OK, the first one is explain analyze, but the second one is just plain
explain...
^ permalink raw reply [nested|flat] 14+ messages in thread
* Re: joining two tables slow due to sequential scan
@ 2006-02-10 22:44 Tom Lane <tgl@sss.pgh.pa.us>
parent: Tim Jones <TJones@optio.com>
1 sibling, 0 replies; 14+ messages in thread
From: Tom Lane @ 2006-02-10 22:44 UTC (permalink / raw)
To: Tim Jones <TJones@optio.com>; +Cc: Dave Dutcher <dave@tridecap.com>; pgsql-performance
"Tim Jones" <TJones@optio.com> writes:
> QUERY PLAN
> 'Hash Join (cost=899.83..4384.17 rows=482 width=1350)'
> ' Hash Cond: ("outer".documentidentifier =
> "inner".dssdocumentidentifier)'
This is not EXPLAIN ANALYZE output. Also, the rowcount estimates
seem far enough off in the other query to make me wonder how long
it's been since you ANALYZEd the tables...
More generally, though, I don't see anything particularly wrong
with this query plan. You're selecting enough of the table that
an indexscan isn't necessarily a good plan.
regards, tom lane
^ permalink raw reply [nested|flat] 14+ messages in thread
* Re: joining two tables slow due to sequential scan
@ 2006-02-10 22:46 Scott Marlowe <smarlowe@g2switchworks.com>
parent: Tim Jones <TJones@optio.com>
1 sibling, 0 replies; 14+ messages in thread
From: Scott Marlowe @ 2006-02-10 22:46 UTC (permalink / raw)
To: Tim Jones <TJones@optio.com>; +Cc: Dave Dutcher <dave@tridecap.com>; pgsql-performance
On Fri, 2006-02-10 at 16:43, Tim Jones wrote:
> oops
>
> QUERY PLAN
> 'Hash Join (cost=899.83..4384.17 rows=482 width=1350) (actual
> time=0.203..0.203 rows=0 loops=1)'
> ' Hash Cond: ("outer".documentidentifier =
> "inner".dssdocumentidentifier)'
> ' -> Seq Scan on documentversions (cost=0.00..2997.68 rows=96368
> width=996) (actual time=0.007..0.007 rows=1 loops=1)'
> ' -> Hash (cost=898.62..898.62 rows=482 width=354) (actual
> time=0.161..0.161 rows=0 loops=1)'
> ' -> Bitmap Heap Scan on clinicaldocuments (cost=4.69..898.62
> rows=482 width=354) (actual time=0.159..0.159 rows=0 loops=1)'
> ' Recheck Cond: (patientidentifier = 123)'
> ' -> Bitmap Index Scan on ix_cdocpid (cost=0.00..4.69
> rows=482 width=0) (actual time=0.153..0.153 rows=0 loops=1)'
> ' Index Cond: (patientidentifier = 123)'
> 'Total runtime: 0.392 ms'
>
> note I have done these on a smaller db than what I am using but the
> plans are the same
Hmmmm. We really need to see what's happening on the real database to
see what's going wrong. i.e. if the real database thinks it'll get 30
rows and it gets back 5,000,000 that's a problem.
The query planner in pgsql is cost based, so until you have real data
underneath it, and analyze it, you can't really say how it will behave
for you. I.e. small test sets don't work.
^ permalink raw reply [nested|flat] 14+ messages in thread
* Re: joining two tables slow due to sequential scan
@ 2006-02-10 22:51 Tom Lane <tgl@sss.pgh.pa.us>
parent: Tim Jones <TJones@optio.com>
1 sibling, 0 replies; 14+ messages in thread
From: Tom Lane @ 2006-02-10 22:51 UTC (permalink / raw)
To: Tim Jones <TJones@optio.com>; +Cc: Scott Marlowe <smarlowe@g2switchworks.com>; Dave Dutcher <dave@tridecap.com>; pgsql-performance
"Tim Jones" <TJones@optio.com> writes:
> QUERY PLAN
> 'Hash Join (cost=899.83..4384.17 rows=482 width=1350) (actual
> time=0.203..0.203 rows=0 loops=1)'
> ...
> 'Total runtime: 0.392 ms'
Hardly seems like evidence of a performance problem ...
regards, tom lane
^ permalink raw reply [nested|flat] 14+ messages in thread
* Re: joining two tables slow due to sequential scan
@ 2006-02-10 22:59 Tim Jones <TJones@optio.com>
0 siblings, 1 reply; 14+ messages in thread
From: Tim Jones @ 2006-02-10 22:59 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: pgsql-performance
ok here is real db
the first query I had seems to make no sense because it is only fast if
I limit the rows since almost all rows have status = 'AC'
second query
tables both have about 10 million rows and it takes a long time as you
can see but this person only has approx 160 total documents
QUERY PLAN
------------------------------------------------------------------------
-------------------------------------------------------------------
Hash Join (cost=84813.14..1510711.97 rows=48387 width=555) (actual
time=83266.854..91166.315 rows=3 loops=1)
Hash Cond: ("outer".documentidentifier =
"inner".dssdocumentidentifier)
-> Seq Scan on documentversions (cost=0.00..269141.98 rows=9677398
width=415) (actual time=0.056..49812.459 rows=9677398 loops=1)
-> Hash (cost=83660.05..83660.05 rows=48036 width=140) (actual
time=10.833..10.833 rows=3 loops=1)
-> Bitmap Heap Scan on clinicaldocuments
(cost=301.13..83660.05 rows=48036 width=140) (actual time=0.243..0.258
rows=3 loops=1)
Recheck Cond: (patientidentifier = 690193)
-> Bitmap Index Scan on ix_cdocpid (cost=0.00..301.13
rows=48036 width=0) (actual time=0.201..0.201 rows=3 loops=1)
Index Cond: (patientidentifier = 690193)
Total runtime: 91166.540 ms
Tim Jones
Healthcare Project Manager
Optio Software, Inc.
(770) 576-3555
-----Original Message-----
From: Tom Lane [mailto:tgl@sss.pgh.pa.us]
Sent: Friday, February 10, 2006 5:52 PM
To: Tim Jones
Cc: Scott Marlowe; Dave Dutcher; pgsql-performance@postgresql.org
Subject: Re: [PERFORM] joining two tables slow due to sequential scan
"Tim Jones" <TJones@optio.com> writes:
> QUERY PLAN
> 'Hash Join (cost=899.83..4384.17 rows=482 width=1350) (actual
> time=0.203..0.203 rows=0 loops=1)'
> ...
> 'Total runtime: 0.392 ms'
Hardly seems like evidence of a performance problem ...
regards, tom lane
^ permalink raw reply [nested|flat] 14+ messages in thread
* Re: joining two tables slow due to sequential scan
@ 2006-02-10 23:25 Dave Dutcher <dave@tridecap.com>
parent: Tim Jones <TJones@optio.com>
0 siblings, 0 replies; 14+ messages in thread
From: Dave Dutcher @ 2006-02-10 23:25 UTC (permalink / raw)
To: 'Tim Jones' <TJones@optio.com>; +Cc: pgsql-performance
OK, if I'm reading this correctly, it looks like the planner is choosing
a sequential scan because it expects 48,000 rows for that
patientidentifier, but its actually only getting 3. The planner has the
number of rows right for the sequential scan, so it seems like the stats
are up to date. I would try increasing the stats for the
patientindentifier column with 'alter table set statistics...' or
increasing the default_statistics_target for the whole DB. Once you
have changed the stats I believe you need to run analyze again.
-----Original Message-----
From: pgsql-performance-owner@postgresql.org
[mailto:pgsql-performance-owner@postgresql.org] On Behalf Of Tim Jones
Sent: Friday, February 10, 2006 4:59 PM
To: Tom Lane
Cc: pgsql-performance@postgresql.org
Subject: Re: [PERFORM] joining two tables slow due to sequential scan
ok here is real db
the first query I had seems to make no sense because it is only fast if
I limit the rows since almost all rows have status = 'AC'
second query
tables both have about 10 million rows and it takes a long time as you
can see but this person only has approx 160 total documents
QUERY PLAN
------------------------------------------------------------------------
-------------------------------------------------------------------
Hash Join (cost=84813.14..1510711.97 rows=48387 width=555) (actual
time=83266.854..91166.315 rows=3 loops=1)
Hash Cond: ("outer".documentidentifier =
"inner".dssdocumentidentifier)
-> Seq Scan on documentversions (cost=0.00..269141.98 rows=9677398
width=415) (actual time=0.056..49812.459 rows=9677398 loops=1)
-> Hash (cost=83660.05..83660.05 rows=48036 width=140) (actual
time=10.833..10.833 rows=3 loops=1)
-> Bitmap Heap Scan on clinicaldocuments
(cost=301.13..83660.05 rows=48036 width=140) (actual time=0.243..0.258
rows=3 loops=1)
Recheck Cond: (patientidentifier = 690193)
-> Bitmap Index Scan on ix_cdocpid (cost=0.00..301.13
rows=48036 width=0) (actual time=0.201..0.201 rows=3 loops=1)
Index Cond: (patientidentifier = 690193)
Total runtime: 91166.540 ms
Tim Jones
Healthcare Project Manager
Optio Software, Inc.
(770) 576-3555
-----Original Message-----
From: Tom Lane [mailto:tgl@sss.pgh.pa.us]
Sent: Friday, February 10, 2006 5:52 PM
To: Tim Jones
Cc: Scott Marlowe; Dave Dutcher; pgsql-performance@postgresql.org
Subject: Re: [PERFORM] joining two tables slow due to sequential scan
"Tim Jones" <TJones@optio.com> writes:
> QUERY PLAN
> 'Hash Join (cost=899.83..4384.17 rows=482 width=1350) (actual
> time=0.203..0.203 rows=0 loops=1)'
> ...
> 'Total runtime: 0.392 ms'
Hardly seems like evidence of a performance problem ...
regards, tom lane
---------------------------(end of broadcast)---------------------------
TIP 5: don't forget to increase your free space map settings
^ permalink raw reply [nested|flat] 14+ messages in thread
* Re: joining two tables slow due to sequential scan
@ 2006-02-13 16:21 Tim Jones <TJones@optio.com>
0 siblings, 0 replies; 14+ messages in thread
From: Tim Jones @ 2006-02-13 16:21 UTC (permalink / raw)
To: Dave Dutcher <dave@tridecap.com>; +Cc: pgsql-performance
ok I am retarded :) Apparently I thought I had done analyze on these
tables but I actually had not and that was all that was needed. but
thanks for the help.
Tim Jones
Healthcare Project Manager
Optio Software, Inc.
(770) 576-3555
-----Original Message-----
From: Dave Dutcher [mailto:dave@tridecap.com]
Sent: Friday, February 10, 2006 6:25 PM
To: Tim Jones
Cc: pgsql-performance@postgresql.org
Subject: RE: [PERFORM] joining two tables slow due to sequential scan
OK, if I'm reading this correctly, it looks like the planner is choosing
a sequential scan because it expects 48,000 rows for that
patientidentifier, but its actually only getting 3. The planner has the
number of rows right for the sequential scan, so it seems like the stats
are up to date. I would try increasing the stats for the
patientindentifier column with 'alter table set statistics...' or
increasing the default_statistics_target for the whole DB. Once you
have changed the stats I believe you need to run analyze again.
-----Original Message-----
From: pgsql-performance-owner@postgresql.org
[mailto:pgsql-performance-owner@postgresql.org] On Behalf Of Tim Jones
Sent: Friday, February 10, 2006 4:59 PM
To: Tom Lane
Cc: pgsql-performance@postgresql.org
Subject: Re: [PERFORM] joining two tables slow due to sequential scan
ok here is real db
the first query I had seems to make no sense because it is only fast if
I limit the rows since almost all rows have status = 'AC'
second query
tables both have about 10 million rows and it takes a long time as you
can see but this person only has approx 160 total documents
QUERY PLAN
------------------------------------------------------------------------
-------------------------------------------------------------------
Hash Join (cost=84813.14..1510711.97 rows=48387 width=555) (actual
time=83266.854..91166.315 rows=3 loops=1)
Hash Cond: ("outer".documentidentifier =
"inner".dssdocumentidentifier)
-> Seq Scan on documentversions (cost=0.00..269141.98 rows=9677398
width=415) (actual time=0.056..49812.459 rows=9677398 loops=1)
-> Hash (cost=83660.05..83660.05 rows=48036 width=140) (actual
time=10.833..10.833 rows=3 loops=1)
-> Bitmap Heap Scan on clinicaldocuments
(cost=301.13..83660.05 rows=48036 width=140) (actual time=0.243..0.258
rows=3 loops=1)
Recheck Cond: (patientidentifier = 690193)
-> Bitmap Index Scan on ix_cdocpid (cost=0.00..301.13
rows=48036 width=0) (actual time=0.201..0.201 rows=3 loops=1)
Index Cond: (patientidentifier = 690193) Total
runtime: 91166.540 ms
Tim Jones
Healthcare Project Manager
Optio Software, Inc.
(770) 576-3555
-----Original Message-----
From: Tom Lane [mailto:tgl@sss.pgh.pa.us]
Sent: Friday, February 10, 2006 5:52 PM
To: Tim Jones
Cc: Scott Marlowe; Dave Dutcher; pgsql-performance@postgresql.org
Subject: Re: [PERFORM] joining two tables slow due to sequential scan
"Tim Jones" <TJones@optio.com> writes:
> QUERY PLAN
> 'Hash Join (cost=899.83..4384.17 rows=482 width=1350) (actual
> time=0.203..0.203 rows=0 loops=1)'
> ...
> 'Total runtime: 0.392 ms'
Hardly seems like evidence of a performance problem ...
regards, tom lane
---------------------------(end of broadcast)---------------------------
TIP 5: don't forget to increase your free space map settings
^ permalink raw reply [nested|flat] 14+ messages in thread
end of thread, other threads:[~2006-02-13 16:21 UTC | newest]
Thread overview: 14+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2006-02-10 22:06 joining two tables slow due to sequential scan Tim Jones <TJones@optio.com>
2006-02-10 22:14 ` Dave Dutcher <dave@tridecap.com>
2006-02-10 22:22 ` Scott Marlowe <smarlowe@g2switchworks.com>
2006-02-10 22:35 Re: joining two tables slow due to sequential scan Tim Jones <TJones@optio.com>
2006-02-10 22:36 ` Scott Marlowe <smarlowe@g2switchworks.com>
2006-02-10 22:37 Re: joining two tables slow due to sequential scan Tim Jones <TJones@optio.com>
2006-02-10 22:39 ` Scott Marlowe <smarlowe@g2switchworks.com>
2006-02-10 22:44 ` Tom Lane <tgl@sss.pgh.pa.us>
2006-02-10 22:43 Re: joining two tables slow due to sequential scan Tim Jones <TJones@optio.com>
2006-02-10 22:46 ` Scott Marlowe <smarlowe@g2switchworks.com>
2006-02-10 22:51 ` Tom Lane <tgl@sss.pgh.pa.us>
2006-02-10 22:59 Re: joining two tables slow due to sequential scan Tim Jones <TJones@optio.com>
2006-02-10 23:25 ` Dave Dutcher <dave@tridecap.com>
2006-02-13 16:21 Re: joining two tables slow due to sequential scan Tim Jones <TJones@optio.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