Sort is generating rows
Hi, I have a query with a strange query plan. This query is roughly searching for sales, and convert them with a currency rate. As currency rate changes from time to time, table contains the currency, the company, the rate, the start date of availability of this rate and the end date of availability. The join is done using : left join currency_rate cr on (cr.currency_id = pp.currency_id and cr.company_id = s.company_id and cr.date_start <= coalesce(s.date_order, now()) and (cr.date_end is null or cr.date_end > coalesce(s.date_order, now( The tricky part is the date range on the currency rate, which is not an equality. the query plan shows: -> Sort (cost=120.13..124.22 rows=1637 width=56) (actual time=14.300..72084.758 rows=308054684 loops=1) Sort Key: cr.currency_id, cr.company_id Sort Method: quicksort Memory: 172kB -> CTE Scan on currency_rate cr (cost=0.00..32.74 rows=1637 width=56) (actual time=1.403..13.610 rows=1576 loops=1) There's 2 challenging things : - planner estimates 1637 rows, and get 300 million lines - sorting is generating lines later in the query plan, you find the join: -> Merge Left Join (cost=341056.75..351344.76 rows=1165112 width=224) (actual time=9792.635..269120.409 rows=1170055 loops=1) Merge Cond: ((pp.currency_id = cr.currency_id) AND (s.company_id = cr.company_id)) Join Filter: ((cr.date_start <= COALESCE((s.date_order)::timestamp with time zone, now())) AND ((cr.date_end IS NULL) OR (cr.date_end > COALESCE((s.date_order)::timestamp with time zone, now() Rows Removed by Join Filter: 307266434 It seems the join deletes all the generated million lines, which is correct. My question is then , is there a better way to join a table to another using a date range, knowing that there's no overlap between date ranges? Should we generate a virtual table with rates for all dates, and joining using an equality? For now, the more currency rates, the slowest the query. There's not that much currency rates (1k in this case), as you can only have one rate per day per currency. Have a nice day, Nicolas.
Re: Sort is generating rows
-> Hash Join (cost=656.36..2796.71 rows=36539 width=76) (actual time=13.236..49.047 rows=32732 loops=1) Hash Cond: (l.order_id = s.id) -> Seq Scan on sale_order_line l (cost=0.00..1673.39 rows=36539 width=17) (actual time=0.019..7.338 rows=32732 loops=1) -> Hash (cost=550.72..550.72 rows=8451 width=67) (actual time=13.184..13.184 rows=8382 loops=1) Buckets: 16384 Batches: 1 Memory Usage: 947kB -> Hash Join (cost=37.69..550.72 rows=8451 width=67) (actual time=0.164..10.135 rows=8382 loops=1) Hash Cond: (s.pricelist_id = pp.id) -> Hash Join (cost=13.97..420.42 rows=8451 width=63) (actual time=0.151..7.064 rows=8382 loops=1) Hash Cond: (s.partner_id = partner.id) -> Seq Scan on sale_order s (cost=0.00..301.51 rows=8451 width=55) (actual time=0.005..1.807 rows=8382 loops=1) -> Hash (cost=13.43..13.43 rows=43 width=12) (actual time=0.136..0.136 rows=43 loops=1) Buckets: 1024 Batches: 1 Memory Usage: 10kB -> Seq Scan on res_partner partner (cost=0.00..13.43 rows=43 width=12) (actual time=0.013..0.112 rows=43 loops=1) -> Hash (cost=16.10..16.10 rows=610 width=8) (actual time=0.007..0.007 rows=1 loops=1) Buckets: 1024 Batches: 1 Memory Usage: 9kB -> Seq Scan on product_pricelist pp (cost=0.00..16.10 rows=610 width=8) (actual time=0.005..0.005 rows=1 loops=1) -> Hash (cost=32.95..32.95 rows=490 width=16) (actual time=0.076..0.076 rows=43 loops=1) Buckets: 1024 Batches: 1 Memory Usage: 11kB -> Hash Left Join (cost=11.88..32.95 rows=490 width=16) (actual time=0.051..0.068 rows=43 loops=1) Hash Cond: (p.product_tmpl_id = t.id) -> Seq Scan on product_product p (cost=0.00..14.90 rows=490 width=8) (actual time=0.007..0.010 rows=43 loops=1) -> Hash (cost=11.39..11.39 rows=39 width=12) (actual time=0.039..0.039 rows=39 loops=1) Buckets: 1024 Batches: 1 Memory Usage: 10kB -> Seq Scan on product_template t (cost=0.00..11.39 rows=39 width=12) (actual time=0.006..0.030 rows=39 loops=1) -> Limit (cost=0.28..36.46 rows=1 width=8) (actual time=0.266..0.266 rows=1 loops=32732) -> Index Scan using res_currency_rate_currency_id_index on res_currency_rate (cost=0.28..36.46 rows=1 width=8) (actual time=0.266..0.266 rows=1 loops=32732) Index Cond: (currency_id = pp.currency_id) Filter: (((company_id = s.company_id) OR (company_id IS NULL)) AND (daterange(name, COALESCE(date_end, (now())::date)) @> (COALESCE((s.date_order)::timestamp with time zone, now()))::date)) Rows Removed by Filter: 502 Planning time: 5.731 ms Execution time: 8944.950 ms (45 rows) Have a nice day, Nicolas. 2018-06-01 0:32 GMT+02:00 Adrian Klaver : > On 05/31/2018 02:09 AM, Nicolas Seinlet wrote: > >> Hi, >> >> I have a query with a strange query plan. >> >> This query is roughly searching for sales, and convert them with a >> currency rate. As currency rate changes from time to time, table contains >> the currency, the company, the rate, the start date of availability of this >> rate and the end date of availability. >> > > My guess is to get a complete answer you are going to need to provide: > > 1) The complete query. > > 2) The complete EXPLAIN ANALYZE. > > More comments inline below. > > >> The join is done using : >> left join currency_rate cr on (cr.currency_id = pp.currency_id and >>cr.company_id = s.company_id and >>cr.date_start <= coalesce(s.date_order, now()) and >> (cr.date_end is null or cr.date_end > coalesce(s.dat
Re: Sort is generating rows
FROM res_currency_rate r
JOIN res_company c ON r.company_id IS NULL OR r.company_id =
c.id) cr ON cr.currency_id = pp.currency_id AND (cr.company_id =
s.company_id OR cr.company_id IS NULL) AND cr.date_start <=
COALESCE(s.date_order::timestamp with time zone, now()) AND (cr.date_end IS
NULL OR cr.date_end > COALESCE(s.date_order::timestamp with time zone,
now()))
GROUP BY l.product_id, l.order_id, t.uom_id, t.categ_id, s.name,
s.date_order, s.confirmation_date, s.partner_id, s.user_id, s.state,
s.company_id, s.pricelist_id, s.analytic_account_id, s.team_id,
p.product_tmpl_id, partner.country_id, partner.commercial_partner_id;
And thanks again for the help.
Have a nice day,
Nicolas
2018-06-01 16:33 GMT+02:00 Adrian Klaver :
> On 06/01/2018 02:36 AM, Nicolas Seinlet wrote:
>
>> Hi,
>>
>> thanks for the answer. The query is based on a view, so here are the
>> view, the query as well as the query plan.
>> I've already taken into account remarks like date ranges.
>>
>
> You changed the query from the original, besides just adding the
> daterange, I see an addition of a LATERAL, where there other changes?
>
> The changes did eliminate the 300 million line sort from what I can see.
>
> The new query takes ~9 secs is that an improvement over the old?
>
> I took the liberty of running the EXPLAIN ANALYZE through
> explain.depesz.com:
>
> https://explain.depesz.com/s/9thl
>
> The largest amount of time was in the Index Scan(8,706.712ms) and that was
> because the scan was looped 32,732 times. I have not used LATERAL in my own
> code so I looked it up:
>
> https://www.postgresql.org/docs/10/static/sql-select.html
>
> LATERAL
>
> "
> ...
>
> When a FROM item contains LATERAL cross-references, evaluation proceeds as
> follows: for each row of the FROM item providing the cross-referenced
> column(s), or set of rows of multiple FROM items providing the columns, the
> LATERAL item is evaluated using that row or row set's values of the
> columns. The resulting row(s) are joined as usual with the rows they were
> computed from. This is repeated for each row or set of rows from the column
> source table(s).
> ...
> "
>
> If I am following correctly that might explain some of looping seen above.
>
>
>> SELECT min(l.id <http://l.id>) AS id,
>> l.product_id,
>> t.uom_id AS product_uom,
>> sum(l.product_uom_qty / u.factor * u2.factor) AS product_uom_qty,
>> sum(l.qty_delivered / u.factor * u2.factor) AS qty_delivered,
>> sum(l.qty_invoiced / u.factor * u2.factor) AS qty_invoiced,
>> sum(l.qty_to_invoice / u.factor * u2.factor) AS qty_to_invoice,
>> sum(l.price_total / COALESCE(cr.rate, 1.0)) AS price_total,
>> sum(l.price_subtotal / COALESCE(cr.rate, 1.0)) AS price_subtotal,
>> sum(l.price_reduce * l.qty_to_invoice / COALESCE(cr.rate, 1.0)) AS
>> amount_to_invoice,
>> sum(l.price_reduce * l.qty_invoiced / COALESCE(cr.rate, 1.0)) AS
>> amount_invoiced,
>> count(*) AS nbr,
>> s.name <http://s.name>,
>> s.date_order AS date,
>> s.confirmation_date,
>> s.state,
>> s.partner_id,
>> s.user_id,
>> s.company_id,
>> date_part('epoch'::text, avg(date_trunc('day'::text, s.date_order) -
>> date_trunc('day'::text, s.create_date))) / (24 * 60 *
>> 60)::numeric(16,2)::double precision AS delay,
>> t.categ_id,
>> s.pricelist_id,
>> s.analytic_account_id,
>> s.team_id,
>> p.product_tmpl_id,
>> partner.country_id,
>> partner.commercial_partner_id,
>> sum(p.weight * l.product_uom_qty / u.factor * u2.factor) AS weight,
>> sum(p.volume * l.product_uom_qty::double precision /
>> u.factor::double precision * u2.factor::double precision) AS volume
>> FROM sale_order_line l
>> JOIN sale_order s ON l.order_id = s.id <http://s.id>
>> JOIN res_partner partner ON s.partner_id = partner.id <
>> http://partner.id>
>> LEFT JOIN product_product p ON l.product_id = p.id <http://p.id>
>> LEFT JOIN product_template t ON p.product_tmpl_id = t.id <
>> http://t.id>
>> LEFT JOIN uom_uom u ON u.id <http://u.id> = l.product_uom
>> LEFT JOIN uom_uom u2 ON u2.id <http://u2.id> = t.uom_id
>> JOIN product_pricelist pp ON s.pricelist_id = pp.id <http://pp.id>
>> LEFT JOIN LATERAL ( SELECT res_currency_rate.rate
>> FROM res_currency_rate
>>WHERE res_currency_rate.currency_id = pp.currency_id AND
>> (res_currency_r
Re: Sort is generating rows
Hi, I've tried some tests, by generating various datas in the res_currency_rate table. If I generate res_currency_rate rows for unsused currencies, this doesn't influence the execution time. if I generate more res_currency_rate for used currencies, this slower the query. If I generate 100 rates, on a one per day basis, I get an execution time of 4.5 seconds If I generate 100 rates, on a one per 3 days basis, execution time drops to 4 seconds If I generate 100 rates, on a one per 6 days basis, execution time drops to 3.8 seconds. I've executed following tests many times, to avoid cache or buffers related issues, each time after vacuuming table, ... The execution time with a join of type daterange : res_currency_rate.currency_id = pp.currency_id AND (res_currency_rate.company_id = s.company_id OR res_currency_rate.company_id IS NULL) AND daterange(res_currency_rate.name, res_currency_rate.date_end) @> COALESCE(s.date_order::timestamp with time zone, now())::date is slower than the date comparison equivalent: res_currency_rate.currency_id = pp.currency_id AND (res_currency_rate.company_id = s.company_id OR res_currency_rate.company_id IS NULL) AND res_currency_rate.name <= COALESCE(s.date_order::timestamp with time zone, now()) AND (res_currency_rate.date_end IS NULL OR res_currency_rate.date_end > COALESCE(s.date_order::timestamp with time zone, now()))
Re: Sort is generating rows
Hi, a currency rate can have no company, and is then applicable to currencies which have no rate specific for the company. Le dim. 10 juin 2018 à 17:24, Adrian Klaver a écrit : > On 06/05/2018 07:58 AM, Nicolas Seinlet wrote: > > Hi, > > > > I've tried some tests, by generating various datas in > > the res_currency_rate table. > > > > If I generate res_currency_rate rows for unsused currencies, this > > doesn't influence the execution time. > > if I generate more res_currency_rate for used currencies, this slower > > the query. > > If I generate 100 rates, on a one per day basis, I get an execution time > > of 4.5 seconds > > If I generate 100 rates, on a one per 3 days basis, execution time drops > > to 4 seconds > > If I generate 100 rates, on a one per 6 days basis, execution time drops > > to 3.8 seconds. > > I took another look at the query and got to wondering about the snippet > below: > > > LEFT JOIN (SELECT > r.currency_id, COALESCE(r.company_id, c.id) AS > company_id, r.rate, > r.name AS date_start, r.date_end > FROM > res_currency_rate r > JOIN > res_company c > ON > r.company_id IS NULL OR r.company_id = c.id > ) cr > ON > cr.currency_id = pp.currency_id > AND > (cr.company_id = s.company_id OR cr.company_id IS NULL) > AND > daterange(cr.date_start, COALESCE(cr.date_end, now()::date)) @> > COALESCE(s.date_order::timestamp with time zone, now())::date > > In particular: > > FROM > res_currency_rate r > JOIN > res_company c > ON > r.company_id IS NULL OR r.company_id = c.id > > Are there NULL company_id values in res_currency_rate? > > If so I am trying to figure out how the JOIN to res_company would work > in that situation. > > What happens if eliminate the r.company_id IS NULL? > > > > > I've executed following tests many times, to avoid cache or buffers > > related issues, each time after vacuuming table, ... > > > > The execution time with a join of type daterange : > > res_currency_rate.currency_id = pp.currency_id AND > > (res_currency_rate.company_id = s.company_id OR > > res_currency_rate.company_id IS NULL) AND > > daterange(res_currency_rate.name <http://res_currency_rate.name>, > > res_currency_rate.date_end) @> COALESCE(s.date_order::timestamp with > > time zone, now())::date > > > > is slower than the date comparison equivalent: > > res_currency_rate.currency_id = pp.currency_id AND > > (res_currency_rate.company_id = s.company_id OR > > res_currency_rate.company_id IS NULL) AND res_currency_rate.name > > <http://res_currency_rate.name> <= COALESCE(s.date_order::timestamp > with > > time zone, now()) AND (res_currency_rate.date_end IS NULL OR > > res_currency_rate.date_end > COALESCE(s.date_order::timestamp with time > > zone, now())) > > > > > > > -- > Adrian Klaver > adrian.kla...@aklaver.com >
plan using BTree VS GIN
Hello, we faced an issue with a select query on a relatively large table on our
database. The query involves one single table. The table has more than 10
million records. It's mainly composed of varchar fields, have a primary key
(id) of type serial, and when records of this table are shown to users, they
are sorted users 2 fields, display_name (varchar) and id (the primary key).
Because this table is heavily used in various contexts in our application, we
have multiple indexes on it. Among other index, we have gin index on some
fields of the table. Among other things, we're using unaccent. We are aware the
unaccent function is mutable, but we have an immutable version of unaccent. the
table is similar to (I can give you all the fields of the table if needed):
Table "public.res_partner"
Column | Type | Collation |
Nullable | Default
---+-+---+--+-
id | integer | |
not null | nextval('res_partner_id_seq'::regclass) active
| boolean | | | name
| character varying | | |
display_name | character varying | |
| ref | character varying |
| | email | character varying
| | | vat | character
varying | | | type |
character varying | | | company_registry
| character varying | | | Gin Index:
"res_partner_unaccent_tgm_ref" gin (unaccent(ref::text) gin_trgm_ops) WHERE ref
IS NOT NULL "res_partner_unaccent_tgm_vat" gin (unaccent(vat::text)
gin_trgm_ops) WHERE vat IS NOT NULL "res_partner_unaccent_tgm_idx_gin2" gin
(unaccent(name::text) gin_trgm_ops, unaccent(display_name::text) gin_trgm_ops,
unaccent(ref::text) gin_trgm_ops, unaccent(email::text) gin_trgm_ops,
unaccent(vat::text) gin_trgm_ops) "res_partner_name_tgm_idx_gin" gin (name
gin_trgm_ops, display_name gin_trgm_ops, ref gin_trgm_ops, email gin_trgm_ops,
vat gin_trgm_ops) "res_partner_unaccent_tgm_display_namee" gin
(unaccent(display_name::text) gin_trgm_ops) "res_partner_unaccent_tgm_email"
gin (unaccent(email::text) gin_trgm_ops) WHERE email IS NOT NULL
"res_partner_comp_reg_idx3" gin (unaccent(company_registry::text) gin_trgm_ops)
WHERE company_registry IS NOT NULL BTree index:
"res_partner_displayname_id_idx" btree (display_name, id) WHERE active
"res_partner_comp_reg_idx2" btree (unaccent(company_registry::text)) WHERE
company_registry IS NOT NULL The btree index res_partner_displayname_id_idx
have been added lately and perfectly match a criteria (where active) and
sorting (display_name, id) we have in quite all our queries on this table. The
query that cause the issue is this one: SELECT "res_partner"."id" FROM
"res_partner" WHERE (("res_partner"."active" = true) AND ( (
( ((unaccent("res_partner"."display_name"::text) ilike
unaccent('%nse%')) OR (unaccent("res_partner"."email"::text) ilike
unaccent('%nse%'))) OR (unaccent("res_partner"."ref"::text) ilike
unaccent('%nse)%'))) OR (unaccent("res_partner"."vat"::text) ilike
unaccent('%nse%'))) OR (unaccent("res_partner"."company_registry"::text)
ilike unaccent('%nse)%' AND ((("res_partner"."type" != 'private') OR
"res_partner"."type" IS NULL) OR "res_partner"."type" IS NULL ) ORDER BY
"res_partner"."display_name" ,"res_partner"."id" LIMIT 100 We have the
common criteria (active=true), the common sorting, a limit, and a search on
various fields. The fields on which we're searching with criteria like '%
whatever%' are gin indexed. Here is the query plan: Limit
(cost=0.56..10703.36 rows=100 width=25) (actual time=56383.794..86509.036
rows=1 loops=1) Output: id, display_name Buffers: shared hit=4322296
read=1608998 dirtied=1 written=1247 -> Index Scan using
res_partner_displayname_id_idx on public.res_partner (cost=0.56..1200212.37
rows=11214 width=25) (actual time=56383.793..86509.022 rows=1 loops=1)
Output: id, display_name Filter: res_partner.type)::text <>
'private'::text) OR (res_partner.type IS NULL) OR (res_partner.type IS NULL))
AND ((unaccent((res_partner.display_name)::text) ~~* '%nse%'::text) OR
(unaccent((res_partner.email)::text) ~~ * '%nse%'::text) OR
(unaccent((res_partner.ref)::text) ~~* '%nse%'::text) OR
(unaccent((res_partner.vat)::text) ~~* '%nse%'::text) OR
Re: plan using BTree VS GIN
On Friday, June 23rd, 2023 at 2:52 PM, Laurenz Albe
wrote:
>
>
> On Fri, 2023-06-23 at 12:08 +, Nicolas Seinlet wrote:
>
> > we faced an issue with a select query on a relatively large table on our
> > database.
> > The query involves one single table. The table has more than 10 million
> > records.
> > It's mainly composed of varchar fields, have a primary key (id) of type
> > serial,
> > and when records of this table are shown to users, they are sorted users 2
> > fields,
> > display_name (varchar) and id (the primary key). Because this table is
> > heavily used
> > in various contexts in our application, we have multiple indexes on it.
> > Among other
> > index, we have gin index on some fields of the table.
> >
> > The btree index res_partner_displayname_id_idx have been added lately and
> > perfectly
> > match a criteria (where active) and sorting (display_name, id) we have in
> > quite all
> > our queries on this table.
> >
> > The query that cause the issue is this one:
> > SELECT "res_partner"."id"
> > FROM "res_partner"
> > WHERE (("res_partner"."active" = true) AND
> > (
> > (
> > (
> > ((unaccent("res_partner"."display_name"::text) ilike unaccent('%nse%'))
> > OR (unaccent("res_partner"."email"::text) ilike unaccent('%nse%')))
> > OR (unaccent("res_partner"."ref"::text) ilike unaccent('%nse)%')))
> > OR (unaccent("res_partner"."vat"::text) ilike unaccent('%nse%')))
> > OR (unaccent("res_partner"."company_registry"::text) ilike
> > unaccent('%nse)%'
> >
> > AND ((("res_partner"."type" != 'private') OR "res_partner"."type" IS NULL)
> > OR "res_partner"."type" IS NULL )
> >
> > ORDER BY "res_partner"."display_name" ,"res_partner"."id"
> > LIMIT 100
> >
> > We have the common criteria (active=true), the common sorting, a limit, and
> > a search
> > on various fields. The fields on which we're searching with criteria like
> > '% whatever%' are gin indexed.
> >
> > Here is the query plan:
> > Limit (cost=0.56..10703.36 rows=100 width=25) (actual
> > time=56383.794..86509.036 rows=1 loops=1)
> > Output: id, display_name
> > Buffers: shared hit=4322296 read=1608998 dirtied=1 written=1247
> > -> Index Scan using res_partner_displayname_id_idx on public.res_partner
> > (cost=0.56..1200212.37 rows=11214 width=25) (actual
> > time=56383.793..86509.022 rows=1 loops=1)
> > Output: id, display_name
> > Filter: res_partner.type)::text <> 'private'::text) OR
> > (res_partner.type IS NULL) OR (res_partner.type IS NULL)) AND
> > ((unaccent((res_partner.display_name)::text) ~~* '%nse%'::text) OR
> > (unaccent((res_partner.email)::text) ~~
> > * '%nse%'::text) OR (unaccent((res_partner.ref)::text) ~~* '%nse%'::text)
> > OR (unaccent((res_partner.vat)::text) ~~* '%nse%'::text) OR
> > (unaccent((res_partner.company_registry)::text) ~~*
> > '%nse%'::text)))
> > Rows Removed by Filter: 6226870
> > Buffers: shared hit=4322296 read=1608998 dirtied=1 written=1247
> > Planning Time: 0.891 ms
> > Execution Time: 86509.070 ms
> > (10 rows)
> >
> > It's not using our gin index at all, but the btree one.
>
>
> The problem is that PostgreSQL estimates that the index scan will return 11214
> rows, when it is actually one. This makes the plan to scan the table using
> an index that matches the ORDER BY clause appealing: we might find 100 rows
> quickly and avoid a sort.
>
> You can try to improve the estimates with more detailed statistics,
> but if that doesn't do the job, you can modify the ORDER BY clause so
> that it cannot use the bad index:
>
> ORDER BY res_partner.display_name ,res_partner.id + 0
>
> Yours,
> Laurenz Albe
Hello,
First of all, thanks, this solves the issue for the given query.
Some more questions then,
> we might find 100 rows quickly
The cost estimate for 11214 rows is 1200212.37
If I look at the other plan, none of the estimated cost reach such levels (~2k
for indexes + 1k for the BitmapOr, 3k for Bitmap Heap Scan, and finally 1k for
sort and limit), roughly 7k
And that's part of what I didn't understand. How is the first cost estimated?
If we divide by 110 the cost to go from 11k records to 100, it's still ~10k,
more than the other plan.
Thanks again,
Nicolas.
publickey - nicolas@seinlet.com - 0xCAEB7FAF.asc
Description: application/pgp-keys
signature.asc
Description: OpenPGP digital signature
Failing streaming replication on PostgreSQL 14
Hello everyone, Since I moved some clusters from PostgreSQL 12 to 14, I noticed random failures in streaming replication. I say "random" mostly because I haven't got the source of the issue. I'm using the Ubuntu/cyphered ZFS/PostgreSQL combination. I'm using Ubuntu LTS (20.04 22.04) and provided ZFS/PostgreSQL with LTS (PostgreSQL 12 on Ubuntu 20.04 and 14 on 22.04). The streaming replication of PostgreSQL is configured with `primary_conninfo 'host=main_server port=5432 user=replicant password=a_very_secure_password sslmode=require application_name=replication_postgresql_app' ` , no replication slot nor restore command, and the wal is configured with `full_page_writes = off wal_init_zero = off wal_recycle = off` If this works like a charm on PostgreSQL 12, it's sometimes failing with PostgreSQL 14. As we also changed the OS, maybe the issue relies somewhere else. When the issue is detected, the WAL on the primary is correct. A piece of the WAL is wrong on the secondary. Only some bytes. Some bytes later, the wal is again correct. Stopping PostgreSQL on the secondary, removing the wrong WAL file, and restarting PostgreSQL solves the issue. We've added another secondary and noticed the issue can appear on one of the secondaries, not both at the same time. What can I do to detect the origin of this issue? Have a nice week, Nicolas. signature.asc Description: OpenPGP digital signature
Re: Failing streaming replication on PostgreSQL 14
On Monday, April 15th, 2024 at 14:36, Ron Johnson wrote: > On Mon, Apr 15, 2024 at 2:53 AM Nicolas Seinlet wrote: > > > Hello everyone, > > > > Since I moved some clusters from PostgreSQL 12 to 14, I noticed random > > failures in streaming replication. I say "random" mostly because I haven't > > got the source of the issue. > > > > I'm using the Ubuntu/cyphered ZFS/PostgreSQL combination. I'm using Ubuntu > > LTS (20.04 22.04) and provided ZFS/PostgreSQL with LTS (PostgreSQL 12 on > > Ubuntu 20.04 and 14 on 22.04). > > > > The streaming replication of PostgreSQL is configured with > > `primary_conninfo 'host=main_server port=5432 user=replicant > > password=a_very_secure_password sslmode=require > > application_name=replication_postgresql_app' ` , no replication slot nor > > restore command, and the wal is configured with `full_page_writes = off > > wal_init_zero = off wal_recycle = off` > > > > If this works like a charm on PostgreSQL 12, it's sometimes failing with > > PostgreSQL 14. As we also changed the OS, maybe the issue relies somewhere > > else. > > > > When the issue is detected, the WAL on the primary is correct. A piece of > > the WAL is wrong on the secondary. Only some bytes. Some bytes later, the > > wal is again correct. Stopping PostgreSQL on the secondary, removing the > > wrong WAL file, and restarting PostgreSQL solves the issue. > > > > We've added another secondary and noticed the issue can appear on one of > > the secondaries, not both at the same time. > > > > What can I do to detect the origin of this issue? > > > 1. Minor version number? > 2. Using replication_slots? > 3. Error message(s)? > > Hi, 1. PostgreSQL 14.11 2. No. no replication slot nor restore command. As we've understood the replication slot, it's a mechanism to keep on the primary side everything needed for the secondary to recover. Will this make the primary acknowledge that the secondary received the good wal file? 3. incorrect resource manager data checksum Looking at the WAL files with xxd gives the following diff: The bad one: 006c9160: 0a6e 7514 5030 2e31 0e35 016c 0f07 0009 2f62 6568 6100 7669 6f72 3a6e 6f72 be6d .nu.P0.1.5.l/beha.vior:nor.m 006c9180: 006c91a0: 006c91c0: 437a 4263 7500 7273 6f72 3a70 6f69 0302 4503 9023 3237 3665 3720 323b 223e 5527 CzBcu.rsor:poi..E..#276e7 2;">U' The good one contains the same 1st and 4th lines, but the 2nd and 3rd lines contain the correct values, as if a packet was missed. Thanks for helping, Nicolas. signature.asc Description: OpenPGP digital signature
Re: Failing streaming replication on PostgreSQL 14
Hello, > What exactly is "cyphered ZFS"? Can you reproduce the problem with some > other filesystem? If it's something very unusual, it might well be a > bug in the filesystem. The filesystem is openzfs with native aes-256-gcm encryption: https://openzfs.github.io/openzfs-docs/man/master/7/zfsprops.7.html#encryption I've not tested if we get the same issue on another filesystem. I don't face the issue on Ubuntu 20.04/openzfs 0.8/PostgreSQL 12, but I have fewer systems with this deployment. On Ubuntu 22.04/openzfs 2.1.5/PostgreSQL 14, I face the issue from time to time, without knowing what triggers the error. thanks for helping, Nicolas. publickey - nicolas@seinlet.com - 0xCAEB7FAF.asc Description: application/pgp-keys signature.asc Description: OpenPGP digital signature
Re: Failing streaming replication on PostgreSQL 14
Hi, facing the same situation again, but this time, analyzing the wal with xxd shows a different pattern. I had no blocks of . The output of pg_waldump is: pg_waldump: fatal: error in WAL record at 11C/93F9FF70: invalid magic number in log segment 0001011C0093, offset 16384000 The output of xxd -C16 is 00f9ff60: b364 0079 6e61 6d69 6320 6c80 0300 .d.ynamic l. 00f9ff70: 4000 6659 a406 60f7 f993 1c01 @...fY..`... 00f9ff80: 000b 82b3 8d9b 0020 1000 7f06 . .. I'm still unable to determine the cause of the issue, nor if the issue is on the primary server sending a corrupted wal segment, or on the secondary receiving a corrupted wal segment, or the openzfs filesystem on the primary allowing wal_sender to read still-not-written wal segment, or ... Is there any log option I can add on the two clusters to help me locate the issue's origin? thanks, Nicolas. On Tuesday, April 16th, 2024 at 09:56, Nicolas Seinlet wrote: > > > Hello, > > > What exactly is "cyphered ZFS"? Can you reproduce the problem with some > > other filesystem? If it's something very unusual, it might well be a > > bug in the filesystem. > > > The filesystem is openzfs with native aes-256-gcm encryption: > https://openzfs.github.io/openzfs-docs/man/master/7/zfsprops.7.html#encryption > > I've not tested if we get the same issue on another filesystem. > > I don't face the issue on Ubuntu 20.04/openzfs 0.8/PostgreSQL 12, but I have > fewer systems with this deployment. > On Ubuntu 22.04/openzfs 2.1.5/PostgreSQL 14, I face the issue from time to > time, without knowing what triggers the error. > > thanks for helping, > > Nicolas. signature.asc Description: OpenPGP digital signature
second CTE kills perf
Hello, I'm trying to understand this behaviour and the limits of CTE, when they reach the limits, when they cannot receive parameters from a caller, ... I'm running a query on PostgreSQL 10 with a cte. the query runs in ~ 10 seconds. If I add a second CTE with the same query as the previous one and select * from second_cte as query, it now runs in ~ 10 minutes. oversimplified example: 10 seconds version: | WITH cte1 AS (SELECT x,y,z FROM table) SELECT row_number() over(),x,y,z FROM cte1 WHERE x=32; 10 minutes version: | WITH cte1 AS (SELECT x,y,z FROM table), cte2 AS (SELECT row_number() over(),x,y,z FROM cte1) SELECT * FROM cte2 WHERE x=32; The real examples, with query plans: https://explain.dalibo.com/plan/98A https://explain.dalibo.com/plan/o6X4 Thanks for your time, Nicolas Seinlet. publickey - nicolas@seinlet.com - 0xCAEB7FAF.asc Description: application/pgp-keys signature.asc Description: OpenPGP digital signature
Re: second CTE kills perf
Hi, thanks for all. I replaced row_number() with some computed int which speeds up a lot the query. ‐‐‐ Original Message ‐‐‐ On Tuesday, June 22nd, 2021 at 15:53, Tom Lane wrote: > Nicolas Seinlet nico...@seinlet.com writes: > > > I'm trying to understand this behaviour and the limits of CTE, when they > > reach the limits, when they cannot receive parameters from a caller, ... > > I'm running a query on PostgreSQL 10 with a cte. the query runs in ~ 10 > > seconds. If I add a second CTE with the same query as the previous one and > > select * from second_cte as query, it now runs in ~ 10 minutes. > > > oversimplified example: > > > > 10 seconds version: > > > > | WITH cte1 AS (SELECT x,y,z FROM table) SELECT row_number() over(),x,y,z > > FROM cte1 WHERE x=32; > > > 10 minutes version: > > > > | WITH cte1 AS (SELECT x,y,z FROM table), cte2 AS (SELECT row_number() > > over(),x,y,z FROM cte1) SELECT * FROM cte2 WHERE x=32; > > [ shrug... ] You are asking for two different computations, and the > > second one is far more expensive. > > In the first case, the WHERE x=32 clause is applied before the window > > function, so we can (indeed must) filter out all rows not having x=32 > > before doing the window function. > > In the second case, WHERE x=32 is applied above/after the window > > function. We cannot push down the WHERE to before the window function. > > (In this case, filtering beforehand would obviously change the results > > of row_number, but in general we don't know enough about window function > > behavior to risk such changes.) So row_number has to be computed over > > the entire contents of the "table", and that's not cheap. > > It does surprise me a bit that row_number is quite that expensive, > > but if you are expecting equivalent results from these two queries, > > you're simply wrong. > > regards, tom lane publickey - nicolas@seinlet.com - 0xCAEB7FAF.asc Description: application/pgp-keys signature.asc Description: OpenPGP digital signature
More records after sort
Hello,
I'm facing a strange behaviour and I don't understand why. From a wider query
plan, https://explain.dalibo.com/plan/7yh ,a table is scanned and filter based
on its primary key, returning 98 records. Then, those records are sorted on
another field, and the output is 758,247,643 records.
What can make PostgreSQL to generate records while sorting? What can I do to
understand why this is occuring?
-> Sort (cost=228.52..228.76 rows=98 width=16)
(actual time=1.502..31540.651 rows=758247643 loops=1)
Sort Key: i.currency_id
Sort Method: quicksort Memory: 29kB
-> Index Scan using account_invoice_pkey on
account_invoice i (cost=0.29..225.28 rows=98 width=16) (actual
time=0.035..1.481 rows=98 loops=1)
Index Cond: (id = ANY
('{3839,6912,7164,11026,6479,9973,3599,11306,4092,10652,2732,8625,198,10536,5876,7864,5498,13080,4660,8948,597,10762,12573,11613,12905,11483,11227,12876,4470,8954,5628,4386,9321,4398,11595,7125,8116,1231,1610,6828,2105,9658,1616,5735,9066,4631,51,1185,11736,9579,4303,9983,12759,3889,4667,11258,9423,11855,6555,3164,7597,5596,10396,620,10330,4082,926,3252,5375,8118,7141,3500,12065,2794,2367,12088,7454,7998,11439,8893,6304,10814,7723,4393,3901,3383,5770,5857,4746,1858,5320,4574,8314,11914,7930,7308,6228,3627}'::integer[]))
I can of course describe data model or full query.
Thanks again for reading me,
Nicolas.
publickey - nicolas@seinlet.com - 0xCAEB7FAF.asc
Description: application/pgp-keys
signature.asc
Description: OpenPGP digital signature
Re: More records after sort
On Tuesday, July 20th, 2021 at 16:34, Tom Lane wrote: > Nicolas Seinlet nico...@seinlet.com writes: > > > I'm facing a strange behaviour and I don't understand why. From a wider > > query plan, https://explain.dalibo.com/plan/7yh ,a table is scanned and > > filter based on its primary key, returning 98 records. Then, those records > > are sorted on another field, and the output is 758,247,643 records. > > You haven't showed us the whole plan; tsk tsk. > > However, I'm going to bet that this sort is the inner input of a merge > > join, and that the apparent "extra" tuples are caused by the merge backing > > up and rescanning the sort result to deal with duplicate join keys in its > > outer input. There must be a heck of a lot of duplicates. The planner > > would typically avoid using merge join if it knew that, so I'm wondering > > if you have up-to-date statistics for the outer side of the join. > > regards, tom lane Many thanks for the fast response. The full query plan is below, and the Dalibo link points to it as well. You're right, it's a merge join. One table doesn't have up-to-date statistics, because the table is filled during the transaction involving this query. A workaround we found, but we don't understand why and it's frustrating, is to remove a join from the query (the one with currency table) and replace it with a new column in the table for which the sort generates records. the currency table has up to date statistics, is vacuumed, ... The table with no up-to-date statistics is involved in a where clause: AND NOT EXISTS (SELECT invl_id FROM invl_aml_mapping WHERE invl_id=il.id) AND NOT EXISTS (SELECT aml_id FROM invl_aml_mapping WHERE aml_id=ml.id) Query: INSERT INTO invl_aml_mapping_temp(invl_id, aml_id, cond) SELECT il.id, ml.id, 48 FROM account_invoice_line il JOIN account_invoice i ON i.id = il.invoice_id JOIN account_move m ON m.id = i.move_id JOIN account_move_line ml ON ml.move_id = m.id JOIN res_company comp ON comp.id = i.company_id WHERE il.display_type IS NULL AND ml.tax_line_id IS NULL AND NOT EXISTS (SELECT invl_id FROM invl_aml_mapping WHERE invl_id=il.id) AND NOT EXISTS (SELECT aml_id FROM invl_aml_mapping WHERE aml_id=ml.id) AND i.id = ANY(ARRAY[3839,6912,7164,11026,6479,9973,3599,11306,4092,10652,2732,8625,198,10536,5876,7864,5498,13080,4660,8948,597,10762,12573,11613,12905,11483,11227,12876,4470,8954,5628,4386,9321,4398,11595,7125,8116,1231,1610,6828,2105,9658,1616,5735,9066,4631,51,1185,11736,9579,4303,9983,12759,3889,4667,11258,9423,11855,6555,3164,7597,5596,10396,620,10330,4082,926,3252,5375,8118,7141,3500,12065,2794,2367,12088,7454,7998,11439,8893,6304,10814,7723,4393,3901,3383,5770,5857,4746,1858,5320,4574,8314,11914,7930,7308,6228,3627]) AND il.account_id = ml.account_id AND ( ARRAY(SELECT r.tax_id FROM account_invoice_line_tax r WHERE r.invoice_line_id = il.id ORDER BY r.tax_id) = ARRAY(SELECT r.account_tax_id FROM account_move_line_account_tax_rel r WHERE r.account_move_line_id = ml.id ORDER BY r.account_tax_id) ) AND ROUND(il.price_subtotal - ml._mig_124_precomputed_amount, i.decimal_places) = 0.0 AND il.product_id = ml.product_id; Plan: - -- Insert on invl_aml_mapping_temp (cost=241.69..505.08 rows=1 width=12) (actual time=185635.849..185635.883 rows=0 loops=1) -> Nested Loop Anti Join (cost=241.69..505.08 rows=1 width=12) (actual time=185635.847..185635.880 rows=0 loops=1) -> Nested Loop (cost=241.27..504.61 rows=1 width=8) (actual time=185635.846..185635.873 rows=0 loops=1) -> Nested Loop (cost=241.15..504.43 rows=1 width=12) (actual time=185635.839..185635.866 rows=0 loops=1) Join Filter: (i.move_id = m.id) -> Merge Join (cost=240.73..503.98 rows=1 width=20) (actual time=185635.815..185635.836 r
Re: More records after sort
Thanks all, I'll try this on Monday when I go back to the office. Nicolas. ‐‐‐ Original Message ‐‐‐ On Wednesday, July 21st, 2021 at 02:55, Rob Sargent wrote: > > Query: > > > > INSERT INTO invl_aml_mapping_temp(invl_id, aml_id, cond) > > > > SELECT il.id, ml.id, 48 > > > > FROM account_invoice_line il > > > > JOIN account_invoice i ON i.id = il.invoice_id > > > > JOIN account_move m ON m.id = i.move_id > > > > JOIN account_move_line ml ON ml.move_id = m.id > > > > JOIN res_company comp ON comp.id = i.company_id > > > > WHERE il.display_type IS NULL > > > > AND ml.tax_line_id IS NULL > > > > AND NOT EXISTS (SELECT invl_id FROM invl_aml_mapping WHERE > > invl_id=il.id) > > > > AND NOT EXISTS (SELECT aml_id FROM invl_aml_mapping WHERE > > aml_id=ml.id) > > > > AND i.id = > > Out of curiosity, would combining those two NOT EXISTS in one help? Ever? > > > i.e. WHERE invl_id=il.id or aml_id = ml.id publickey - nicolas@seinlet.com - 0xCAEB7FAF.asc Description: application/pgp-keys signature.asc Description: OpenPGP digital signature
