Boa tarde! Tenho uma tabela com um campo date e tenho um índice para este campo.. Gostaria de entender porque um WHERE nesse campo date usando ">= AND <=" faz a leitura de dados através do índice(Index Scan) e usando "BETWEEN" a leitura é feita de forma sequencial?
Comparativos do EXPLAIN ANALYZE abaixo: SELECT com WHERE ( ( hist.datahorafim::date between '06/20/2012' and '06/26/2012')) "Sort (cost=10000002255.87..10000002256.70 rows=331 width=77) (actual time=42.662..48.170 rows=27319 loops=1)" " Sort Key: endterminal" " Sort Method: quicksort Memory: 4141kB" " -> Seq Scan on thistprod hist (cost=10000000000.00..10000002242.02 rows=331 width=77) (actual time=2.284..32.959 rows=27319 loops=1)" " Filter: (((datahorafim)::date >= '2012-06-20'::date) AND ((datahorafim)::date <= '2012-06-26'::date))" "Total runtime: 52.685 ms" SELECT com WHERE hist.datahorafim >= '06/20/2012' AND hist.datahorafim <= '06/26/2012' "Sort (cost=3507.53..3569.86 rows=24932 width=77) (actual time=18.105..23.105 rows=24865 loops=1)" " Sort Key: endterminal" " Sort Method: quicksort Memory: 3815kB" " -> Bitmap Heap Scan on thistprod hist (cost=393.81..1686.79 rows=24932 width=77) (actual time=2.670..9.120 rows=24865 loops=1)" " Recheck Cond: ((datahorafim >= '2012-06-20 00:00:00'::timestamp without time zone) AND (datahorafim <= '2012-06-26 00:00:00'::timestamp without time zone))" " -> Bitmap Index Scan on idx_autech_thistprod_9 (cost=0.00..387.57 rows=24932 width=0) (actual time=2.622..2.622 rows=24865 loops=1)" " Index Cond: ((datahorafim >= '2012-06-20 00:00:00'::timestamp without time zone) AND (datahorafim <= '2012-06-26 00:00:00'::timestamp without time zone))" "Total runtime: 27.227 ms" Se alguém souber o motivo, agradeço!! Renato Becker Desenvolvimento de Software DBA PostgreSQL @renatobecker http://br.linkedin.com/pub/renato-becker/21/a/988
_______________________________________________ pgbr-geral mailing list [email protected] https://listas.postgresql.org.br/cgi-bin/mailman/listinfo/pgbr-geral
