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
