Re: SHARED LOCKS , EXCLUSIVE LOCKS, ACCESS EXCLUSIVE LOCKS

On 4/4/21 6:42 AM, aditya desai wrote: > Hi, > We have few select queries during which we see SHARED LOCKS and > EXCLUSIVE LOCKS on tables. Can these locks cause slowness? Is there > any way to reduce the locks? > > What must be causing ACCESS EXCLUSIVE LOCKS when the application is > running sel

Re: select count(*) is slow

On 4/6/21 9:30 AM, aditya desai wrote: > Thanks Tom. Will try with numeric. Please ignore table and index naming. > > On Tue, Apr 6, 2021 at 6:55 PM Tom Lane > wrote: > > aditya desai mailto:admad...@gmail.com>> writes: > > Below query takes 12 seconds. We have

Re: select count(*) is slow

Thanks Tom. Will try with numeric. Please ignore table and index naming. On Tue, Apr 6, 2021 at 6:55 PM Tom Lane wrote: > aditya desai writes: > > Below query takes 12 seconds. We have an index on postcode. > > > select count(*) from table where postcode >= '00420' AND postcode <= > '00500' >

Re: select count(*) is slow

aditya desai writes: > Below query takes 12 seconds. We have an index on postcode. > select count(*) from table where postcode >= '00420' AND postcode <= '00500' That query does not match this index: > CREATE INDEX Table_i1 > ON table USING btree > ((postcode::numeric)); You could ei

Re: Substitute for synonym in Oracle after migration to postgres

Thanks will check. On Tue, Apr 6, 2021 at 4:11 PM hubert depesz lubaczewski wrote: > On Tue, Apr 06, 2021 at 01:22:31PM +0530, aditya desai wrote: > > Hi, > > We have to access data from one schema to another. We have created a > view for this but performance is not good. We tried > > materializ

select count(*) is slow

Hi, Below query takes 12 seconds. We have an index on postcode. select count(*) from table where postcode >= '00420' AND postcode <= '00500' index: CREATE INDEX Table_i1 ON table USING btree ((postcode::numeric)); Table has 180,000 rows and the count is 150,000. Expectation is to run

Re: Substitute for synonym in Oracle after migration to postgres

On Tue, Apr 06, 2021 at 01:22:31PM +0530, aditya desai wrote: > Hi, > We have to access data from one schema to another. We have created a view for > this but performance is not good. We tried > materialized views as well but Refresh MV is creating problem as it puts and > access exclusive locks.

Re: Substitute for synonym in Oracle after migration to postgres

On Tue, 2021-04-06 at 13:22 +0530, aditya desai wrote: > We have to access data from one schema to another. We have created > a view for this but performance is not good. The performance of a view that is just a simple SELECT to a table in a different schema will be just as good as using that tab

Substitute for synonym in Oracle after migration to postgres

Hi, We have to access data from one schema to another. We have created a view for this but performance is not good. We tried materialized views as well but Refresh MV is creating problem as it puts and access exclusive locks. Is there any other way to achieve this? Regards, Aditya.