Tom Lane wrote:
Looking back at Alban's original post, I finally see what the planner is up to:-> Merge Join (cost=0.00..165.07 rows=1 width=28) (actual time=53.890..129.310 rows=1 loops=1) Merge Cond: ("outer".number = "inner".number) -> Nested Loop (cost=0.00..2796.82 rows=30 width=28) (actual time=44.088..117.487 rows=2 loops=1) -> Nested Loop (cost=0.00..2682.38 rows=30 width=24) (actual time=44.034..117.375 rows=2 loops=1) -> Index Scan using mm_insrel_full_idx on mm_insrel_table (cost=0.00..2512.97 rows=30 width=20) (actual time=43.975..117.246 rows=2 loops=1) Index Cond: (dnumber = 558332) The reason it's choosing this indexscan is that that will give it data sorted by mm_insrel_table.number, which it can feed into the mergejoin without an extra sort step. Now sorting 30 rows is not going to take nearly as much time as the indexscan eats up, so this still doesn't make sense --- until you notice that it's estimating the top merge join at considerably less than the cost of its inputs (165.07, vss 2796.82 just for this input). That means it thinks it won't have to run the inputs to completion in order to finish the mergejoin, and so it's picking a sub-plan that has zero start cost.
I'm thinking that removing the indexes it's erroneously using now could help performance, as it can no longer use that index. It may however pick the primary key index (likely), or - if we remove even that one - a sequential scan... Experimenting will answer that.
Thanks for your answers so far, at least now we know what's going on. Regards, -- Alban Hertroys [EMAIL PROTECTED] magproductions b.v. T: ++31(0)534346874 F: ++31(0)534346876 M: I: www.magproductions.nl A: Postbus 416 7500 AK Enschede // Integrate Your World // ---------------------------(end of broadcast)--------------------------- TIP 5: don't forget to increase your free space map settings
