Thread: planner/optimizer question
This executes quickly (as expected): explain select * from flow_stats where src_addr='1.1.1.1' order by log_date desc limit 5; NOTICE: QUERY PLAN: Limit (cost=1241.77..1241.77 rows=5 width=116) -> Sort (cost=1241.77..1241.77 rows=307 width=116) -> Index Scan using flow_stats_ix6 on flow_stats (cost=0.00..1229.07 rows=307 width=116) Bue this executes slowly: explain select * from flow_stats where src_addr='1.1.1.1' order by log_date desc limit 3; NOTICE: QUERY PLAN: Limit (cost=0.00..796.61 rows=3 width=116) -> Index Scan Backward using flow_stats_ix4 on flow_stats (cost=0.00..81594.14 rows=307 width=116) Where flow_stats_ix4 is (log_date) flow_stats_ix6 is (src_addr,log_date) The reason for the slowness is that the given source address does not exist, and it has to scan through the entire index to determine that the requested value does not exist (same is true for rare values). Can the optimizer/planner be told to do an 'Index Scan Backward' on flow_stats_ix6, or even just an 'Index Scan' & Sort? Or are backward scans of secondary index segments not implemented? ---------------------------------------------------------------- Philip Warner | __---_____ Albatross Consulting Pty. Ltd. |----/ - \ (A.B.N. 75 008 659 498) | /(@) ______---_ Tel: (+61) 0500 83 82 81 | _________ \ Fax: (+61) 0500 83 82 82 | ___________ | Http://www.rhyme.com.au | / \| | --________-- PGP key available upon request, | / and from pgp5.ai.mit.edu:11371 |/
Philip Warner wrote: > > This executes quickly (as expected): > > explain select * from flow_stats where src_addr='1.1.1.1' > order by log_date desc limit 5; > NOTICE: QUERY PLAN: > > Limit (cost=1241.77..1241.77 rows=5 width=116) > -> Sort (cost=1241.77..1241.77 rows=307 width=116) > -> Index Scan using flow_stats_ix6 on flow_stats > (cost=0.00..1229.07 rows=307 width=116) > > Bue this executes slowly: > > explain select * from flow_stats where src_addr='1.1.1.1' order by > log_date desc limit 3; > NOTICE: QUERY PLAN: > > Limit (cost=0.00..796.61 rows=3 width=116) > -> Index Scan Backward using flow_stats_ix4 on flow_stats > (cost=0.00..81594.14 rows=307 width=116) > > Where > > flow_stats_ix4 is (log_date) > flow_stats_ix6 is (src_addr,log_date) > > The reason for the slowness is that the given source address does not > exist, and it has to scan through the entire index to determine that the > requested value does not exist (same is true for rare values). > > Can the optimizer/planner be told to do an 'Index Scan Backward' on > flow_stats_ix6, or even just an 'Index Scan' & Sort? Or are backward scans > of secondary index segments not implemented? How about the following ? explain select * from flow_stats where src_addr='1.1.1.1' order by src_addr desc, log_date desc limit 3; regards, Hiroshi Inoue
At 15:40 29/10/01 +0900, Hiroshi Inoue wrote: >Philip Warner wrote: > >How about the following ? > > explain select * from flow_stats where src_addr='1.1.1.1' > order by src_addr desc, log_date desc limit 3; > Yep, that works. Thanks. ---------------------------------------------------------------- Philip Warner | __---_____ Albatross Consulting Pty. Ltd. |----/ - \ (A.B.N. 75 008 659 498) | /(@) ______---_ Tel: (+61) 0500 83 82 81 | _________ \ Fax: (+61) 0500 83 82 82 | ___________ | Http://www.rhyme.com.au | / \| | --________-- PGP key available upon request, | / and from pgp5.ai.mit.edu:11371 |/
> Bue this executes slowly: > > explain select * from flow_stats where src_addr='1.1.1.1' order by > log_date desc limit 3; > NOTICE: QUERY PLAN: > > Limit (cost=0.00..796.61 rows=3 width=116) > -> Index Scan Backward using flow_stats_ix4 on flow_stats > (cost=0.00..81594.14 rows=307 width=116) > > Where > > flow_stats_ix4 is (log_date) > flow_stats_ix6 is (src_addr,log_date) This would be a possible optimization, that other db's also seem to miss (at least in older versions). The trick with all ot them is to include the =constant restricted column in the order by: select * from flow_stats where src_addr='1.1.1.1' order by src_addr desc, log_date desc limit 3; Note, that because src_addr is fixed it won't change the result. Andreas