From: | Ivan Sergio Borgonovo <mail(at)webthatworks(dot)it> |
---|---|
To: | pgsql-general(at)postgresql(dot)org |
Subject: | Re: eliminating records not in (select id ... so SLOW? |
Date: | 2008-08-04 13:03:19 |
Message-ID: | 20080804150319.1687b340@dawn.webthatworks.it |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
On Fri, 01 Aug 2008 10:33:59 -0400
Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> Ivan Sergio Borgonovo <mail(at)webthatworks(dot)it> writes:
> > Well I reached 3Gb of work_mem and still I got:
>
> > "Seq Scan on catalog_categoryitem (cost=31747.84..4019284477.13
> > rows=475532 width=6)"
> > " Filter: (NOT (subplan))"
> > " SubPlan"
> > " -> Materialize (cost=31747.84..38509.51 rows=676167
> > width=8)" " -> Seq Scan on catalog_items
> > (cost=0.00..31071.67 rows=676167 width=8)"
>
> Huh. The only way I can see for that to happen is if the datatypes
> involved aren't hashable. What's the datatypes of the two columns
> being compared, anyway?
I changed both columns to bigint.
I added 2 indexes on the ItemID column of both tables and increased
work_mem to 3Gb [sic].
The query got executed in ~1300ms... but explain gave the same
output as the one above.
The problem is solved... but curious mind want to know.
--
Ivan Sergio Borgonovo
http://www.webthatworks.it
From | Date | Subject | |
---|---|---|---|
Next Message | rihad | 2008-08-04 13:27:37 | index speed and failed expectations? |
Previous Message | ravi kiran | 2008-08-04 12:24:12 | Fwd: Returning Cursor |