questions on interpreting the output of explain
От | Michael Olivier |
---|---|
Тема | questions on interpreting the output of explain |
Дата | |
Msg-id | 19990220050748.28250.rocketmail@send103.yahoomail.com обсуждение исходный текст |
Ответы |
Re: [SQL] questions on interpreting the output of explain
|
Список | pgsql-sql |
I'm trying to optimize a query and don't yet understand PostGreSQL's query optimization... I'm not sure how to read it in terms of best outcome. On a query that looks like: explain select U.oid from users U, selections S where U.acctname = S.acctname and U.birthdate <= '1-1-1963' and U.birthdate >= '1-1-1933' and 38 >= S.field1_int and 38 <= S.field2_int and U.tol_a < 99.0 and U.tol_b < 99.0 (indexes on: U.birthdate, U.acctname, S.acctname, U.tol_a, U.tol_b) I get: Nested Loop (cost=9.38 size=1 width=28) -> Index Scan on u (cost=3.26 size=3 width=16) -> Index Scan on s (cost=2.04 size=25 width=12) ...but if I shorten the query slightly: explain select U.oid from users U, selections S where U.acctname = S.acctname and U.birthdate <= '1-1-1963' and U.birthdate >= '1-1-1933' and 38 >= S.field1_int and 38 <= S.field2_int ...I get: Hash Join (cost=14.67 size=3 width=28) -> Index Scan on u (cost=3.26 size=26 width=16) -> Hash (cost=0.00 size=0 width=0) -> Index Scan on s (cost=7.73 size=25 width=12) Can someone explain the details here to me? I guess specific questions are: - Does hash join mean my acctname indexes are hashed, not btree'd? - What does index scan mean? - Nested loop? - Does the cost roll-up, meaning the top line is total cost, and the rest is a breakdown, or do I add up all the cost numbers? - Can I compare costs between two queries? thanks! Michael _________________________________________________________ DO YOU YAHOO!? Get your free @yahoo.com address at http://mail.yahoo.com
В списке pgsql-sql по дате отправления: