Robins Tharakan <robins.tharakan@xxxxxxxxxx> writes: > ORIGINAL QUERY (on PostgreSQL 8.4.9): > http://explain.depesz.com/s/bTm > EXPLAIN ANALYZE 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) ; > ------------------------------------------------------------------------------------------------------------------------------------------------------------ > Nested Loop (cost=132.97..194243.54 rows=156031 width=4) (actual > time=6.612..43179.524 rows=2120 loops=1) > -> Nested Loop (cost=132.97..1107.63 rows=156031 width=4) (actual > time=6.576..29122.017 rows=6938 loops=1) > -> HashAggregate (cost=132.97..133.96 rows=99 width=4) > (actual time=6.543..12.726 rows=2173 loops=1) > -> Index Scan using "IX_large_table_b_SigId" on > large_table_b (cost=0.00..132.56 rows=164 width=4) (actual > time=0.029..3.425 rows=2173 loops=1) > Index Cond: (field_a = 2673056) > -> Index Scan using "IX_large_table_b_field_b" on > large_table_b (cost=0.00..9.81 rows=2 width=8) (actual > time=6.732..13.384 rows=3 loops=2173) > Index Cond: (public.large_table_b.field_b = > public.large_table_b.field_b) > -> Index Scan using "PK_large_table_a" on large_table_a > (cost=0.00..1.23 rows=1 width=4) (actual time=2.021..2.021 rows=0 > loops=6938) > Index Cond: (large_table_a.field_a = public.large_table_b.field_a) > Total runtime: 43182.975 ms > OPTIMIZED QUERY (on PostgreSQL 8.4.7): > http://explain.depesz.com/s/emO > EXPLAIN ANALYZE 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; > ------------------------------------------------------------------------------------------------------------------------------------------------------------ > Nested Loop (cost=0.00..2356.98 rows=494 width=4) (actual > time=0.086..96.056 rows=2120 loops=1) > -> Nested Loop (cost=0.00..1745.51 rows=494 width=4) (actual > time=0.051..48.900 rows=6938 loops=1) > -> Index Scan using "IX_large_table_b_SigId" on large_table_b > s2 (cost=0.00..132.56 rows=164 width=4) (actual time=0.028..3.411 > rows=2173 loops=1) > Index Cond: (field_a = 2673056) > -> Index Scan using "IX_large_table_b_field_b" on > large_table_b s1 (cost=0.00..9.81 rows=2 width=8) (actual > time=0.007..0.012 rows=3 loops=2173) > 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.004..0.004 rows=0 > loops=6938) > Index Cond: (large_table_a.field_a = s1.field_a) > Total runtime: 98.165 ms I suspect that you're just fooling yourself here, and the "optimized" query is no such thing. Those plans are identical except for the insertion of the HashAggregate step, which in itself adds less than 10msec to the runtime, and we can see it's not eliminating any rows either. So why does the second one run so much faster? I can think of three theories: 1. The tables are horrendously bloated on the first database, so that many more pages have to be touched to get the same number of tuples. This would likely indicate an improper autovacuum configuration. 2. You failed to account for caching effects, ie the first example is being run "cold" and has to actually read everything from disk, whereas the second example has everything it needs already in RAM. In that case the speed differential is quite illusory. 3. The HashAggregate would likely spit out the rows in a completely different order than it received them. If scanning large_table_b in the order of IX_large_table_b_SigId happens to yield field_b values that are very well ordered, it's possible that locality of access in the other indexscans would be enough better in the second plan to account for the speedup. This seems the least likely theory, though. BTW, how come is it that "SELECT large_table_b.field_b FROM large_table_b WHERE field_a = 2673056" produces no duplicate field_b values? Is that just luck? Is there a unique constraint on the table that implies it will happen? regards, tom lane -- Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
