From: Dave Dutcher <dave@tridecap.com>
To: 'Tim Jones' <TJones@optio.com>
Cc: pgsql-performance@postgresql.org
Subject: Re: joining two tables slow due to sequential scan
Date: Fri, 10 Feb 2006 17:25:20 -0600
Message-ID: <002601c62e99$434299b0$8300a8c0@tridecap.com> (raw)
In-Reply-To: <47668A1334CDBF46927C1A0DFEB223D3131324@mail.optiosoftware.com>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: dave@tridecap.com, TJones@optio.com
Subject: Re: joining two tables slow due to sequential scan
In-Reply-To: <002601c62e99$434299b0$8300a8c0@tridecap.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
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