Substitute for synonym in Oracle after migration to postgres

[aditya desai]

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.

Re: Substitute for synonym in Oracle after migration to postgres

[Laurenz Albe]

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 table
directly.

Yours,
Laurenz Albe
-- 
Cybertec | https://www.cybertec-postgresql.com

Re: Substitute for synonym in Oracle after migration to postgres

[hubert depesz lubaczewski]

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.
> Is there any other way to achieve this?

Yes, just use the other table right in your query. There is no need to
add wrappers.

select * from schema1.table join schema2.table on ...

depesz

select count(*) is slow

[aditya desai]

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
this query in 2-3 seconds(it takes 2 seconds in Oracle).

Here is a query plan:

"Aggregate  (cost=622347.34..622347.35 rows=1 width=8) (actual
time=12850.580..12850.580 rows=1 loops=1)"
"  ->  Bitmap Heap Scan on table  (cost=413379.89..621681.38 rows=266383
width=0) (actual time=12645.656..12835.185 rows=209749 loops=1)"
"Recheck Cond: (((postcode)::text >= '00420'::text) AND
((postcode)::text <= '00500'::text))"
"Heap Blocks: exact=118286"
"->  Bitmap Index Scan on table_i4  (cost=0.00..413313.29
rows=266383 width=0) (actual time=12615.321..12615.321 rows=209982 loops=1)"
"  Index Cond: (((postcode)::text >= '00420'::text) AND
((postcode)::text <= '00500'::text))"
"Planning Time: 0.191 ms"
"Execution Time: 12852.823 ms"



Regards,
Aditya.

Re: Substitute for synonym in Oracle after migration to postgres

[aditya desai]

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
> > 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?
>
> Yes, just use the other table right in your query. There is no need to
> add wrappers.
>
> select * from schema1.table join schema2.table on ...
>
> depesz
>

Re: select count(*) is slow

[Tom Lane]

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 either change postcode to numeric, change all your queries
of this sort to include the cast explicitly, or make an index that
doesn't have a cast.

regards, tom lane

Re: select count(*) is slow

[aditya desai]

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'
>
> That query does not match this index:
>
> > CREATE INDEX Table_i1
> > ON table  USING btree
> > ((postcode::numeric));
>
> You could either change postcode to numeric, change all your queries
> of this sort to include the cast explicitly, or make an index that
> doesn't have a cast.
>
> regards, tom lane
>

Re: select count(*) is slow

[Andrew Dunstan]

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 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 either change postcode to numeric, change all your queries
> of this sort to include the cast explicitly, or make an index that
> doesn't have a cast.
>
>                       
>


IMNSHO postcodes, zip codes, telephone numbers and the like should never
be numeric under any circumstances. This isn't numeric data (what is the
average postcode?), it's textual data consisting of digits, so they
should always be text/varchar. The index here should just be on the
plain text column, not cast to numeric.


cheers


andrew


--
Andrew Dunstan
EDB: https://www.enterprisedb.com

Re: SHARED LOCKS , EXCLUSIVE LOCKS, ACCESS EXCLUSIVE LOCKS

[Andrew Dunstan]

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 select queries? Is it AUTOVACUUM?
>

Suggest you read this part of The Fine Manual:



cheers


andrew


-- 

Andrew Dunstan
EDB: https://www.enterprisedb.com