Re: Understanding BRIN index performance

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



On 3 October 2016 at 10:00, Ivan Voras <ivoras@xxxxxxxxx> wrote:
> Hi,
>
> I have a table of around 20 G, more than 220 million records, and I'm
> running this query on it:
>
> explain analyze SELECT MAX(id) - (SELECT id FROM expl_transactions WHERE
> dateAdded < (now() - INTERVAL '10 MINUTES') ORDER BY dateAdded DESC LIMIT 1)
> FROM expl_transactions;
>
> "id" is SERIAL, "dateAdded" is timestamp without timezone
>
> The "dateAdded" field also has a "default now()" applied to it some time
> after its creation, and a fair amount of null values in the records (which I
> don't think matters for this query, but maybe I'm wrong).
>
> My first idea is to create a default BRIN index on dateAdded since the above
> query is not run frequently. To my surprise, the planner refused to use the
> index and used sequential scan instead. When I forced sequential scanning
> off, I got this:
>
> https://explain.depesz.com/s/W8oo
>
> The query was executing for 40+ seconds. It seems like the "index scan" on
> it returns nearly 9% of the table, 25 mil rows. Since the data in dateAdded
> actually is sequential and fairly selective (having now() as the default
> over a long period of time), this surprises me.
>
> With a normal btree index, of course, it runs fine:
>
> https://explain.depesz.com/s/TB5

Btree retains ordering, BRIN does not.

We've discussed optimizing the sort based upon BRIN metadata, but
that's not implemented yet.

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


-- 
Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance



[Postgresql General]     [Postgresql PHP]     [PHP Users]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Yosemite]

  Powered by Linux