Re: BRIN indexes

Felipe Santos wrote:

> brin_db=# EXPLAIN ANALYSE SELECT count(*) FROM orders WHERE order_date
> BETWEEN '2012-01-04 09:00:00' and '2014-01-04 14:30:00';
>
> QUERY PLAN
>
> ------------------------------------------------------------------------------------------------------------------------------------------------------
> -----------------
> Aggregate (cost=2408269.34..2408269.35 rows=1 width=0) (actual
> time=14164.923..14164.923 rows=1 loops=1)
> -> Bitmap Heap Scan on orders (cost=326808.28..2328609.76
> rows=31863832 width=0) (actual time=42.742..11430.562 rows=31589101 loops=1)
> Recheck Cond: ((order_date >= '2012-01-04 09:00:00-02'::timestamp
> with time zone) AND (order_date <= '2014-01-04 14:30:00-02'::timestamp with
> time zone))
> Rows Removed by Index Recheck: 21907
> Heap Blocks: lossy=201344
> -> Bitmap Index Scan on idx_order_date_brin
> (cost=0.00..318842.32 rows=31863832 width=0) (actual time=36.151..36.151
> rows=2013440 loops=1)
> Index Cond: ((order_date >= '2012-01-04
> 09:00:00-02'::timestamp with time zone) AND (order_date <= '2014-01-04
> 14:30:00-02'::timestamp
> with time zone))
> Planning time: 0.297 ms
> Execution time: 14164.985 ms
> (9 rows)

The number of blocks read from the heap is a key number to watch for,
because when you get concurrency that's what going to matter the most.
Here you have 201k buffer reads, versus, uh, I don't know how many pages
read (because you didn't use the VERBOSE explain option). I think it's
worth comparing how many buffer accesses your query had to read.

--
Álvaro Herrera http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services