Planner misestimation for JOIN with VARCHAR
Helloes, I do have a set of tables which contain user data and users can choose to have columns as constrained VARCHAR, limit is typically 100. While users can also choose from different types, quite often they go the VARCHAR route. Furthermore, they can pick PKs almost freely. As a result, I quite often see tables with the following DDL: CREATE TABLE example_1( a VARCHAR(100) , b VARCHAR(100) , c VARCHAR(100) , payload TEXT ); ALTER TABLE example_1 ADD PRIMARY KEY (a, b, c); Due to processing, these need to be joined together sometimes considering the complete PK. For instance, assume example_1 and example_2 having the same structure as above. Then, when I do SELECT * FROM example_1 t1 INNER JOIN example_2 t2 ON( t1.a = t2.a AND t1.b = t2.b AND t1.c = t2.c ); the planner will very likely estimate a single resulting row for this operation. For instance: Gather (cost=1510826.53..3100992.19 rows=1 width=138) Workers Planned: 13 -> Parallel Hash Join (cost=1510726.53..3100892.04 rows=1 width=138) Hash Cond: (((t1.a)::text = (t2.a)::text) AND ((t1.b)::text = (t2.b)::text) AND ((t1.c)::text = (t1.c)::text)) -> Parallel Seq Scan on example_1 t1 (cost=0.00..1351848.61 rows=7061241 width=69) -> Parallel Hash (cost=1351848.61..1351848.61 rows=7061241 width=69) -> Parallel Seq Scan on example_2 t2 (cost=0.00..1351848.61 rows=7061241 width=69) This does not create a problem when joining just two tables on their own. However, with a more complex query, there will be more than one single-row estimates. Hence, I typically see a nested loop which takes very long to process eventually. This runs on PG 12, and I ensured that the tables are analyzed, my default_statistics_target is 2500. However, it seems, that with more VARCHARs being in the JOIN, the estimates becomes worse. Given the table definition as above, I wonder whether I have overlooked anything in terms of settings or additional indices which could help here. Things tried so far without any noticeable change: - Add an index on top of the whole PK - Add indexes onto other columns trying to help the JOIN - Add additional statistics on two related columns Another idea I had was to make use of generated columns and hash the PKs together to an BIGINT and solely use this for the JOIN. However, this would not work when not all columns of the PK are used for the JOIN. Thanks, Sebastian -- Sebastian Dressler, Solution Architect +49 30 994 0496 72 | sebast...@swarm64.com Swarm64 AS Parkveien 41 B | 0258 Oslo | Norway Registered at Brønnøysundregistrene in Norway under Org.-Number 911 662 787 CEO/Geschäftsführer (Daglig Leder): Thomas Richter; Chairman/Vorsitzender (Styrets Leder): Dr. Sverre Munck Swarm64 AS Zweigstelle Hive Ullsteinstr. 120 | 12109 Berlin | Germany Registered at Amtsgericht Charlottenburg - HRB 154382 B
Re: Planner misestimation for JOIN with VARCHAR
> On 9. Jun 2020, at 21:30, Michael Lewis wrote: > >> On Tue, Jun 9, 2020 at 12:34 PM Sebastian Dressler >> wrote: >> - Add an index on top of the whole PK >> - Add indexes onto other columns trying to help the JOIN >> - Add additional statistics on two related columns >> >> Another idea I had was to make use of generated columns and hash the PKs >> together to an BIGINT and solely use this for the JOIN. However, this would >> not work when not all columns of the PK are used for the JOIN. > > Can you expand on the additional statistics you created? Why was it on only > two columns? Did you include MCVs type of extended stats? Sure, for the MCVs specifically I tried to select which columns belong together to also have value for the end-user when running a query. Basically in accordance what the docs suggest, i.e. [1] section 14.2.2.3 last paragraph. Given the nature of the data I however think this can be very difficult to do without requiring further user input. Likewise, as the others suggested, it did not help for this particular case. Cheers, Sebastian [1]: https://www.postgresql.org/docs/current/planner-stats.html#PLANNER-STATS-EXTENDED -- Sebastian Dressler, Solution Architect +49 30 994 0496 72 | sebast...@swarm64.com Swarm64 AS Parkveien 41 B | 0258 Oslo | Norway Registered at Brønnøysundregistrene in Norway under Org.-Number 911 662 787 CEO/Geschäftsführer (Daglig Leder): Thomas Richter; Chairman/Vorsitzender (Styrets Leder): Dr. Sverre Munck Swarm64 AS Zweigstelle Hive Ullsteinstr. 120 | 12109 Berlin | Germany Registered at Amtsgericht Charlottenburg - HRB 154382 B
Re: CPU Configuration - postgres
Hi, > On 11. Jun 2020, at 10:24, Sujith Kumar.S wrote: > > Is there any configuration in postgresql that restrict the number of CPU > usage by postgres process. For parallelism, there are these options: - max_parallel_workers_per_gather - max_parallel_workers - max_worker_processes Where the first two essentially can limit the amount of cores to be used. Is that what you were asking for? Cheers, Sebastian -- Sebastian Dressler, Solution Architect +49 30 994 0496 72 | sebast...@swarm64.com Swarm64 AS Parkveien 41 B | 0258 Oslo | Norway Registered at Brønnøysundregistrene in Norway under Org.-Number 911 662 787 CEO/Geschäftsführer (Daglig Leder): Thomas Richter; Chairman/Vorsitzender (Styrets Leder): Dr. Sverre Munck Swarm64 AS Zweigstelle Hive Ullsteinstr. 120 | 12109 Berlin | Germany Registered at Amtsgericht Charlottenburg - HRB 154382 B
Re: Foreign table performance issue / PostgreSQK vs. ORACLE
Hi Ingolf, > On 29. Jan 2021, at 13:56, Markhof, Ingolf > wrote: > > Hi! > > I am struggling with the slow performance when running queries referring to > foreign tables. – Yes, I know… - Please read the whole story! Done and it rings a bell or two. > The set-up basically is a production database and a reporting database. As > names indicate, the production database is used for production, the reporting > database is for analysis. On the reporting database, the only way to access > product data is via foreign tables that link to the related production tables. > > Now, while some queries on the reporting service run fine, some don't even > return any data after hours. > > However, the same set-up worked fine in Oracle before. Reporting wasn't > always fast, but it delivered results in acceptable time. A query executed on > the Oracle reporting server returns data in e.g. 30 seconds. But running the > query translated to PostgreSQL on the PostgreSQL DB does not deliver a single > row after hours (!) of run time. > > So, I wonder: Is there a fundamental difference between Oracle database links > and foreign tables in PostgreSQL that could explain the different run times? > Could there be some tuning option in PostgreSQL to make queries via foreign > tables faster (e.g. I heard about option fetch_size)? You did not explicitly mention it, but I assume you are using postgres_fdw to connect from reporting (R) to production (P). Thomas and Tom already mentioned incomplete/non-existing/non-applicable filter pushdowns. I want to add another probable root cause to the list explaining the behavior you experience. The postgres_fdw uses a CURSOR on P to execute the query. While this guarantees transaction safety, it also prohibits parallelism (PostgreSQL server-side cursors enforce a sequential plan). As a result, depending on the size of tables, indexes, and filters pushed down (or not), this probably results in slow-running queries. IMO, the worst-case scenario is that a sequential table scan without any filtering, and a single worker runs on the target. Of course, you can try to optimize schemas on P and queries on R, enabling more filter pushdown and eventually a faster execution. However, I believe this does not work with your entire workload, i.e. there will always be performance gaps. The parallelism issue is theoretically fixable by utilizing partitions on P. R then connects to P with multiple postgres_fdw-backed child tables. However, this will only work with a patch to postgres_fdw to implement "IsForeignScanParallelSafe" (see [1] for a possible implementation). Without this method, there will be no parallelism again. Without, the partitions scan occurs sequentially, not showing a performance gain. I want to mention there are proprietary options available (re-)enabling PostgreSQL parallelism with cursors. Such an extension can potentially fix your performance issue. However, I have not tried it so far with a setup similar to yours. Cheers, Sebastian [1]: https://github.com/swarm64/parallel-postgres-fdw-patch -- Sebastian Dressler, Solution Architect, Swarm64 AS +49 30 994 0496 72 | sebast...@swarm64.com
Re: Foreign table performance issue / PostgreSQK vs. ORACLE
Hi Ingolf, On 2. Feb 2021, at 13:05, Markhof, Ingolf mailto:ingolf.mark...@de.verizon.com>> wrote: Hi! My PostgreSQL version is 11.8. The query I am running is referring to a number of foreign tables. The first one (table1) has to IDs, let's say ID_A and ID_B. While ID_A is unique, ID_B is not. In my case, I am pulling formation for a value of IB_B for which about 800 rows (with unique ID_A) exist. I found: While select * from my_view where id_b='some value'; seemingly runs "forever" (I cancelled execution after a few hours), the following completes in about 1 hr: select * from my_view where ia_a in ( select id_a from table1 where id_b='some value' ); So, I tried smaller chunks of ID_a and found the execution time is non-linear with respect to number of IDs. For e.g. 50 ID_A's, it was completed in about 12 sec. [...] I then found the option fetch_size, e.g. ALTER SERVER some_server OPTIONS (fetch_size '5'). A chunk of 50 now executes in 2 seconds (instead of 12 before). Thanks for the additional info. I tried to replicate this, you can find the GitHub Gist at [1], happy to hear your feedback about it. What I can see from the execution plans in my example is, that the postgres_fdw pushes down that part Remote SQL: SELECT id_a FROM public.a WHERE ((id_b = 1)) part to the remote. On the remote DB this query can result in either an index-only scan, an index scan or a full table scan. Which method is chosen depends on table size and indexes. Given the nature of postgres_fdw this will be done in any case with a sequential query. For this part I would claim: - If the planner expects few rows, it will choose an index-lookup which is a good thing because it effectively reduces the amount of data that needs to be queried. This would make it fast, given this is a sequential scan. - If the planner expects many rows, it might choose a sequential scan which can be slow depending on the overall size of the table and likely whether it is cached or not. So, I found the "size" of the query has a serious impact to the execution time. I don't really understand why execution 16*50 takes 16*2 secs only, but executing 1*800 takes about 3000 seconds... The mentioned fetch_size parameter has a positive effect, because one can grab many more rows and return them at the same time. Worst case (and this is just pure assumption), on each new fetch, the query might be re-executed and thus runtime becomes much more. Further up in the plan, I see Remote SQL: SELECT id_a, id_b FROM public.a which is the "SELECT * FROM my_view" part. Meaning, here it will definitely do a full table scan on remote since it cannot push down the IN condition. I don't really see right now why this query at all is slower than your original form. In my experiment it is not, but maybe I am doing something wrong in the schema. One thought would be however, that the full table scan on the remote is more efficient than pushing down the filter and thus it returns faster. To really figure out more, I would suggest to increase the logging level on your remote server in order to see which queries are really executed. Even better to maybe use auto_explain to fetch plans and see whether these claims apply. Best, Sebastian -- [1]: https://gist.github.com/sdressler/9a93d66b7052dc75ec45c0a4bf5c61de Sebastian Dressler, Solution Architect, Swarm64 +49 30 994 0496 72 | sebast...@swarm64.com<mailto:sebast...@swarm64.com>
