From: Scott Marlowe <smarlowe@g2switchworks.com>
To: Tim Jones <TJones@optio.com>
Cc: Dave Dutcher <dave@tridecap.com>
Cc: pgsql-performance@postgresql.org
Subject: Re: joining two tables slow due to sequential scan
Date: Fri, 10 Feb 2006 16:46:14 -0600
Message-ID: <1139611574.22740.154.camel@state.g2switchworks.com> (raw)
In-Reply-To: <47668A1334CDBF46927C1A0DFEB223D313131D@mail.optiosoftware.com>
References: <47668A1334CDBF46927C1A0DFEB223D313131D@mail.optiosoftware.com>
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.
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: smarlowe@g2switchworks.com, TJones@optio.com, dave@tridecap.com
Subject: Re: joining two tables slow due to sequential scan
In-Reply-To: <1139611574.22740.154.camel@state.g2switchworks.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