Skip site navigation (1) Skip section navigation (2)

Re: Which of the solution is better?

From: "Josh Berkus" <josh(at)agliodbs(dot)com>
To: pgsql-performance(at)postgresql(dot)org
Subject: Re: Which of the solution is better?
Date: 2002-12-11 21:53:38
Message-ID: web-2277358@davinci.ethosmedia.com (view raw or flat)
Thread:
Lists: pgsql-performance
Wei,

> I have two tables A and B where A is a huge table with thousands of
> rows, B is a small table with only a couple of entries.
>
> I want to do something like
>
> SELECT
> A.ID
> A.Name
> FROM
> A JOIN B ON (A.ID = B.ID)

You might consider:
SELECT A.ID
       A.Name
FROM A
WHERE EXISTS (SELECT ID FROM B WHERE B.ID = A.ID)

This lets the parser know that you are not interested in retrieving
entire records from B, just those rows from A which correspond to B.
 Run that, and compare the EXPLAIN ANALYZE plan against one which lets
the that parser have free reign:

SELECT A.ID, A.Name
FROM A, B
WHERE A.ID = B.ID

Chances are, the parser will do a better job on the query than you can
do by making stuff explicit.

Give it a try.

-Josh Berkus

pgsql-performance by date

Next:From: Justin CliftDate: 2002-12-11 22:06:00
Subject: Re: Good/Bad RAID and SCSI controllers?
Previous:From: Josh BerkusDate: 2002-12-11 21:49:20
Subject: Good/Bad RAID and SCSI controllers?

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group