pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedIndex Usage using IN
6+ messages / 5 participants
[nested] [flat]
* Index Usage using IN
@ 2006-02-01 20:12 Ralph Mason <ralph.mason@telogis.com>
2006-02-01 20:22 ` Re: Index Usage using IN Jeffrey W. Baker <jwbaker@acm.org>
2006-02-01 21:23 ` Re: Index Usage using IN Bruno Wolff III <bruno@wolff.to>
2006-02-01 22:12 ` Re: Index Usage using IN Hari Warrier <hbhaskaran@gmail.com>
0 siblings, 3 replies; 6+ messages in thread
From: Ralph Mason @ 2006-02-01 20:12 UTC (permalink / raw)
To: pgsql-performance
Hi,
I have 2 tables both have an index on ID (both ID columns are an oid).
I want to find only only rows in one and not the other.
Select ID from TableA where ID not IN ( Select ID from Table B)
This always generates sequential scans.
Table A has about 250,000 rows. Table B has about 250,000 Rows.
We should get a Scan on Table B and a Index Lookup on Table A.
Is there any way to force this? enable_seqscan off doesn't help at all.
The Plan is
Seq Scan on tablea(cost=100000000.00..23883423070450.96 rows=119414 width=4)
Filter: (NOT (subplan))"
SubPlan ->
Seq Scan on tableb (cost=100000000.00..100004611.17 rows=242617
width=4)
Thanks
Ralph
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Index Usage using IN
2006-02-01 20:12 Index Usage using IN Ralph Mason <ralph.mason@telogis.com>
@ 2006-02-01 20:22 ` Jeffrey W. Baker <jwbaker@acm.org>
2006-02-01 20:28 ` Re: Index Usage using IN Jeffrey W. Baker <jwbaker@acm.org>
2006-02-01 20:41 ` Re: Index Usage using IN Tom Lane <tgl@sss.pgh.pa.us>
2 siblings, 2 replies; 6+ messages in thread
From: Jeffrey W. Baker @ 2006-02-01 20:22 UTC (permalink / raw)
To: pgsql-performance
On Thu, 2006-02-02 at 09:12 +1300, Ralph Mason wrote:
> Hi,
>
> I have 2 tables both have an index on ID (both ID columns are an oid).
>
> I want to find only only rows in one and not the other.
>
> Select ID from TableA where ID not IN ( Select ID from Table B)
Have you considered this:
SELECT ID from TableA EXCEPT Select ID from Table B
?
-jwb
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Index Usage using IN
2006-02-01 20:12 Index Usage using IN Ralph Mason <ralph.mason@telogis.com>
2006-02-01 20:22 ` Re: Index Usage using IN Jeffrey W. Baker <jwbaker@acm.org>
@ 2006-02-01 20:28 ` Jeffrey W. Baker <jwbaker@acm.org>
1 sibling, 0 replies; 6+ messages in thread
From: Jeffrey W. Baker @ 2006-02-01 20:28 UTC (permalink / raw)
To: pgsql-performance
On Wed, 2006-02-01 at 12:22 -0800, Jeffrey W. Baker wrote:
> On Thu, 2006-02-02 at 09:12 +1300, Ralph Mason wrote:
> > Hi,
> >
> > I have 2 tables both have an index on ID (both ID columns are an oid).
> >
> > I want to find only only rows in one and not the other.
> >
> > Select ID from TableA where ID not IN ( Select ID from Table B)
>
> Have you considered this:
>
> SELECT ID from TableA EXCEPT Select ID from Table B
Alternately:
SELECT a.ID
FROM TableA AS a
LEFT JOIN TableB AS b
ON a.ID = b.ID
WHERE b.ID IS NULL
-jwb
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Index Usage using IN
2006-02-01 20:12 Index Usage using IN Ralph Mason <ralph.mason@telogis.com>
2006-02-01 20:22 ` Re: Index Usage using IN Jeffrey W. Baker <jwbaker@acm.org>
@ 2006-02-01 20:41 ` Tom Lane <tgl@sss.pgh.pa.us>
1 sibling, 0 replies; 6+ messages in thread
From: Tom Lane @ 2006-02-01 20:41 UTC (permalink / raw)
To: Ralph Mason <ralph.mason@telogis.com>; +Cc: pgsql-performance
"Jeffrey W. Baker" <jwbaker@acm.org> writes:
> On Thu, 2006-02-02 at 09:12 +1300, Ralph Mason wrote:
>> Select ID from TableA where ID not IN ( Select ID from Table B)
> Have you considered this:
> SELECT ID from TableA EXCEPT Select ID from Table B
Also, increasing work_mem might persuade the planner to try a hashed
subplan, which'd be a lot better than what you have. Note that it's
quite unlikely that indexes are going to help for this.
regards, tom lane
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Index Usage using IN
2006-02-01 20:12 Index Usage using IN Ralph Mason <ralph.mason@telogis.com>
@ 2006-02-01 21:23 ` Bruno Wolff III <bruno@wolff.to>
2 siblings, 0 replies; 6+ messages in thread
From: Bruno Wolff III @ 2006-02-01 21:23 UTC (permalink / raw)
To: Ralph Mason <ralph.mason@telogis.com>; +Cc: pgsql-performance
On Thu, Feb 02, 2006 at 09:12:59 +1300,
Ralph Mason <ralph.mason@telogis.com> wrote:
> Hi,
>
> I have 2 tables both have an index on ID (both ID columns are an oid).
>
> I want to find only only rows in one and not the other.
>
> Select ID from TableA where ID not IN ( Select ID from Table B)
>
> This always generates sequential scans.
>
> Table A has about 250,000 rows. Table B has about 250,000 Rows.
>
> We should get a Scan on Table B and a Index Lookup on Table A.
I don't think that is going to work if there are NULLs in table B.
I don't know whether or not Postgres has code to special case NULL testing
(either for constraints ruling them out, or doing probes for them in addition
to the key it is trying to match) for doing NOT IN. Just doing a simple
index probe into table A isn't going to tell you all you need to know if
you don't find a match.
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Index Usage using IN
2006-02-01 20:12 Index Usage using IN Ralph Mason <ralph.mason@telogis.com>
@ 2006-02-01 22:12 ` Hari Warrier <hbhaskaran@gmail.com>
2 siblings, 0 replies; 6+ messages in thread
From: Hari Warrier @ 2006-02-01 22:12 UTC (permalink / raw)
To: Ralph Mason <ralph.mason@telogis.com>; +Cc: pgsql-performance
Select ID from TableA where not exists ( Select ID from Table B where ID
= TableA.ID)
might give you index scan. Of course, that is only useful is TableA is
very small table.
Not appropriate for 250k rows
on 2/1/2006 12:12 PM Ralph Mason said the following:
> Hi,
>
> I have 2 tables both have an index on ID (both ID columns are an oid).
>
> I want to find only only rows in one and not the other.
>
> Select ID from TableA where ID not IN ( Select ID from Table B)
>
> This always generates sequential scans.
>
> Table A has about 250,000 rows. Table B has about 250,000 Rows.
>
> We should get a Scan on Table B and a Index Lookup on Table A.
>
> Is there any way to force this? enable_seqscan off doesn't help at all.
>
> The Plan is
>
> Seq Scan on tablea(cost=100000000.00..23883423070450.96 rows=119414
> width=4)
> Filter: (NOT (subplan))"
> SubPlan -> Seq Scan on tableb (cost=100000000.00..100004611.17
> rows=242617 width=4)
>
>
> Thanks
> Ralph
>
>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 6: explain analyze is your friend
>
^ permalink raw reply [nested|flat] 6+ messages in thread
end of thread, other threads:[~2006-02-01 22:12 UTC | newest]
Thread overview: 6+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2006-02-01 20:12 Index Usage using IN Ralph Mason <ralph.mason@telogis.com>
2006-02-01 20:22 ` Jeffrey W. Baker <jwbaker@acm.org>
2006-02-01 20:28 ` Jeffrey W. Baker <jwbaker@acm.org>
2006-02-01 20:41 ` Tom Lane <tgl@sss.pgh.pa.us>
2006-02-01 21:23 ` Bruno Wolff III <bruno@wolff.to>
2006-02-01 22:12 ` Hari Warrier <hbhaskaran@gmail.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