unpredictable cost in explain
От | Ruslan A Dautkhanov |
---|---|
Тема | unpredictable cost in explain |
Дата | |
Msg-id | 41D292B5.5070403@scn.ru обсуждение исходный текст |
Список | pgsql-bugs |
Hello ! Please help me, I tried to make request run faster 1st request (original): SELECT dy.*, sat.dton as satdton FROM dyBilling dy, saType sat WHERE dy.dt>='20-12-2004' AND dy.dt<='20-12-2004' AND dy.servid=sat.servid AND dy.dt BETWEEN sat.dton AND sat.dtoff it have execution time 4174,514 ms and following query plan: Nested Loop (cost=0.00..11.57 rows=1 width=41) -> Index Scan using dybilling_dt_servid_sid on dybilling dy (cost=0.00..5.69 rows=1 width=37) Index Cond: ((dt >= '20-12-2004'::date) AND (dt <= '20-12-2004'::date)) -> Index Scan using satype_dton_dtoff_servid on satype sat (cost=0.00..5.86 rows=1 width=12) Index Cond: (("outer".dt >= sat.dton) AND ("outer".dt <= sat.dtoff) AND ("outer".servid = sat.servid)) 2nd request: SELECT dy.*, sat.dton as satdton FROM dyBilling dy, saType sat WHERE dy.dt='20-12-2004' AND -- <- diff only here dy.servid=sat.servid AND dy.dt BETWEEN sat.dton AND sat.dtoff it have execution time 106,340 ms and following query plan: Merge Join (cost=3973.95..4037.21 rows=195 width=41) Merge Cond: ("outer".servid = "inner".servid) Join Filter: (("outer".dt >= "inner".dton) AND ("outer".dt <= "inner".dtoff)) -> Sort (cost=3451.52..3453.99 rows=989 width=37) Sort Key: dy.servid -> Index Scan using dybilling_dt_servid_sid on dybilling dy (cost=0.00..3402.32 rows=989 width=37) Index Cond: (dt = '20-12-2004'::date) -> Sort (cost=522.43..538.58 rows=6460 width=12) Sort Key: sat.servid -> Seq Scan on satype sat (cost=0.00..113.60 rows=6460 width=12) So, Estimated Cost Real Execution Time, ms 1st request 0.00..11.57 4174,514 2ns request 3973.95..4037.21 106,340 Why? Is this EXPLAIN's behaviour correct ? Results reproducable any number of times, no any caching effects etc. Some maybe useful info: isbs=# select count(*) from dyBilling; --------- 1144078 isbs=# select count(*) from saType; ------- 6472 isbs=# select version(); --------------------------------------------------------------------- PostgreSQL 7.4.1 on i386-unknown-freebsd4.7, compiled by GCC 2.95.4 Thanks. -- best regards, Ruslan A Dautkhanov rusland@scn.ru
В списке pgsql-bugs по дате отправления: