Thread: Trouble to understand plain of postgresql
I'm not be able to understand this plain:
Look this:
explain analyze
select ven.filcli, ven.codcli, rec.datven
from tt_ven ven, tt_rec rec
where ven.codfil = rec.codfil
and ven.sequen = rec.sequen
and rec.datven between to_timestamp('01/03/2003 00:00:00', 'dd/mm/yyyy hh24:mi:ss')
and to_timestamp('03/03/2003 23:59:59', 'dd/mm/yyyy hh24:mi:ss')
and rec.flgest = 'A'
select ven.filcli, ven.codcli, rec.datven
from tt_ven ven, tt_rec rec
where ven.codfil = rec.codfil
and ven.sequen = rec.sequen
and rec.datven between to_timestamp('01/03/2003 00:00:00', 'dd/mm/yyyy hh24:mi:ss')
and to_timestamp('03/03/2003 23:59:59', 'dd/mm/yyyy hh24:mi:ss')
and rec.flgest = 'A'
Nested Loop (cost=0.00..248.89 rows=1 width=67) (actual time=604.00..604.00 rows=0 loops=1)
-> Seq Scan on tt_rec rec (cost=0.00..242.86 rows=1 width=29) ...
Filter: (((datven)::timestamp with time zone >= to_timestamp('01/03/2003 00:00:00' ...
-> Index Scan using pk_ven on tt_ven ven (cost=0.00..6.01 rows=1 width=38) (never executed)
Index Cond: ((ven.codfil = "outer".codfil) AND (ven.sequen = "outer".sequen))
Total runtime: 606.00 msec
-> Seq Scan on tt_rec rec (cost=0.00..242.86 rows=1 width=29) ...
Filter: (((datven)::timestamp with time zone >= to_timestamp('01/03/2003 00:00:00' ...
-> Index Scan using pk_ven on tt_ven ven (cost=0.00..6.01 rows=1 width=38) (never executed)
Index Cond: ((ven.codfil = "outer".codfil) AND (ven.sequen = "outer".sequen))
Total runtime: 606.00 msec
But if I change the function to_timestamp to to_date ...
explain analyze
select ven.filcli, ven.codcli, rec.datven
from tt_ven ven, tt_rec rec
where ven.codfil = rec.codfil
and ven.sequen = rec.sequen
and rec.datven between to_date('01/03/2003 00:00:00', 'dd/mm/yyyy hh24:mi:ss')
and to_date('03/03/2003 23:59:59', 'dd/mm/yyyy hh24:mi:ss')
and rec.flgest = 'A'
select ven.filcli, ven.codcli, rec.datven
from tt_ven ven, tt_rec rec
where ven.codfil = rec.codfil
and ven.sequen = rec.sequen
and rec.datven between to_date('01/03/2003 00:00:00', 'dd/mm/yyyy hh24:mi:ss')
and to_date('03/03/2003 23:59:59', 'dd/mm/yyyy hh24:mi:ss')
and rec.flgest = 'A'
Nested Loop (cost=0.00..12.03 rows=1 width=67) (actual time=114.00..114.00 rows=0 loops=1)
-> Index Scan using i_lc_rec_datven_flgest on tt_rec rec ...
Index Cond: ((datven >= '2003-03-01 00:00:00'::timestamp ...
-> Index Scan using pk_ven on tt_ven ven (cost=0.00..6.01 rows=1 width=38) (never executed)
Index Cond: ((ven.codfil = "outer".codfil) AND (ven.sequen = "outer".sequen))
Total runtime: 115.00 msec
-> Index Scan using i_lc_rec_datven_flgest on tt_rec rec ...
Index Cond: ((datven >= '2003-03-01 00:00:00'::timestamp ...
-> Index Scan using pk_ven on tt_ven ven (cost=0.00..6.01 rows=1 width=38) (never executed)
Index Cond: ((ven.codfil = "outer".codfil) AND (ven.sequen = "outer".sequen))
Total runtime: 115.00 msec
Here it use INDEX SCAN i_lc_rec_datven_flgest
Why this happen?
Cristian
On 9 Jun 2003 at 18:57, Cristian Custodio wrote:
> explain analyze
> select ven.filcli, ven.codcli, rec.datven
> from tt_ven ven, tt_rec rec
> where ven.codfil = rec.codfil
> and ven.sequen = rec.sequen
> and rec.datven between to_date('01/03/2003 00:00:00', 'dd/mm/yyyy
> hh24:mi:ss')
> and to_date('03/03/2003 23:59:59', 'dd/mm/yyyy
> hh24:mi:ss')
> and rec.flgest = 'A'
>
>
> Nested Loop (cost=0.00..12.03 rows=1 width=67) (actual time=114.00..114.00
> rows=0 loops=1)
> -> Index Scan using i_lc_rec_datven_flgest on tt_rec rec ...
> Index Cond: ((datven >= '2003-03-01 00:00:00'::timestamp ...
> -> Index Scan using pk_ven on tt_ven ven (cost=0.00..6.01 rows=1 width=38)
> (never executed)
> Index Cond: ((ven.codfil = "outer".codfil) AND (ven.sequen =
> "outer".sequen))
> Total runtime: 115.00 msec
> Here it use INDEX SCAN i_lc_rec_datven_flgest
Just a guess, is rec.datven a date field and not a timestamp?
Bye
Shridhar
--
I've already got a female to worry about. Her name is the Enterprise. --
Kirk, "The Corbomite Maneuver", stardate 1514.0