> 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
--
Greg Sabino Mullane [email protected]
End Point Corporation
PGP Key: 0x14964AC8
signature.asc
Description: OpenPGP digital signature
_______________________________________________ Wikitech-l mailing list [email protected] https://lists.wikimedia.org/mailman/listinfo/wikitech-l
