Does anyone know a DBMS where joining with limits actually works? Because I'm sick of this crap.
FWIW, this is what Postgres gives:
mw=# explain select * from recentchanges left join tag_summary on ts_rc_id=rc_id order by rc_timestamp desc limit 50;
QUERY PLAN ------------------------------------------------------------ Limit (cost=0.00..31.28 rows=50) -> Nested Loop Left Join (cost=0.00..475208.34 rows=2940914) -> Index Scan Backward using rc_timestamp on recentchanges (cost=0.00..45650.46 rows=1020612) -> Index Scan using tag_summary_rc_id on tag_summary (cost=0.00..0.37 rows=4) Index Cond: (tag_summary.ts_rc_id = recentchanges.rc_id)
EXPLAIN ANALYZE with about a million (bogus) rows in each table:
mw=# explain analyze select * from recentchanges left join tag_summary on ts_rc_id=rc_id order by rc_timestamp desc limit 50; QUERY PLAN ------------------------------------------------------------------------- Limit (cost=0.00..31.28 rows=50) (actual time=0.147..0.415 rows=50 loops=1) -> Nested Loop Left Join (cost=0.00..475208.34 rows=2940914) (actual time=0.146..0.372 rows=50 loops=1) -> Index Scan Backward using rc_timestamp on recentchanges (cost=0.00..45650.46 rows=1020612) (actual time=0.110..0.122 rows=4 loops=1) -> Index Scan using tag_summary_rc_id on tag_summary (cost=0.00..0.47 rows=4) (actual time=0.014..0.043 rows=12 loops=4) Index Cond: (tag_summary.ts_rc_id = recentchanges.rc_id) Total runtime: 0.559 ms