pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedPerformance of lateral join
4+ messages / 2 participants
[nested] [flat]
* Performance of lateral join
@ 2021-07-26 13:56 Simen Andreas Andreassen Lønsethagen <simen.lonsethagen@fremtind.no>
0 siblings, 1 reply; 4+ messages in thread
From: Simen Andreas Andreassen Lønsethagen @ 2021-07-26 13:56 UTC (permalink / raw)
To: pgsql-performance@lists.postgresql.org <pgsql-performance@lists.postgresql.org>
Hi, first time posting, hope I have included the relevant information.
I am trying to understand the performance of a query which is intended to retrieve a subset of the following table:
Table "contracts.bis_person_alle_endringer"
Column | Type | Collation | Nullable | Default
----------------------------------+--------------------------+-----------+----------+---------
person_id | uuid | | not null |
dpd_gyldig_fra_dato | date | | not null |
dpd_i_kraft_fra_dato | date | | not null |
dpd_i_kraft_til_dato | date | | not null |
dpd_endret_tidspunkt | timestamp with time zone | | not null |
dpd_bis_foedselsnummer | text | | |
dpd_bis_treffkilde_id | text | | |
... [omitted for brevity] ...
dpd_endret_av | text | | |
dpd_bis_kjoenn_id | text | | |
Indexes:
"bis_person_alle_endringer_by_person_id" btree (person_id)
"bis_person_alle_endringer_unique_descending" UNIQUE, btree (dpd_bis_foedselsnummer, dpd_gyldig_fra_dato DESC, dpd_endret_tidspunkt DESC)
dpd=> SELECT relname, relpages, reltuples, relallvisible, relkind, relnatts, relhassubclass, reloptions, pg_table_size(oid) FROM pg_class WHERE relname='bis_person_alle_endringer';
relname | relpages | reltuples | relallvisible | relkind | relnatts | relhassubclass | reloptions | pg_table_size
---------------------------+----------+-------------+---------------+---------+----------+----------------+------------+---------------
bis_person_alle_endringer | 9367584 | 1.09584e+08 | 6392129 | r | 106 | f | | 76760489984
(1 row)
I have ommitted most of the columns, as there are 106 columns in total. The ommitted columns have data types text, numeric or date, all are nullable.
To create the subsets, I (or rather my application) will receive lists of records which should be matched according to some business logic. Each of these lists will be read into a temporary table:
Table "pg_temp_9.records_to_filter_on"
Column | Type | Collation | Nullable | Default
---------------------+------+-----------+----------+---------
foedselsnummer | text | | |
tariff_dato | date | | |
versjons_dato | date | | |
kjent_i_system_dato | date | | |
The subset is then created by the following query, which finds the records in contracts.bis_person_alle_endringer which satisfies the business logic (if any).
select * from records_to_filter_on r
left join lateral (
select * from contracts.bis_person_alle_endringer b
where b.dpd_bis_foedselsnummer = r.foedselsnummer AND
r.kjent_i_system_dato >= b.dpd_endret_tidspunkt AND
r.tariff_dato > b.dpd_gyldig_fra_dato
order by b.dpd_gyldig_fra_dato desc, b.dpd_endret_tidspunkt desc
limit 1
) c on true
where person_id is not null and
r.versjons_dato < c.dpd_i_kraft_til_dato
The temporary table records_to_filter_on and the result of the above query will typically contain 1-5 million rows (the returned subsets are used for training machine learning models).
I've created a sample data set with 3.75 million rows and run EXPLAIN (ANALYZE, BUFFERS) on the query, https://explain.dalibo.com/plan/U41 (and also attached). Running the full EXPLAIN (ANALYZE, BUFFERS) takes about 30 minutes, which seems quite slow. However, as I am new to postgres, I find it difficult to interpret the output of the EXPLAIN (ANALYZE, BUFFERS) - most of the time is spent during an index scan, which to my understanding is "good". However, I don't think I understand postgres well enough to judge whether this is the best I can achieve (or at last close enough) or if the query should be rewritten. Alternatively, is it not realistic to expect faster performance given the size of the table and the hardware of the database instance?
I am running PostgreSQL 11.9 on x86_64-pc-linux-gnu using AWS Aurora on a db.t3.large instance (https://aws.amazon.com/rds/instance-types/). The output of
SELECT name, current_setting(name), source
FROM pg_settings
WHERE source NOT IN ('default', 'override');
is attached in pg_settings.conf.
I realize that these questions are a little vague, but any guidance would be much appreciated.
Thanks, Simen Lønsethagen
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Performance of lateral join
@ 2021-07-26 18:17 Justin Pryzby <pryzby@telsasoft.com>
parent: Simen Andreas Andreassen Lønsethagen <simen.lonsethagen@fremtind.no>
0 siblings, 1 reply; 4+ messages in thread
From: Justin Pryzby @ 2021-07-26 18:17 UTC (permalink / raw)
To: Simen Andreas Andreassen Lønsethagen <simen.lonsethagen@fremtind.no>; +Cc: pgsql-performance@lists.postgresql.org
On Mon, Jul 26, 2021 at 01:56:54PM +0000, Simen Andreas Andreassen Lønsethagen wrote:
> To create the subsets, I (or rather my application) will receive lists of records which should be matched according to some business logic. Each of these lists will be read into a temporary table:
Easy first question: is the temp table analyzed before being used in a join ?
(This is unrelated to "explain analyze").
> I am running PostgreSQL 11.9 on x86_64-pc-linux-gnu using AWS Aurora on a db.t3.large instance (https://aws.amazon.com/rds/instance-types/). The output of
> FROM pg_settings
> is attached in pg_settings.conf.
I think the attachment is missing.
--
Justin
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Performance of lateral join
@ 2021-07-27 09:08 Simen Andreas Andreassen Lønsethagen <simen.lonsethagen@fremtind.no>
parent: Justin Pryzby <pryzby@telsasoft.com>
0 siblings, 1 reply; 4+ messages in thread
From: Simen Andreas Andreassen Lønsethagen @ 2021-07-27 09:08 UTC (permalink / raw)
To: Justin Pryzby <pryzby@telsasoft.com>; +Cc: pgsql-performance@lists.postgresql.org <pgsql-performance@lists.postgresql.org>
> Easy first question: is the temp table analyzed before being used in a join ?
No, I haven't done that. Today, I tried to run
ANALYZE records_to_filter_on;
on the same sample data set (3.75 million rows) before the join, and it did not seem to make much of a difference in terms of time (new output of EXPLAIN ANALYZE at https://explain.dalibo.com/plan/YZu - it seems very similar to me).
Not sure if it is relevant, but I did some experimentation with smaller samples, and for those, there was a significant speedup. Could there be some size threshold on the temp table after which running ANALYZE does not yield any speedup?
> I think the attachment is missing.
Added now.
Simen
Attachments:
[application/json] explain_analyze.json (5.2K, ../../58EEA217-E918-49C0-987A-E634AD6BB1B2@fremtind.no/2-explain_analyze.json)
download
[application/octet-stream] pg_settings.conf (53.9K, ../../58EEA217-E918-49C0-987A-E634AD6BB1B2@fremtind.no/3-pg_settings.conf)
download
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Performance of lateral join
@ 2021-07-28 02:27 Justin Pryzby <pryzby@telsasoft.com>
parent: Simen Andreas Andreassen Lønsethagen <simen.lonsethagen@fremtind.no>
0 siblings, 0 replies; 4+ messages in thread
From: Justin Pryzby @ 2021-07-28 02:27 UTC (permalink / raw)
To: Simen Andreas Andreassen Lønsethagen <simen.lonsethagen@fremtind.no>; +Cc: pgsql-performance@lists.postgresql.org
On Tue, Jul 27, 2021 at 09:08:49AM +0000, Simen Andreas Andreassen Lønsethagen wrote:
> > Easy first question: is the temp table analyzed before being used in a join ?
>
> No, I haven't done that. Today, I tried to run
>
> ANALYZE records_to_filter_on;
>
> on the same sample data set (3.75 million rows) before the join, and it did not seem to make much of a difference in terms of time (new output of EXPLAIN ANALYZE at https://explain.dalibo.com/plan/YZu - it seems very similar to me).
If the "shape" of the plan didn't change, then ANALYZE had no effect.
I think you'd see an improvement if both tables were ordered by foedselsnummer.
It might be that that's already somewhat/partially true (?)
I suggest to create an index on the temp table's r.foedselsnummer, CLUSTER on
that index, and then ANALYZE the table. The index won't be useful for this
query, it's just for clustering (unless you can instead populate the temp table
in order).
Check if there's already high correlation of dpd_bis_foedselsnummer (over 0.9):
| SELECT tablename, attname, inherited, null_frac, n_distinct, correlation FROM pg_stats WHERE attname='dpd_bis_foedselsnummer' AND tablename='...';
If not, consider clustering on the existing "unique_descending" index and then
analyzing that table, too.
This would also affect performance of other queries - hopefully improving
several things at once.
--
Justin
^ permalink raw reply [nested|flat] 4+ messages in thread
end of thread, other threads:[~2021-07-28 02:27 UTC | newest]
Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2021-07-26 13:56 Performance of lateral join Simen Andreas Andreassen Lønsethagen <simen.lonsethagen@fremtind.no>
2021-07-26 18:17 ` Justin Pryzby <pryzby@telsasoft.com>
2021-07-27 09:08 ` Simen Andreas Andreassen Lønsethagen <simen.lonsethagen@fremtind.no>
2021-07-28 02:27 ` Justin Pryzby <pryzby@telsasoft.com>
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