15x slower PreparedStatement vs raw query

PreparedStatement: 15s Raw query with embedded params: 1s See issue on github with query and explain analyze: https://github.com/pgjdbc/pgjdbc/issues/2145

Re: 15x slower PreparedStatement vs raw query

slow, more planning can save huge execution time. This is a better general usage option and should be enabled by default, and users who want fast planning should set the variable to use the generic plan. Justin Pryzby wrote: On Sun, May 02, 2021 at 07:45:26PM +0000, Alex wrote: PreparedStatement: 15

Re: 15x slower PreparedStatement vs raw query

4, 2021 at 6:05 AM Alex mailto:cdalx...@yahoo.com> > wrote: Shouldn't this process be automatic based on some heuristics? Saving 10ms planning but costing 14s execution is catastrophic. For example, using some statistics to limit planner time to some percent of of previous executions.

Re: 15x slower PreparedStatement vs raw query

changed to include execution time in the decision. On Wednesday, May 5, 2021, 9:57:20 AM GMT+3, David Rowley wrote: On Tue, 4 May 2021 at 22:05, Alex wrote: > Shouldn't this process be automatic based on some heuristics? When plan_cache_mode is set to "auto", then the

Slow query because lexeme index not used

Table "product" has a GIN index on "lexeme" column (tsvector) that is not used. Query that doesn't use lexeme idx:  https://explain.dalibo.com/plan/BlB#plan, ~8s, ~60.000 blocks needed Query forced to use lexeme idx: https://explain.dalibo.com/plan/i52, ~800ms (10x less), ~15.000 blocks needed

Re: Slow query because lexeme index not used

the planner estimation (nested loop 69x to 12x), but the same ineffective plan is issued, without lexeme_idx:https://explain.dalibo.com/plan/B7d#plan (has query with stats) On Sunday, August 8, 2021, 3:35:31 AM GMT+3, Justin Pryzby wrote: On Sat, Aug 07, 2021 at 07:35:25PM +000

RE: Query became very slow after 9.6 -> 10 upgrade

Hello! What about : select name,setting from pg_settings where name like '%_cost'; -- Alex Ignatov Postgres Professional: <http://www.postgrespro.com> http://www.postgrespro.com The Russian Postgres Company From: Dmitry Shalashov [mailto:skau...@gmail.com]

RE: Query became very slow after 9.6 -> 10 upgrade

setup_cost | 1000 parallel_tuple_cost | 0.1 random_page_cost | 4 seq_page_cost| 1 Can you generate plan with random_page_cost = 4? -- Alex Ignatov Postgres Professional: <http://www.postgrespro.com> http://www.postgrespro.com The Russian Postgres Company

RE: Bad estimates

It doesn’t help in this case. -- Alex Ignatov Postgres Professional: <http://www.postgrespro.com> http://www.postgrespro.com The Russian Postgres Company From: Don Seiler [mailto:d...@seiler.us] Sent: Wednesday, November 22, 2017 5:49 PM To: Artur Zając Cc: pgsql-perfo

Bad plan chosen for union all

nning in Heroku. What are my options here? Currently, I'm planning to avoid these bad plans by using a less straightforward query for the view: SELECT coalesce(contrib.id, cm.contribution_id) AS contribution_id, coalesce(cm.yield, im.yield) AS yield, coalesce(cm.term, im.term) AS term FROM contribut

Re: Bad plan chosen for union all

=17 loops=1) Planning time: 0.396 ms Execution time: 0.165 ms schema here: https://gist.github.com/awreece/aeacbc818277c7c6d99477645e7fcd03 Best, ~Alex On Tue, Nov 28, 2017 at 2:13 AM Alex Reece wrote: > I'm on PostgreSQL 9.6.5 and getting an awkwardly bad plan c

Re: Bad plan chosen for union all

ion_id = contributions.id ) cim ON true WHERE earnings.id = '\x595400456c1f1400116b3843' Is there a way I can get the restriction to be pushed down into my subquery in this lateral form? Best, ~Alex

Different plan chosen when in lateral subquery

ies_id, allocated_on DESC) Indexes: "contributions_pkey" PRIMARY KEY, btree (id) "contributions_id_accrue_from_idx" btree (id, events_earnings_accrue_from) I have a few questions here: - Why doesn't it use the primary key index in either case? - Why isn't it choosing portfolio_allocations_pnsa, which seems like it would prevent it from having to sort? Best, ~Alex

Re: Different plan chosen when in lateral subquery

contrib.investment_id) Heap Fetches: 1792457 Planning time: 0.721 ms Execution time: 7236.507 ms On Tue, Dec 5, 2017 at 10:04 AM Alex Reece wrote: > I get very different plan chosen when my query is in a lateral subquery vs > standalone -- it doesn't us

Re: Different plan chosen when in lateral subquery

ould prevent it from having to sort? - What information can I gather to answer these questions on my own? ~Alex On Tue, Dec 5, 2017 at 10:08 AM Alex Reece wrote: > Weird, when I deleted an erroneous index it started picking a reasonable > plan. This now works as expected, for posterity here

Table with large number of int columns, very slow COPY FROM

result of profiling session with perf: https://pastebin.com/pjv7JqxD -- Regards, Alex.

RE: pg_xlog unbounded growth

Have you tried archive_command='/bin/true' as Andreas wrote? -Original Message- From: Stefan Petrea [mailto:stefan.pet...@tangoe.com] Sent: Wednesday, January 24, 2018 2:48 PM To: pgsql-performa...@postgresql.org Subject: pg_xlog unbounded growth Hello, This email is structured in sec

Getting an index scan to be a parallel index scan

can't be parallelized? Or is there some other way I could structure the query to get it to use parallelism? I've tried this both on PG 15.1 (In docker, which seems to be on Debian 15.1) and PG 14.5 (on Centos 7) an

Re: Getting an index scan to be a parallel index scan

,4654284,3558460]::integer[]); QUERY PLAN Index Scan using testing_pkey on testing (cost=0.43..6138.81 rows=1000 width=74) Index Cond: (id = ANY ('{1608377,5449811, ... < removed for brevity > ... 4654284,3558460}'::integer[])) (2 rows) Thanks

Re: Getting an index scan to be a parallel index scan

testing_pkey on testing (cost=0.43..8.37 rows=1 width=74) Index Cond: (id = ids.probe_id) (6 rows) Thanks, Alex Kaiser On Wed, Feb 1, 2023 at 1:52 PM Thomas Munro wrote: > On Wed, Feb 1, 2023 at 6:39 PM Alex Kaiser wrote: > > select * from testing where id in (1608377,5449811, .

Re: Getting an index scan to be a parallel index scan

27;t really care about the few extra wasted CPU cycles. But I can't actually do this as I can't set parallel_setup_costs to be negative, so I wouldn't be able to get PG to choose the parallel plan even if I did partition the table. > If I had more timerons myself ... If only we a