Re: Slow query in table where many rows were deleted. VACUUM FULL fixes it

Hi Pavlos This is my understanding of why you were not able to run the query fast enough after the vacuum analyze. This is possibly what would have happened: 1. The relation has 5 million expired URLs and 5 thousand non-expired URLs 2. Assuming that the table only has 5 million and 5 th

Re: Slow query in table where many rows were deleted. VACUUM FULL fixes it

On Wed, 31 Jan 2024 at 09:09, Philip Semanchuk wrote: > So in your case those 5m rows that you deleted were probably still clogging > up your table until you ran VACUUM FULL. It seems more likely to me that the VACUUM removed the rows and just left empty pages in the table. Since there's no ind

Re: Slow query in table where many rows were deleted. VACUUM FULL fixes it

> On Jan 30, 2024, at 4:40 AM, Pavlos Kallis wrote: > > Shouldn't VACUUM ANALYZE reclaim the disk space? Hi Pavlos, The short answer to this is “no”. That’s an important difference between VACUUM (also known as “plain” VACUUM) and VACUUM FULL. In some special cases plain VACUUM can reclaim

Re: Slow query in table where many rows were deleted. VACUUM FULL fixes it

On Tue, 2024-01-30 at 11:40 +0200, Pavlos Kallis wrote: > I have the following table: > > CREATE TABLE IF NOT EXISTS public.shortened_url > ( >     id character varying(12) COLLATE pg_catalog."default" NOT NULL, >     created_at timestamp without time zone, >     expires_at timestamp without time

Re: Slow query, possibly not using index

> > > > > More important question is, how can I find out why the index was not > auto vacuumed. > > You should have a look at pg_stat_user_tables. It'll let you know if > the table is being autovacuumed and how often. If you're concerned > about autovacuum not running properly, then you might wan

Re: Slow query, possibly not using index

On Mon, 28 Aug 2023 at 19:21, Les wrote: > More important question is, how can I find out why the index was not auto > vacuumed. You should have a look at pg_stat_user_tables. It'll let you know if the table is being autovacuumed and how often. If you're concerned about autovacuum not running

Re: Slow query, possibly not using index

po 28. 8. 2023 v 13:00 odesílatel Les napsal: > > >> >> =# select * from pgstatindex('media.idx_block_unused'); >> version | tree_level | index_size | root_block_no | internal_pages | >> leaf_pages | empty_pages | deleted_pages | avg_leaf_density | >> leaf_fragmentation >> >> -+-

Re: Slow query, possibly not using index

> > > =# select * from pgstatindex('media.idx_block_unused'); > version | tree_level | index_size | root_block_no | internal_pages | > leaf_pages | empty_pages | deleted_pages | avg_leaf_density | > leaf_fragmentation > > -+++---++---

Re: Slow query, possibly not using index

> >> > All right, I started pgstattuple() and I'll also do pgstatindex(), but it > takes a while. I'll get back with the results. > =# select * from pgstattuple('media.block'); table_len | tuple_count | tuple_len | tuple_percent | dead_tuple_count | dead_tuple_len | dead_tuple_percent | f

Re: Slow query, possibly not using index

> > > > > I'm aware of the problems with random UUID values. I was using this > > function to create ulids from the beginning: > > Oh, well that would have been useful information to provide at the > outset. I'm sorry, I left this out. > Now that we know the index order is correlated with creatio

Re: Slow query, possibly not using index

Les writes: >>> If I try to select a single unused block this way: >>> explain analyze select id from media.block b where nrefs =0 limit 1 >>> then it runs for more than 10 minutes (I'm not sure how long, I cancelled >>> the query after 10 minutes). >> You might think that even so, it shouldn't t

Re: Slow query, possibly not using index

> Nobody ever deleted anything from this table. Since it was created, this has been a write-only table. does write-only include updates? that would create the dead rows tom is referring to. > I believe it is not actually using the index, because reading a single (random?) entry from an index shou

Re: Slow query, possibly not using index

> > If I try to select a single unused block this way: > > explain analyze select id from media.block b where nrefs =0 limit 1 > > then it runs for more than 10 minutes (I'm not sure how long, I cancelled > > the query after 10 minutes). > > Are you sure it isn't blocked on a lock? > Yes, I'm sure

Re: Slow query, possibly not using index

Les writes: > If I try to select a single unused block this way: > explain analyze select id from media.block b where nrefs =0 limit 1 > then it runs for more than 10 minutes (I'm not sure how long, I cancelled > the query after 10 minutes). Are you sure it isn't blocked on a lock? Another theor

Re: slow query to improve performace

On Fri, Feb 25, 2022 at 3:18 PM Ayub Khan wrote: > Hi, > > Could some some verify the attached query to verify the performance and > suggest some steps to improve it, this query is created as a view. This > view is used to get the aggregates of orders based on its current status > I don't see ho

Re: slow query to improve performace

Please provide some more information, like your postgres version and settings. Some relevant things are included here. https://wiki.postgresql.org/wiki/Slow_Query_Questions -- Justin

Re: Slow query fixed by replacing equality with a nested query

On Mon, Jan 24, 2022 at 6:22 AM Valentin Janeiko wrote: > I have rewritten the query using JOINs. I had to make one of them a > FULL JOIN, but otherwise JOINs seem like a good idea. > I have added the new query to the (same) gist: > > https://gist.github.com/valeneiko/89f8cbe26db7ca2651b47524462b

Re: Slow query fixed by replacing equality with a nested query

On Fri, Jan 21, 2022 at 4:37 AM wrote: > I have done a few simple experiments in the past comparing CTEs like this > to JOINS, but the resultant query plans were the same. CTEs seemed easier > to follow when troubleshooting issues, so I left them as such. Do JOINs > become better than CTEs at a c

Re: Slow query fixed by replacing equality with a nested query

I have rewritten the query using JOINs. I had to make one of them a FULL JOIN, but otherwise JOINs seem like a good idea. I have added the new query to the (same) gist: https://gist.github.com/valeneiko/89f8cbe26db7ca2651b47524462b5d18#file-queryoptimized-sql The query plan is much better with just

RE: Slow query fixed by replacing equality with a nested query

My mistake. I have updated the query in the gist: cte1 should have been referenced in cte2. The query plans are correct. It was just the query in the gist that was incorrect (I was just verifying cte1 was the culprit – without it the query is fast too). This SQL query is a result of transl

Re: Slow query fixed by replacing equality with a nested query

I don't see any reference to cte1. Is that expected? I'm unclear why these sets are not just inner join'd on resource_surrogate_id. It seems like that column it is being selected as Sid1 in each CTE, and then the next one does the below. Why? where resource_surrogate_id IN (SELECT Sid1 FROM cte_p

Re: Slow query because lexeme index not used

> Could you show the table stats for product.id ?  In particular its "correlation".  frac_mcv | tablename | attname | inherited | null_frac | n_distinct | n_mcv | n_hist | correlation --+---+-+---+---++---++-   |

Re: Slow query because lexeme index not used

On Sat, Aug 07, 2021 at 07:35:25PM +, Alex wrote: > 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://ex

Re: slow query

Below is the test setup Jmeter-->(load balanced tomcat on ec2 instances)>rds read replicas All these are running on different ec2 instances in AWS cloud in the same region On Tue, 8 Jun 2021, 19:03 Ayub Khan, wrote: > > I checked all the indexes are defined on the tables however the query

Re: slow query

On Tue, Jun 8, 2021 at 12:32 PM Ayub Khan wrote: > In AWS RDS performance insights the client writes is high and the api > which receives data on the mobile side is slow during load test. > That indicates a client or network problem. Jeff

Re: slow query

below is function definition of is_menu_item_available, for each item based on current day time it returns when it's available or not. The same api works fine on oracle, I am seeing this slowness after migrating the queries to postgresql RDS on AWS CREATE OR REPLACE FUNCTION is_menu_item_availab

Re: slow query

Ayub Khan writes: > I checked all the indexes are defined on the tables however the query seems > slow, below is the plan. Can any one give any pointers to verify ? You might try to do something about the poor selectivity estimate here: > -> Index Scan usin

Re: slow query

In AWS RDS performance insights the client writes is high and the api which receives data on the mobile side is slow during load test. On Tue, 8 Jun 2021, 19:03 Ayub Khan, wrote: > > I checked all the indexes are defined on the tables however the query > seems slow, below is the plan. Can any o

Re: slow query

> On Jun 8, 2021, at 09:03, Ayub Khan wrote: > I checked all the indexes are defined on the tables however the query seems > slow, below is the plan. It's currently running in slightly under six milliseconds. That seems reasonably fast given the number of operations required to fulfill it.

Re: slow query with inline function on AWS RDS with RDS 24x large

You are right, I dropped BRIN and created btree and the performance on 0 rows matching criteria table is good, below is the plan with BTREE. I will test by inserting lot of data. Hash Join (cost=50186.91..3765911.10 rows=5397411 width=291) (actual time=1.501..1.504 rows=0 loops=1) Hash Cond: (

Re: slow query with inline function on AWS RDS with RDS 24x large

Hi pá 4. 6. 2021 v 10:32 odesílatel Ayub Khan napsal: > BRIN index is only on the date_time column, I even tried with btree index > with no performance gains. > -> Bitmap Heap Scan on restaurant_order ro (cost=5427.94..3353966.60 rows=19275986 width=108) (actual time=1036.793..1

Re: slow query with inline function on AWS RDS with RDS 24x large

BRIN index is only on the date_time column, I even tried with btree index with no performance gains. On Fri, Jun 4, 2021 at 11:23 AM Pavel Stehule wrote: > > > pá 4. 6. 2021 v 10:07 odesílatel Ayub Khan napsal: > >> >> below query is slow even with no data >> >> >> explain ANALYZE >> >> WITH bu

Re: slow query with inline function on AWS RDS with RDS 24x large

pá 4. 6. 2021 v 10:07 odesílatel Ayub Khan napsal: > > below query is slow even with no data > > > explain ANALYZE > > WITH business AS( SELECT * FROM get_businessday_utc_f() start_date) > SELECT ro.order_id, > ro.date_time, > round(ro.order_amount, 2) AS order_amount, > b.branch_id

Re: Slow query performance inside a transaction on a clean database

On Fri, 2021-03-05 at 17:55 +, val.jane...@gmail.com wrote: > I have a SELECT query that uses a long chain of CTEs (6) and is executed > repeatedly as part of the transaction (with different parameters). It is > executed quickly most of the time, but sometimes becomes very slow. I > managed to

Re: Slow query and wrong row estimates for CTE

*You are totally right, the max(score_value) FILTER (WHERE score_name = 'student_performance_index') in the SELECT clause is redundant.* Le mer. 17 févr. 2021 à 21:33, Dane Foster a écrit : > On Wed, Feb 17, 2021 at 1:37 PM Yoan SULTAN wrote: > >> *Hi all, * >> >> *This is my first post on th

Re: Slow query and wrong row estimates for CTE

On Wed, Feb 17, 2021 at 1:37 PM Yoan SULTAN wrote: > *Hi all, * > > *This is my first post on this mailing list, I really enjoy it.* > *I wanted to add some details and answers to this disccusion.* > I'm happy you've decided to join the conversation and about the fact that you've opened up an ent

Re: Slow query and wrong row estimates for CTE

*Hi all, * *This is my first post on this mailing list, I really enjoy it.* *I wanted to add some details and answers to this disccusion.* 17 févr. 2021 à 17:52, Dane Foster a écrit : > > A small update (see below/inline). > > > On Tue, Feb 16, 2021 at 2:11 PM Dane Foster wrote: > >> Short co

Re: Slow query and wrong row estimates for CTE

A small update (see below/inline). On Tue, Feb 16, 2021 at 2:11 PM Dane Foster wrote: > Short conclusion: > Switching from CTEs to temporary tables and analyzing reduced the runtime > from 15 minutes to about 1.5 minutes. > > > Longer conclusion: > > @Justin Pryzby > >- I experimented w/ m

Re: Slow query and wrong row estimates for CTE

Short conclusion: Switching from CTEs to temporary tables and analyzing reduced the runtime from 15 minutes to about 1.5 minutes. Longer conclusion: @Justin Pryzby - I experimented w/ materializing the CTEs and it helped at the margins but did not significantly contribute to a reduction

Re: Slow query and wrong row estimates for CTE

On Tue, Feb 16, 2021 at 10:13 AM Michael Lewis wrote: >Sort Method: external >> merge Disk: 30760kB >>Worker 0: Sort >> Method: external merge Disk: 30760kB >>

Re: Slow query and wrong row estimates for CTE

On Mon, Feb 15, 2021 at 5:32 PM Justin Pryzby wrote: > ... > > Without looking closely, an index might help: student_id,assignment_id > That'd avoid the sort, and maybe change the shape of the whole plan. > I tried that prior to posting on the forum and it didn't make a difference. 🙁 I'll try yo

Re: Slow query and wrong row estimates for CTE

> >Sort Method: external > merge Disk: 30760kB >Worker 0: Sort Method: > external merge Disk: 30760kB >Worker 1: Sort Method: > external me

Re: Slow query and wrong row estimates for CTE

On Mon, Feb 15, 2021 at 12:49:29PM -0500, Dane Foster wrote: > PostgreSQL version: PostgreSQL 12.5 on x86_64-pc-linux-gnu, compiled by gcc > (GCC) 4.8.5 20150623 (Red Hat 4.8.5-11), 64-bit > EXPLAIN (ANALYZE, BUFFERS) > WITH max_spi AS ( Since v12, CTEs are usually inlined by default. I suspect i

Re: Slow Query

Hi all, Thanks, Michael (and Martin other thread)! We added those indexes you suggested, and went ahead and added indexes for all our foreign keys. We also added one combination index on notification (user, time). It led to a small constant factor speed up (2x) but is still taking a 13+ seconds. :

Re: Slow Query

Based on the execution plan, it looks like the part that takes 13 seconds of the total 14.4 seconds is just calculating the max time used in the where clause. Anytime I see an OR involved in a plan gone off the rails, I always always check if re-writing the query some other way may be faster. How's

Re: Slow Query

Is there no index on thread.spool? What about notification.user? How about message.time (without thread as a leading column). Those would all seem very significant. Your row counts are very low to have a query perform so badly. Work_mem could probably be increased above 4MB, but it isn't hurting th

Re: slow query

That plan looks like it might have been cropped in places, and the formatting is making it tricky to help. Could you try again, pasting the plan into https://explain.depesz.com/ to make it easier to review? On Fri, Apr 3, 2020 at 5:18 PM dangal wrote: > Justin thank you very much for your answe

Re: slow query

Justin thank you very much for your answer, as you can also see the number of rows differs a lot I attach the complete explain, do not attach it because it is large "HashAggregate (cost=12640757.46..12713163.46 rows=385 width=720) (actual time=1971962.023..1971962.155 rows=306 loops=1)" " Output

Re: slow query

On Fri, Apr 03, 2020 at 09:03:49AM -0700, dangal wrote: > Dear I have a question to ask you > I am having a slow problem with a query and I am seeing with the explain that > the current cost and time differ by 4 times The "cost" is in arbitrary units, and the time is in units of milliseconds. The

Re: Slow query on V12.

Em 23/09/2019 16:44, Tom Lane escreveu: =?UTF-8?Q?Lu=c3=ads_Roberto_Weck?= writes: This is the query that is actually slow: -- EXPLAIN (ANALYZE, COSTS, VERBOSE, BUFFERS) SELECT table_schema, table_name,    n_live_tup::numeric as est_rows,    pg_table_size(relid)::numeric as table_si

Re: Slow query on V12.

=?UTF-8?Q?Lu=c3=ads_Roberto_Weck?= writes: > This is the query that is actually slow: > -- EXPLAIN (ANALYZE, COSTS, VERBOSE, BUFFERS) > SELECT table_schema, table_name, >    n_live_tup::numeric as est_rows, >    pg_table_size(relid)::numeric as table_size >   FROM information_schema.co

Re: Slow query on V12.

Em 23/09/2019 16:03, Luís Roberto Weck escreveu: Em 23/09/2019 15:43, nikhil raj escreveu: Hi, Can you check by vacuum analyze  the database. And run the query. **Remember don't  use Vacuum full. On Tue, 24 Sep 2019, 12:07 am Luís Roberto Weck, mailto:luisrobe...@siscobra.com.br>> wrote:

Re: Slow query on V12.

Em 23/09/2019 15:43, nikhil raj escreveu: Hi, Can you check by vacuum analyze  the database. And run the query. **Remember don't  use Vacuum full. On Tue, 24 Sep 2019, 12:07 am Luís Roberto Weck, mailto:luisrobe...@siscobra.com.br>> wrote: Hi! Recently I've been looking for bloat

Re: Slow query on V12.

Hi, Can you check by vacuum analyze the database. And run the query. **Remember don't use Vacuum full. On Tue, 24 Sep 2019, 12:07 am Luís Roberto Weck, < luisrobe...@siscobra.com.br> wrote: > Hi! > > Recently I've been looking for bloat in my databases and found a query to > show which table

Re: Slow query on a one-tuple table

Hi all, I sometimes set autovacuum_vacuum_scale factor = 0 but only when I also set autovacuum_vacuum_threshold to some non-zero number to force vacuums after a certain number of rows are updated.  It takes the math out of it by setting the threshold explicitly. But in this case he has also

Re: Slow query on a one-tuple table

=?UTF-8?Q?Lu=c3=ads_Roberto_Weck?= writes: > As fas as autovacuum options, this is what I'm using: > autovacuum_vacuum_scale_factor=0, Ugh ... maybe I'm misremembering, but I *think* that has the effect of disabling autovac completely. You don't want zero. Check in pg_stat_all_tables.last_auto

Re: Slow query on a one-tuple table

Em 19/09/2019 19:32, Michael Lewis escreveu: I have about 6 bigint fields in this table that are very frequently updated, but none of these are indexed. I thought that by not having an index on them, would make all updates HOT, therefore not bloating the primary key index. Se

Re: Slow query on a one-tuple table

> > I have about 6 bigint fields in this table that are very frequently > updated, but none of these are indexed. I thought that by not having an > index on them, would make all updates HOT, therefore not bloating the > primary key index. Seems I was wrong? > HOT update is only possible if there i

Re: Slow query on a one-tuple table

Em 19/09/2019 17:41, Luís Roberto Weck escreveu: Em 19/09/2019 17:24, Luís Roberto Weck escreveu: Em 19/09/2019 17:11, Igor Neyman escreveu: With LIMIT 1, I get 3 shared buffers hit, pretty much always. Ch

Re: Slow query on a one-tuple table

Em 19/09/2019 17:24, Luís Roberto Weck escreveu: Em 19/09/2019 17:11, Igor Neyman escreveu: With LIMIT 1, I get 3 shared buffers hit, pretty much always. Check if assessoria_pkey index is bloated. Regards,

Re: Slow query on a one-tuple table

Em 19/09/2019 17:11, Igor Neyman escreveu: With LIMIT 1, I get 3 shared buffers hit, pretty much always. Check if assessoria_pkey index is bloated. Regards, Igor Neyman With this query[1] it shows: curren

RE: Slow query on a one-tuple table

With LIMIT 1, I get 3 shared buffers hit, pretty much always. Check if assessoria_pkey index is bloated. Regards, Igor Neyman

Re: Slow query on a one-tuple table

Em 19/09/2019 15:34, Igor Neyman escreveu: -Original Message- From: Luís Roberto Weck [mailto:luisrobe...@siscobra.com.br] Sent: Thursday, September 19, 2019 2:30 PM To: Michael Lewis Cc: pgsql-performance@lists.postgresql.org Subject: Re: Slow query on a one-tuple table WARNING: This

RE: Slow query on a one-tuple table

-Original Message- From: Luís Roberto Weck [mailto:luisrobe...@siscobra.com.br] Sent: Thursday, September 19, 2019 2:30 PM To: Michael Lewis Cc: pgsql-performance@lists.postgresql.org Subject: Re: Slow query on a one-tuple table WARNING: This email originated from outside of Perceptron

Re: Slow query on a one-tuple table

Em 19/09/2019 14:21, Michael Lewis escreveu: Is this result able to be repeated? Yes, I  can consistently repeat it. Postgres version is 11.1. Other executions: Index Scan using assessoria_pkey on public.assessoria (cost=0.25..2.47 rows=1 width=62) (actual time=1.591..4.035 rows=1 loops=1

Re: Slow query on a one-tuple table

Is this result able to be repeated?

RE: Slow query with aggregate and many LEFT JOINS

From: kimaidou [mailto:kimai...@gmail.com] Sent: Friday, February 22, 2019 10:37 AM To: pgsql-performance@lists.postgresql.org Subject: Slow query with aggregate and many LEFT JOINS Hi all, I need to optimize the following query http://paste.debian.

RE: Slow query with aggregate and many LEFT JOINS

From: kimaidou [mailto:kimai...@gmail.com] Sent: Friday, February 22, 2019 10:37 AM To: pgsql-performance@lists.postgresql.org Subject: Slow query with aggregate and many LEFT JOINS Hi all, I need to optimize the following query http://paste.debian.net/hidden/ef08f864/ I use it to create a mater

Re: Slow query when pg_trgm is in inner lopp

Hi Jeff, the way I see it, is it a poor man's implementation of 'full-text' search. I just discussed it with out navdata team and we might be redefine how do we do the search. Regardless of that, I think that issue with Postgres stands. I tried now to see, how the query would behave if we always

Re: Slow query when pg_trgm is in inner lopp

On Wed, Jun 20, 2018 at 9:21 AM, Sasa Vilic wrote: > Query that we have finds all routes between two set of points. A set is a > dynamically/loosely defined by pattern given by the user input. So for > example > if user wants to find all routes between international airports in Austria > toward

Re: Slow query when pg_trgm is in inner lopp

Hi Matthew, thank you for query response. There is no particular reason for using GIST instead of GIN. We only recently discovered pg_trgm so we are new to this. What I read is that GIN can be faster then GIST but it really depends on query and on amount of data. Nevertheless, both index are by m

Re: Slow query when pg_trgm is in inner lopp

Is there a reason you used GIST on your pg_trgm indices and not GIN? In my tests and previous posts on here, it nearly always performs worse. Also, did you make sure if it's really SSD and set the random_page_cost accordingly? Matthew Hall > On Jun 20, 2018, at 8:21 AM, Sasa Vilic wrote: > >

Re: Slow query on partitioned table.

Re-added -performance. On Tue, Mar 27, 2018 at 05:13:25PM +0100, Glenn Pierce wrote: > Damn as I was playing with the indexes I must have deleted the constraints :( > Question if I have a constraint like > > ALTER TABLE sensor_values_2007q1 > ADD CONSTRAINT sensor_values_2007q1_sensor_id_timest

Re: Slow query on partitioned table.

On Tue, Mar 27, 2018 at 03:14:30PM +0100, Glenn Pierce wrote: > Hi I am having terrible trouble with a simple partitioned table. > Select queries are very slow. > The child tables are all like > Check constraints: > "sensor_values_2018q1_timestamp_check" CHECK (ts >= '2018-01-01 > 00:00:00