From: | "Madusudanan(dot)B(dot)N" <b(dot)n(dot)madusudanan(at)gmail(dot)com> |
---|---|
To: | Ivan Voras <ivoras(at)gmail(dot)com> |
Cc: | postgres performance list <pgsql-performance(at)postgresql(dot)org> |
Subject: | Re: Understanding BRIN index performance |
Date: | 2016-10-03 09:26:37 |
Message-ID: | CA+_K-Dzrkzp_mqGytSaXFsfwWFvMT+K7orCS_YEbtXCOy6tMWQ@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-performance |
I don't think a BRIN index would help in either case.
BRIN just marks each page with a max and min boundaries which are helpful
in where clauses and has nothing to do with ordering.
For the first operation i.e Max a btree index would do an index scan
backward which is just an index lookup in reverse and for order by it can
use the index as well since a btree index is ordered by default.
That is the reason why it switches to a sequential scan since there is no
way for a BRIN index to be used in the case of a max / order by.
On Mon, Oct 3, 2016 at 2:30 PM, Ivan Voras <ivoras(at)gmail(dot)com> 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
>
>
> Any ideas?
>
>
--
Regards,
Madusudanan.B.N <http://madusudanan.com>
From | Date | Subject | |
---|---|---|---|
Next Message | Simon Riggs | 2016-10-03 09:40:37 | Re: Understanding BRIN index performance |
Previous Message | Ivan Voras | 2016-10-03 09:00:52 | Understanding BRIN index performance |