bug or lacking doc hint
Hi, I have had a perf (++) pb with a join plan cf the pb with join plan thread. I did simplify the thing up to when its a simple join between a 15M lines table and a 30k lines table. if I put in the on part something like table1.a=table2.b, Postgres does the job in around 1 seconde. if in the on part of the join I write table1.a=table2.b OR substr(table1.c,x,x) =table2.d then I have to cancel the request after 20 minutes seeing disk usage growing and growing. When I ask this list, David Rowley suggest to rewrite the SQL, replacing the OR by a union. Fine, this do work, even if a bit complex as the original SQL was a set of intricate joins. So, either this behaviour ( postgres unable to find an appropriate plan for join with OR clauses) is a true bug which is waiting to be corrected, either the doc is lacking a chapter on which one can read that Postgres does NOT support that syntax as soon as there is some data volumes. So, now I am writing some kind of recipe book for the users of that DB. What should I write ? Marc MILLAS Senior Architect +33607850334 www.mokadb.com
Re: Why can't lseek the STDIN_FILENO?
OK, I see, I'm so sorry for my action. I will ask in cpplang slack community in the future. And really thanks for your advice. Your, Wen Yi -- Original -- From: "John McKown" https://unix.stackexchange.com/questions/502518/problems-when-test-whether-standard-input-is-capable-of-seeking The bottom line from the above post is that STDIN is not seekable when it is a terminal. On Fri, Jun 23, 2023, 21:17 Wen Yi <896634...@qq.com> wrote: Hi community, I am testing the lseek & write & read, and I write the code like this: /* lseek_test.c Test the lseek Wen Yi */ #include
Re: bug or lacking doc hint
On 6/25/23 10:01, Marc Millas wrote: Hi, I have had a perf (++) pb with a join plan cf the pb with join plan thread. I did simplify the thing up to when its a simple join between a 15M lines table and a 30k lines table. if I put in the on part something like table1.a=table2.b, Postgres does the job in around 1 seconde. if in the on part of the join I write table1.a=table2.b OR substr(table1.c,x,x) =table2.d That left side SUBSTR() can't be good for the query optimizer. (Or I'm old and still using habits picked up 25 years ago on a different RDBMS.) Maybe a function index on table1 would help. -- Born in Arizona, moved to Babylonia.
Re: Helping planner to chose sequential scan when it improves performance
On Tue, Jun 13, 2023 at 3:28 PM Patrick O'Toole wrote: > run the query twice first, then... Is that a realistic way to run the test? Often forcing all the data needed for the query into memory is going to make things less realistic, not more realistic. Assuming the system has more stuff to do than just perform this one query, it might be unusual for the query to find everything it needs in memory. Also, if you really do want to do it this way, then you should do this for every plan. Different plans need to access a different collections of buffers, so prewarming just one plan will privilege that one over the others. > > PLAN A (default config, effective cache size just shy of 15GB): 3.829 > seconds. A nested loop is used to probe the hash index > `conversation_item_item_hash_index` for each row of item_text. Although the > cost of probing once is low, a fair amount of time passes because the > operation is repeated ~1.3 million times. > > PLAN B (enable_indexscan off, effective cache same as before): 3.254 > seconds (~15% speedup, sometimes 30%). Both tables are scanned sequentially > and conversation_item is hashed before results are combined with a hash > join. > > PLAN C: (random_page_cost = 8.0, instead of default 4, effective cache > same as before): 2.959 (~23% speedup, sometimes 38%). Same overall plan as > PLAN B, some differences in buffers and I/O. I'll note we had to get to 8.0 > before we saw a change to planner behavior; 5.0, 6.0, and 7.0 were too low > to make a difference. > The difference between B and C looks like it is entirely noise, having to do with how many buffers it found already in the cache and how many of them needed cleaning (which causes the buffer to be dirty as the cleaned version now needs to be written to disk) and how many dirty buffers it found that needed to be written in order to make way to read other buffers it needs. (This last number most generally reflects dirty buffers left around by other things which this query encountered, not the buffers the query itself dirtied). None of this is likely to be reproducible, and so not worth investigating. And the difference between A and BC is small enough that it is unlikely to be worth pursuing, either, even if it is reproducible. If your apps runs this one exact query often enough that a 30% difference is worth worrying about, you would probably be better served by questioning the business case. What are you doing with 1.4 million rows once you do fetch them, that it needs to be repeated so often? If you think that taking a deep dive into this one query is going to deliver knowledge which will pay off for other (so far unexamined) queries, I suspect you are wrong. Look for queries where the misestimation is more stark than 30% to serve as your case studies. > > Environment: > > Postgres 15.2 > Amazon RDS — db.m6g.2xlarge > > > Questions: > > In Plan A, what factors are causing the planner to select a substantially > slower plan despite having recent stats about number of rows? > Even if it were worth trying to answer this (which I think it is not), there isn't much we can do with dummy tables containing no data. You would need to include a script to generate data of a size and distribution which reproduces the given behavior. > Is there a substantial difference between the on-the-fly hash done in Plan B and Plan C compared to the hash-index used in Plan A? Can I assume they are essentially the same? Perhaps there are there differences in how they're applied? They are pretty much entirely different. Once jumps all over the index on disk, the other reads the table sequentially and (due to work_mem) parcels it out into chunks where it expects each chunk can also be read back in sequentially as well. About the only thing not different is that they both involve computing a hash function. > Is it common to see values for random_page_cost set as high as 8.0? We would of course need to investigate whether we see a net positive or net negative impact on other queries, to adopt this as a general setting, but is it a proposal we should actually consider? I've never needed to set it that high, but there is no a priori reason it wouldn't make sense to do. Settings that high would probably only be suitable for HDD (rather than SSD) storage and when caching is not very effective, which does seem to be the opposite of your situation. So I certainly wouldn't do it just based on the evidence at hand. Cheers, Jeff >
Re: bug or lacking doc hint
On Mon, 26 Jun 2023 at 03:02, Marc Millas wrote: > When I ask this list, David Rowley suggest to rewrite the SQL, replacing the > OR by a union. > > Fine, this do work, even if a bit complex as the original SQL was a set of > intricate joins. > > > So, either this behaviour ( postgres unable to find an appropriate plan for > join with OR clauses) > is a true bug which is waiting to be corrected, either the doc is lacking a > chapter on which one can read that Postgres does NOT support that syntax as > soon as there is some data volumes. The problem is that out of the 3 methods PostgreSQL uses to join tables, only 1 of them supports join conditions with an OR clause. Merge Join cannot do this because results can only be ordered one way at a time. Hash Join technically could do this, but it would require that it built multiple hash tables. Currently, it only builds one table. That leaves Nested Loop as the join method to implement joins with OR clauses. Unfortunately, nested loops are quadratic and the join condition must be evaluated once per each cartesian product row. That does not scale well. Tom Lane did start some work [1] to allow the planner to convert some queries to use UNION instead of evaluating OR clauses, but, if I remember correctly, it didn't handle ORs in join conditions, though perhaps having it do that would be a natural phase 2. I don't recall why the work stopped. > So, now I am writing some kind of recipe book for the users of that DB. > > What should I write ? You're asking a public mailing list to write private documentation for the company you work for? hmm. David [1] https://www.postgresql.org/message-id/flat/20180904015910.GA1797012%40rfd.leadboat.com#0ddccc114c74d1ddaedcb07b64530967
Re: bug or lacking doc hint
On Sun, Jun 25, 2023 at 9:35 PM David Rowley wrote: > On Mon, 26 Jun 2023 at 03:02, Marc Millas wrote: > > When I ask this list, David Rowley suggest to rewrite the SQL, replacing > the OR by a union. > > > > Fine, this do work, even if a bit complex as the original SQL was a set > of intricate joins. > > > > > > So, either this behaviour ( postgres unable to find an appropriate plan > for join with OR clauses) > > is a true bug which is waiting to be corrected, either the doc is > lacking a chapter on which one can read that Postgres does NOT support that > syntax as soon as there is some data volumes. > > The problem is that out of the 3 methods PostgreSQL uses to join > tables, only 1 of them supports join conditions with an OR clause. > Merge Join cannot do this because results can only be ordered one way > at a time. Hash Join technically could do this, but it would require > that it built multiple hash tables. Currently, it only builds one > table. That leaves Nested Loop as the join method to implement joins > with OR clauses. Unfortunately, nested loops are quadratic and the > join condition must be evaluated once per each cartesian product row. > That does not scale well. > > Tom Lane did start some work [1] to allow the planner to convert some > queries to use UNION instead of evaluating OR clauses, but, if I > remember correctly, it didn't handle ORs in join conditions, though > perhaps having it do that would be a natural phase 2. I don't recall > why the work stopped. > > > So, now I am writing some kind of recipe book for the users of that DB. > > > > What should I write ? > > You're asking a public mailing list to write private documentation for > the company you work for? hmm. > looks like some kind of misunderstanding: what I am asking is: should I , or am I allowed to write that, according to the community, Postgres is unable to do something ? > David > > [1] > https://www.postgresql.org/message-id/flat/20180904015910.GA1797012%40rfd.leadboat.com#0ddccc114c74d1ddaedcb07b64530967 Marc MILLAS Senior Architect +33607850334 www.mokadb.com
Re: Helping planner to chose sequential scan when it improves performance
On Wed, 14 Jun 2023 at 07:28, Patrick O'Toole wrote: > Maybe we are barking up the wrong tree with the previous questions. Are there > other configuration parameters we should consider first to improve > performance in situations like the one illustrated? random_page_cost and effective_cache_size are the main settings which will influence plan A vs plan B. Larger values of effective_cache_size will have the planner apply more seq_page_costs to the index scan. Lower values of effective_cache_size will mean more pages will be assumed to cost random_page_cost. David
Re: bug or lacking doc hint
David Rowley writes: > The problem is that out of the 3 methods PostgreSQL uses to join > tables, only 1 of them supports join conditions with an OR clause. > Merge Join cannot do this because results can only be ordered one way > at a time. Hash Join technically could do this, but it would require > that it built multiple hash tables. Currently, it only builds one > table. That leaves Nested Loop as the join method to implement joins > with OR clauses. Unfortunately, nested loops are quadratic and the > join condition must be evaluated once per each cartesian product row. We can do better than that if the OR'd conditions are each amenable to an index scan on one of the tables: then it can be a nestloop with a bitmap-OR'd inner index scan. I thought the upthread advice to convert the substr() condition into something that could be indexed was on-point. > Tom Lane did start some work [1] to allow the planner to convert some > queries to use UNION instead of evaluating OR clauses, but, if I > remember correctly, it didn't handle ORs in join conditions, though > perhaps having it do that would be a natural phase 2. I don't recall > why the work stopped. As I recall, I was having difficulty convincing myself that de-duplication of results (for cases where the same row satisfies more than one of the OR'd conditions) would work correctly. You can't just blindly make it a UNION because that might remove identical rows that *should* appear more than once in the result. regards, tom lane
Re: bug or lacking doc hint
On Sun, Jun 25, 2023 at 11:48 PM Tom Lane wrote: > David Rowley writes: > > The problem is that out of the 3 methods PostgreSQL uses to join > > tables, only 1 of them supports join conditions with an OR clause. > > Merge Join cannot do this because results can only be ordered one way > > at a time. Hash Join technically could do this, but it would require > > that it built multiple hash tables. Currently, it only builds one > > table. That leaves Nested Loop as the join method to implement joins > > with OR clauses. Unfortunately, nested loops are quadratic and the > > join condition must be evaluated once per each cartesian product row. > > We can do better than that if the OR'd conditions are each amenable > to an index scan on one of the tables: then it can be a nestloop with > a bitmap-OR'd inner index scan. I thought the upthread advice to > convert the substr() condition into something that could be indexed > was on-point. > ok. but one of the tables within the join(s) tables is 10 billions rows, splitted in 120 partitions. Creating something like 20 more indexes to fulfill that condition do have its own problems. > > > Tom Lane did start some work [1] to allow the planner to convert some > > queries to use UNION instead of evaluating OR clauses, but, if I > > remember correctly, it didn't handle ORs in join conditions, though > > perhaps having it do that would be a natural phase 2. I don't recall > > why the work stopped. > > As I recall, I was having difficulty convincing myself that > de-duplication of results (for cases where the same row satisfies > more than one of the OR'd conditions) would work correctly. > You can't just blindly make it a UNION because that might remove > identical rows that *should* appear more than once in the result. > I did rewrite the query using a cte and union(s). For that query, no dedup point. But my pb is that that DB will be used by a bunch of people writing raw SQL queries, and I cannot let them write queries that are going to go on for ages, and eventually crash over temp_file_limit after hours every now and then. So, my understanding of the above is that I must inform the users NOT to use OR clauses into joins. which maybe a pb by itself. regards Marc > regards, tom lane > Marc MILLAS
Re: bug or lacking doc hint
Sounds like the problem you are having is, the server is running out of temporary resources for the operation that users are trying to do. So according to Tom, on the postgres side, the operation cannot be optimized further. I think you have few choices here, - See if increasing the resources of the server will allow them to run the operation - Ask users not to do that operation - Use a extension like citus to scale horizontally But I'm thinking why a massively inefficient join is needed in the first place. Shouldn't joins be for following keyed relationships. So ideally a unique indexed column, but at the very least an indexed column. Why is a join required on a dynamically calculated substring? Can it be made into a static computed value and indexed? Substring sounds like an op that should be in the filter stage. Can you describe your data model? Maybe we can give some specific advice. Regards, Avin On Mon, Jun 26, 2023 at 3:57 AM Marc Millas wrote: > > On Sun, Jun 25, 2023 at 11:48 PM Tom Lane wrote: > >> David Rowley writes: >> > The problem is that out of the 3 methods PostgreSQL uses to join >> > tables, only 1 of them supports join conditions with an OR clause. >> > Merge Join cannot do this because results can only be ordered one way >> > at a time. Hash Join technically could do this, but it would require >> > that it built multiple hash tables. Currently, it only builds one >> > table. That leaves Nested Loop as the join method to implement joins >> > with OR clauses. Unfortunately, nested loops are quadratic and the >> > join condition must be evaluated once per each cartesian product row. >> >> We can do better than that if the OR'd conditions are each amenable >> to an index scan on one of the tables: then it can be a nestloop with >> a bitmap-OR'd inner index scan. I thought the upthread advice to >> convert the substr() condition into something that could be indexed >> was on-point. >> > ok. but one of the tables within the join(s) tables is 10 billions rows, > splitted in 120 partitions. Creating something like 20 more indexes to > fulfill that condition do have its own problems. > >> >> > Tom Lane did start some work [1] to allow the planner to convert some >> > queries to use UNION instead of evaluating OR clauses, but, if I >> > remember correctly, it didn't handle ORs in join conditions, though >> > perhaps having it do that would be a natural phase 2. I don't recall >> > why the work stopped. >> >> As I recall, I was having difficulty convincing myself that >> de-duplication of results (for cases where the same row satisfies >> more than one of the OR'd conditions) would work correctly. >> You can't just blindly make it a UNION because that might remove >> identical rows that *should* appear more than once in the result. >> > > I did rewrite the query using a cte and union(s). For that query, no dedup > point. > But my pb is that that DB will be used by a bunch of people writing raw > SQL queries, and I cannot let them write queries that are going to go on > for ages, and eventually crash over temp_file_limit after hours every now > and then. > So, my understanding of the above is that I must inform the users NOT to > use OR clauses into joins. > which maybe a pb by itself. > regards > Marc > > >> regards, tom lane >> > > Marc MILLAS >
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
How to show current schema of running queries in postgresql 13
Hello,I use multi-schemas in one database in Postgres,such as ``` Postgres(instance) MyDB public MySchema1 table1 table2 MySchema2 table1 table2 MySchema3 table1 table2 ``` And It's open to my users,my users will run queries, such as User1: ``` set search_path=MySchema1; select * from table1,table2; ``` User2: ``` set search_path=MySchema2; select * from table1,table2; ``` User3: ``` set search_path=MySchema3; insert into table3 select * from MySchema1.table1,MySchema2.table2; select * from table3; ``` I want to show current schema of running queries,But pg_stat_activity can only show database name, not schema name. I want current schema (search_path ) as | datname | username | schema | query | | | | | | | MyDB | User1| MySchema1 | select * from table1,table2; | | MyDB | User2| MySchema2 | select * from table1,table2; | | MyDB | User3| MySchema3 | insert into table3 select * from MySchema1.table1,MySchema2.table2; | Is there any sys views can do it? Thank you! Best regards, CavonChen
