Re: Slow query needs a kick in the pants.

"Dann Corbit" <DCorbit(at)connx(dot)com> writes:

> I have two tables in all cases. Each table pair consists of the
> following columns:
> 1. A primary key of one or more columns {with a unique index}
> 2. An Oid column {with a unique index}
> 3. A 64 bit CRC
>
> For both tables, the primary key information will "mostly" match. I
> need to know which primary keys are found in the first table but not in
> the second. Also, which primary keys are found in the second table but
> not in the first.

The approach you wrote is how I would write it too. Though from the problem
description it sounds like you want a FULL OUTER JOIN.

You could try timing it with enable_seqscan = off to see if an index scan is
any faster. It will also show you if postgres thinks you're joining precisely
on the primary key. If it's faster you could try lowering random_page cost to
coerce postgres into doing index scans instead of sequential scans + sorts. In
theory sorting the tables should be faster than the index scan but I'm always
skeptical about that.

If it's something you do often but the data rarely changes you could also try
clustering both tables on the primary key index. This won't fundamentally
change things but postgres will notice it (after an analyze) and be more
likely to use the index, and it will make the index scan somewhat faster.

I wonder how you ended up with a database structure like this, it's arguably
denormalized.

--
greg