I want to optimize this simple join:
SELECT * FROM huge_table h, tiny_table t WHERE UPPER( h.id ) = UPPER( t.id )
huge_table has about 2.5 million records, can be assumed as fixed, and
has the following index:
CREATE INDEX huge_table_index ON huge_table( UPPER( id ) );
...while tiny_table
[ I had a problem with my mailer when I first sent this. My apologies
for any repeats. ]
I want to optimize this simple join:
SELECT * FROM huge_table h, tiny_table t WHERE UPPER( h.id ) = UPPER( t.id )
huge_table has about 2.5 million records, can be assumed as fixed, and
has the
On 5/24/06, Tom Lane [EMAIL PROTECTED] wrote:
[EMAIL PROTECTED] writes:Limit(cost=19676.75..21327.99
rows=6000 width=84)-Hash Join(cost=19676.75..1062244.81 rows=3788315 width=84)Hash Cond: (upper((outer.id)::text) = upper((inner.id)::text))-Seq Scan on huge_table h(cost=
0.00..51292.43
SELECT * FROM huge_table h, tiny_table t WHERE UPPER( h.id ) =
UPPER( t.id )
What about :
SELECT * FROM huge_table h WHERE UPPER(id) IN (SELECT upper(id) FROM
tiny_table t)
Or, try opening a cursor on your original query and using FETCH. It might
result in a different plan.
On 5/24/06, Tom Lane [EMAIL PROTECTED] wrote:
[EMAIL PROTECTED] writes:
Limit (cost=19676.75..21327.99 rows=6000 width=84)
- Hash Join (cost=19676.75..1062244.81 rows=3788315 width=84)
Hash Cond: (upper((outer.id)::text) upper((inner.id)::text))
- Seq Scan on
On Thu, May 25, 2006 at 12:31:04PM -0400, [EMAIL PROTECTED] wrote:
Well, they're not my statistics; they're explain's. You mean there's
Explain doesn't get them from nowhere. How often is the table being
ANALYSEd?
More bewildering still (and infuriating as hell--because it means that
all of
On 5/25/06, [EMAIL PROTECTED] [EMAIL PROTECTED] wrote:
Well, they're not my statistics; they're explain's. You mean there's
a bug in explain? I agree that it makes no sense that the costs don't
differ as much as one would expect, but you can see right there the
numbers of rows for the two
On May 25, 2006, at 12:07 PM, Dawid Kuroczko wrote:
On 5/25/06, [EMAIL PROTECTED] [EMAIL PROTECTED] wrote:
Well, they're not my statistics; they're explain's. You mean there's
a bug in explain? I agree that it makes no sense that the costs
don't
differ as much as one would expect, but you
I want to optimize this simple join:
SELECT * FROM huge_table h, tiny_table t WHERE UPPER( h.id ) = UPPER( t.id )
huge_table has about 2.5 million records, can be assumed as fixed, and
has the following index:
CREATE INDEX huge_table_index ON huge_table( UPPER( id ) );
...while tiny_table
kj
PS: FWIW, the query plan for the query with LIMIT 6000 is this:
What is the explain analyze?
QUERY PLAN
-
Limit (cost=19676.75..21327.99 rows=6000 width=84)
-
[EMAIL PROTECTED] writes:
Limit (cost=19676.75..21327.99 rows=6000 width=84)
- Hash Join (cost=19676.75..1062244.81 rows=3788315 width=84)
Hash Cond: (upper((outer.id)::text) = upper((inner.id)::text))
- Seq Scan on huge_table h (cost=0.00..51292.43 rows=2525543
11 matches
Mail list logo
