Re: Bad plan by Planner (Already resolved?)

Hi,

I'll try to answer in-line.

On 10/17/2011 09:32 PM, Kevin Grittner wrote:
> First off, did you use pg_upgrade from an earlier major release? If
> so, be sure you've dealt with this issue:
Although I joined recently, I doubt whether pg_upgrade was used here.
And this doesn't look like the issue either. There are no data loss
issues and this seems primarily a planner specific bug.

> the description is a
> little vague without table definitions and EXPLAIN ANALYZE output,
> so people might just not be sure.
Makes sense. Just that, I thought I shouldn't drop in a large mail, in
case the issue was a well-known one. Please find below the EXPLAIN
ANALYSE output. I've changed the table-names / field-names and provided
other details as well.

large_table_a: ~20million
n_dead_tuples / reltuples : ~7%
analysed: <2 weeks

large_table_b: ~140million
n_dead_tuples / reltuples : ~0%
analysed: <2 days

default_statistics_target: 1000

field_a: int (indexed)
field_b: int (indexed)

> Since it's arguably in your best
> interest to update at least to 8.4.9 anyway, the easiest way to get
> your answer might be to do so and test it.
Frankly, its slightly difficult to just try out versions. DB>1Tb and
getting that kind of resources to just try out versions for a query is
not that simple. Hope you would understand. I have the workaround
implemented, but just wanted to be sure that this is accommodated in a
newer version.

===============
EXISTING QUERY:
SELECT field_a FROM large_table_a
JOIN large_table_b USING (field_a)
WHERE field_b IN (SELECT large_table_b.field_b
FROM large_table_b WHERE field_a = 2673056)

ANALYSE:
Hash Join (cost=273247.23..6460088.89 rows=142564896 width=4)
Hash Cond: (public.large_table_b.field_b =
public.large_table_b.field_b)
-> Merge Join (cost=273112.62..5925331.24 rows=142564896 width=8)
Merge Cond: (large_table_a.field_a =
public.large_table_b.field_a)
-> Index Scan using "PK_large_table_a" on large_table_a
(cost=0.00..570804.30 rows=22935395 width=4)
-> Index Scan using "IX_large_table_b_field_a" on
large_table_b (cost=0.00..4381499.54 rows=142564896 width=8)
-> Hash (cost=133.32..133.32 rows=103 width=4)
-> HashAggregate (cost=132.29..133.32 rows=103 width=4)
-> Index Scan using "IX_large_table_b_field_a" on
large_table_b (cost=0.00..131.87 rows=165 width=4)
Index Cond: (field_a = 2673056)

=====================

ALTERNATE QUERY:
SELECT s1.field_a FROM large_table_a
JOIN large_table_b s1 USING (field_a)
JOIN large_table_b s2 ON s1.field_b = s2.field_b
WHERE s2.field_a = 2673056

ANALYSE:
Nested Loop (cost=0.00..2368.74 rows=469 width=4) (actual
time=0.090..0.549 rows=6 loops=1)
-> Nested Loop (cost=0.00..1784.06 rows=469 width=4) (actual
time=0.057..0.350 rows=16 loops=1)
-> Index Scan using "IX_large_table_b_field_a" on
large_table_b s2 (cost=0.00..131.87 rows=165 width=4) (actual
time=0.033..0.046 rows=6 loops=1)
Index Cond: (field_a = 2673056)
-> Index Scan using "IX_large_table_b_SampleId" on
large_table_b s1 (cost=0.00..9.99 rows=2 width=8) (actual
time=0.037..0.047 rows=3 loops=6)
Index Cond: (s1.field_b = s2.field_b)
-> Index Scan using "PK_large_table_a" on large_table_a
(cost=0.00..1.23 rows=1 width=4) (actual time=0.011..0.011 rows=0 loops=16)
Index Cond: (large_table_a.field_a = s1.field_a)
Total runtime: 0.620 ms

--
Robins Tharakan