From: Tom Lane <tgl@sss.pgh.pa.us>
To: James Coleman <jtc331@gmail.com>
Cc: pgsql-hackers@lists.postgresql.org, Alexander Korotkov <a.korotkov@postgrespro.ru>
Subject: Re: [PATCH] Incremental sort (was: PoC: Partial sort)
Date: Thu, 06 Sep 2018 13:39:47 -0400
Message-ID: <16617.1536255587@sss.pgh.pa.us> (raw)
In-Reply-To: <153625271389.4078.18405235923135713189.pgcf@coridan.postgresql.org>
References: <CAPpHfds1waRZ=NOmueYq0sx1ZSCnt+5QJvizT8ndT2=etZEeAQ@mail.gmail.com>
<CAPpHfdsjv1+eRvoHqn1NwCEcBAVcOr0c67BFuzg50zZdHTANRg@mail.gmail.com>
<153625271389.4078.18405235923135713189.pgcf@coridan.postgresql.org>
James Coleman <jtc331@gmail.com> writes:
> A fairly common planning problem for us is what we call "most recent first" queries; i.e., "the 50 most recent <table> rows for a <foreign key>".
> Here's a basic setup:
> -- created_at has very high cardinality
> create table foo(pk serial primary key, owner_fk integer, created_at timestamp);
> create index idx_foo_on_owner_and_created_at on foo(owner_fk, created_at);
> -- technically this data guarantees unique created_at values,
> -- but there's no reason it couldn't be modified to have a few
> -- random non-unique values to prove the point
> insert into foo(owner_fk, created_at)
> select i % 100, now() - (i::text || ' minutes')::interval
> from generate_series(1, 1000000) t(i);
> And here's the naive query to get the results we want:
> select *
> from foo
> where owner_fk = 23
> -- pk is only here to disambiguate/guarantee a stable sort
> -- in the rare case that there are collisions in the other
> -- sort field(s)
> order by created_at desc, pk desc
> limit 50;
If you're concerned about the performance of this case, why don't you make
an index that actually matches the query?
regression=# create index on foo (owner_fk, created_at, pk);
CREATE INDEX
regression=# explain analyze select * from foo where owner_fk = 23 order by created_at desc, pk desc limit 50;
QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------------------------------------------
Limit (cost=0.42..110.92 rows=50 width=16) (actual time=0.151..0.280 rows=50 loops=1)
-> Index Only Scan Backward using foo_owner_fk_created_at_pk_idx on foo (cost=0.42..20110.94 rows=9100 width=16) (actual time=0.146..0.255 rows=50 loops=1)
Index Cond: (owner_fk = 23)
Heap Fetches: 50
Planning Time: 0.290 ms
Execution Time: 0.361 ms
(6 rows)
There may be use-cases for Alexander's patch, but I don't find this
one to be terribly convincing.
regards, tom lane
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-hackers@postgresql.org
Cc: tgl@sss.pgh.pa.us, jtc331@gmail.com, a.korotkov@postgrespro.ru
Subject: Re: [PATCH] Incremental sort (was: PoC: Partial sort)
In-Reply-To: <16617.1536255587@sss.pgh.pa.us>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox