Hi Jeff, Thanks for the help. This is the first post by me, and I did mistake unknowingly. I will take care of it next time. Again thanks a lot for the help. -- Thanks & regards, JENISH VYAS On Thu, Jun 2, 2011 at 10:04 AM, Jeff Davis <pgsql@xxxxxxxxxxx> wrote: > > In the future, please remember to CC the list when replying unless you > have a reason not to. This thread is already off-list by now. > > Also, I just noticed that this plan has a sort, and the slow query in > the previous email did not. That looks like it might have been a mistake > when running the regular EXPLAIN (without ANALYZE), because the slow > plan does not look correct without a sort. Anyway... > > On Thu, 2011-06-02 at 09:23 +0300, Jenish wrote: > > Hi Jeff, > > > > This table is growing rapidly. Now the parent table is taking much > > more time for the same query. below is the complite details. > > > > " -> Bitmap Heap Scan on game_round_actions_old > > game_round_actions (cost=73355.48..7277769.30 rows=2630099 width=65) > > (actual time=78319.248..302586.235 rows=2304337 loops=1)" > > " Recheck Cond: (table_id = 1)" > > " -> Bitmap Index Scan on > > "PK_game_round_actions" (cost=0.00..72697.95 rows=2630099 width=0) > > (actual time=78313.095..78313.095 rows=2304337 loops=1)" > > " Index Cond: (table_id = 1)" > > That is the part of the plan that is taking time. Compare that to the > other plan: > > > 2) Child query > > explain analyse Select * from game_round_actions_old where table_id = > > 1 order by table_id,round_id limit 100 > > "Limit (cost=0.00..335.97 rows=100 width=65) (actual > > time=0.035..0.216 rows=100 loops=1)" > > " -> Index Scan using "PK_game_round_actions" on > > game_round_actions_old (cost=0.00..8836452.71 rows=2630099 width=65) > > (actual time=0.033..0.110 rows=100 loops=1)" > > " Index Cond: (table_id = 1)" > > Notice that it's actually using the same index, but the slow plan is > using a bitmap index scan, and the fast plan is using a normal (ordered) > index scan. > > What's happening is that the top-level query is asking to ORDER BY > table_id, round_id LIMIT 100. Querying the child table can get that > order directly from the index, so it scans the index in order, fetches > only 100 tuples, and then it's done. > > But when querying the parent table, it's getting tuples from two tables, > and so the tuples aren't automatically in the right order to satisfy the > ORDER BY. So, it's collecting all of the matching tuples, which is about > 2.6M, then sorting them, then returning the first 100 -- much slower! > > A smarter approach is to scan both tables in the correct order > individually, and merge the results until you get 100 tuples. That would > make both queries run fast. 9.1 is smart enough to do that, but it's > still in beta right now. > > The only answer right now is to rewrite your slow query to be more like > the fast one. I think if you manually push down the ORDER BY ... LIMIT, > it will do the job. Something like: > > select * from > (select * from game_round_actions_old > where table_id = 1 > order by table_id,round_id limit 100 > UNION ALL > select * from game_round_actions_new > where table_id = 1 > order by table_id,round_id limit 100) > order by table_id,round_id limit 100; > > might work. I haven't actually tested that query though. > > Regards, > Jeff Davis > > -- Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
