pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedSELECT Query taking 200 ms on PostgreSQL compared to 4 ms on Oracle after migration.
14+ messages / 6 participants
[nested] [flat]
* SELECT Query taking 200 ms on PostgreSQL compared to 4 ms on Oracle after migration.
@ 2021-04-03 13:38 aditya desai <admad123@gmail.com>
0 siblings, 3 replies; 14+ messages in thread
From: aditya desai @ 2021-04-03 13:38 UTC (permalink / raw)
To: Pgsql Performance <pgsql-performance@lists.postgresql.org>
Hi,
We migrated our Oracle Databases to PostgreSQL. One of the simple select
query that takes 4 ms on Oracle is taking around 200 ms on PostgreSQL.
Could you please advise. Please find query and query plans below. Gather
cost seems high. Will increasing max_parallel_worker_per_gather help?
explain analyse SELECT bom.address_key dom2137,bom.address_type_key
dom1727,bom.start_date dom1077,bom.end_date dom828,bom.address_status_key
dom1955,bom.address_role_key dom1711,bom.delivery_point_created
dom2362,bom.postcode dom873,bom.postcode_name dom1390,bom.street_name
dom1186,bom.premises_number_1 dom1777,bom.premises_number_2
dom1778,bom.premises_letter_1 dom1784,bom.premises_letter_2
dom1785,bom.premises_separator dom1962,bom.stairway dom892,bom.po_box
dom653,bom.apartment_number dom1732,bom.apartment_letter
dom1739,bom.street_key dom1097,bom.address_use_key dom1609,bom.language_key
dom1272,bom.address_family_id dom1796,bom.cur_address_key
dom2566,bom.created_by dom1052,bom.modified_by dom1158,bom.creation_time
dom1392,bom.modification_time dom1813 FROM DEPT.address dom WHERE
address_key = 6113763
[
{
"Plan": {
"Node Type": "Gather",
"Parallel Aware": false,
"Actual Rows": 1,
"Actual Loops": 1,
"Workers Planned": 1,
"Workers Launched": 1,
"Single Copy": true,
"Plans": [
{
"Node Type": "Index Scan",
"Parent Relationship": "Outer",
"Parallel Aware": false,
"Scan Direction": "Forward",
"Index Name": "address1_i7",
"Relation Name": "address",
"Alias": "dom",
"Actual Rows": 1,
"Actual Loops": 1,
"Index Cond": "(address_key = 6113763)",
"Rows Removed by Index Recheck": 0
}
]
},
"Triggers": []
}
]
"Gather (cost=1000.43..1002.75 rows=1 width=127) (actual
time=174.318..198.539 rows=1 loops=1)"
" Workers Planned: 1"
" Workers Launched: 1"
" Single Copy: true"
" -> Index Scan using address1_i7 on address1 dom (cost=0.43..2.65 rows=1
width=127) (actual time=0.125..0.125 rows=1 loops=1)"
" Index Cond: (address_key = 6113763)"
"Planning Time: 0.221 ms"
"Execution Time: 198.601 ms"
Regards,
Aditya.
^ permalink raw reply [nested|flat] 14+ messages in thread
* Re: SELECT Query taking 200 ms on PostgreSQL compared to 4 ms on Oracle after migration.
@ 2021-04-03 13:47 Bharath Rupireddy <bharath.rupireddyforpostgres@gmail.com>
parent: aditya desai <admad123@gmail.com>
2 siblings, 0 replies; 14+ messages in thread
From: Bharath Rupireddy @ 2021-04-03 13:47 UTC (permalink / raw)
To: aditya desai <admad123@gmail.com>; +Cc: Pgsql Performance <pgsql-performance@lists.postgresql.org>
On Sat, Apr 3, 2021 at 7:08 PM aditya desai <admad123@gmail.com> wrote:
>
> Hi,
> We migrated our Oracle Databases to PostgreSQL. One of the simple select query that takes 4 ms on Oracle is taking around 200 ms on PostgreSQL. Could you please advise. Please find query and query plans below. Gather cost seems high. Will increasing max_parallel_worker_per_gather help?
No it doesn't. For small tables, parallelism might not help since it
doesn't come for free. Try setting max_parallel_worker_per_gather to 0
i.e. without parallel query.
With Regards,
Bharath Rupireddy.
EnterpriseDB: http://www.enterprisedb.com
^ permalink raw reply [nested|flat] 14+ messages in thread
* Re: SELECT Query taking 200 ms on PostgreSQL compared to 4 ms on Oracle after migration.
@ 2021-04-03 14:08 Pavel Stehule <pavel.stehule@gmail.com>
parent: aditya desai <admad123@gmail.com>
2 siblings, 2 replies; 14+ messages in thread
From: Pavel Stehule @ 2021-04-03 14:08 UTC (permalink / raw)
To: aditya desai <admad123@gmail.com>; +Cc: Pgsql Performance <pgsql-performance@lists.postgresql.org>
so 3. 4. 2021 v 15:38 odesílatel aditya desai <admad123@gmail.com> napsal:
> Hi,
> We migrated our Oracle Databases to PostgreSQL. One of the simple select
> query that takes 4 ms on Oracle is taking around 200 ms on PostgreSQL.
> Could you please advise. Please find query and query plans below. Gather
> cost seems high. Will increasing max_parallel_worker_per_gather help?
>
> explain analyse SELECT bom.address_key dom2137,bom.address_type_key
> dom1727,bom.start_date dom1077,bom.end_date dom828,bom.address_status_key
> dom1955,bom.address_role_key dom1711,bom.delivery_point_created
> dom2362,bom.postcode dom873,bom.postcode_name dom1390,bom.street_name
> dom1186,bom.premises_number_1 dom1777,bom.premises_number_2
> dom1778,bom.premises_letter_1 dom1784,bom.premises_letter_2
> dom1785,bom.premises_separator dom1962,bom.stairway dom892,bom.po_box
> dom653,bom.apartment_number dom1732,bom.apartment_letter
> dom1739,bom.street_key dom1097,bom.address_use_key dom1609,bom.language_key
> dom1272,bom.address_family_id dom1796,bom.cur_address_key
> dom2566,bom.created_by dom1052,bom.modified_by dom1158,bom.creation_time
> dom1392,bom.modification_time dom1813 FROM DEPT.address dom WHERE
> address_key = 6113763
>
> [
> {
> "Plan": {
> "Node Type": "Gather",
> "Parallel Aware": false,
> "Actual Rows": 1,
> "Actual Loops": 1,
> "Workers Planned": 1,
> "Workers Launched": 1,
> "Single Copy": true,
> "Plans": [
> {
> "Node Type": "Index Scan",
> "Parent Relationship": "Outer",
> "Parallel Aware": false,
> "Scan Direction": "Forward",
> "Index Name": "address1_i7",
> "Relation Name": "address",
> "Alias": "dom",
> "Actual Rows": 1,
> "Actual Loops": 1,
> "Index Cond": "(address_key = 6113763)",
> "Rows Removed by Index Recheck": 0
> }
> ]
> },
> "Triggers": []
> }
> ]
>
> "Gather (cost=1000.43..1002.75 rows=1 width=127) (actual
> time=174.318..198.539 rows=1 loops=1)"
> " Workers Planned: 1"
> " Workers Launched: 1"
> " Single Copy: true"
> " -> Index Scan using address1_i7 on address1 dom (cost=0.43..2.65 rows=1
> width=127) (actual time=0.125..0.125 rows=1 loops=1)"
> " Index Cond: (address_key = 6113763)"
> "Planning Time: 0.221 ms"
> "Execution Time: 198.601 ms"
>
You should have broken configuration - there is not any reason to start
parallelism - probably some option in postgresql.conf has very bad value.
Second - it's crazy to see 200 ms just on interprocess communication -
maybe your CPU is overutilized.
Regards
Pavel
>
>
> Regards,
> Aditya.
>
^ permalink raw reply [nested|flat] 14+ messages in thread
* Re: SELECT Query taking 200 ms on PostgreSQL compared to 4 ms on Oracle after migration.
@ 2021-04-03 14:10 Michael Lewis <mlewis@entrata.com>
parent: aditya desai <admad123@gmail.com>
2 siblings, 1 reply; 14+ messages in thread
From: Michael Lewis @ 2021-04-03 14:10 UTC (permalink / raw)
To: aditya desai <admad123@gmail.com>; +Cc: Pgsql Performance <pgsql-performance@lists.postgresql.org>
It seems like something is missing. Is this table partitioned? How long ago
was migration done? Has vacuum freeze and analyze of tables been done? Was
index created after populating data or reindexed after perhaps? What
version are you using?
^ permalink raw reply [nested|flat] 14+ messages in thread
* Re: SELECT Query taking 200 ms on PostgreSQL compared to 4 ms on Oracle after migration.
@ 2021-04-03 14:16 Justin Pryzby <pryzby@telsasoft.com>
parent: Pavel Stehule <pavel.stehule@gmail.com>
1 sibling, 1 reply; 14+ messages in thread
From: Justin Pryzby @ 2021-04-03 14:16 UTC (permalink / raw)
To: Pavel Stehule <pavel.stehule@gmail.com>; +Cc: aditya desai <admad123@gmail.com>; pgsql-performance@lists.postgresql.org
On Sat, Apr 03, 2021 at 04:08:01PM +0200, Pavel Stehule wrote:
> so 3. 4. 2021 v 15:38 odesílatel aditya desai <admad123@gmail.com> napsal:
> > "Gather (cost=1000.43..1002.75 rows=1 width=127) (actual
> > time=174.318..198.539 rows=1 loops=1)"
> > " Workers Planned: 1"
> > " Workers Launched: 1"
> > " Single Copy: true"
> > " -> Index Scan using address1_i7 on address1 dom (cost=0.43..2.65 rows=1
> > width=127) (actual time=0.125..0.125 rows=1 loops=1)"
> > " Index Cond: (address_key = 6113763)"
> > "Planning Time: 0.221 ms"
> > "Execution Time: 198.601 ms"
>
> You should have broken configuration - there is not any reason to start
> parallelism - probably some option in postgresql.conf has very bad value.
> Second - it's crazy to see 200 ms just on interprocess communication -
> maybe your CPU is overutilized.
It seems like force_parallel_mode is set, which is for debugging and not for
"forcing things to go faster". Maybe we should rename the parameter, like
parallel_mode_testing=on.
http://rhaas.blogspot.com/2018/06/using-forceparallelmode-correctly.html
--
Justin
^ permalink raw reply [nested|flat] 14+ messages in thread
* Re: SELECT Query taking 200 ms on PostgreSQL compared to 4 ms on Oracle after migration.
@ 2021-04-03 14:59 aditya desai <admad123@gmail.com>
parent: Michael Lewis <mlewis@entrata.com>
0 siblings, 1 reply; 14+ messages in thread
From: aditya desai @ 2021-04-03 14:59 UTC (permalink / raw)
To: Michael Lewis <mlewis@entrata.com>; Pgsql Performance <pgsql-performance@lists.postgresql.org>
Hi Michael,
Thanks for your response.
Is this table partitioned? - No
How long ago was migration done? - 27th March 2021
Has vacuum freeze and analyze of tables been done? - We ran vacuum analyze.
Was index created after populating data or reindexed after perhaps? -
Index was created after data load and reindex was executed on all tables
yesterday.
Version is PostgreSQL-11
Regards,
Aditya.
On Sat, Apr 3, 2021 at 7:40 PM Michael Lewis <mlewis@entrata.com> wrote:
> It seems like something is missing. Is this table partitioned? How long
> ago was migration done? Has vacuum freeze and analyze of tables been done?
> Was index created after populating data or reindexed after perhaps? What
> version are you using?
>
^ permalink raw reply [nested|flat] 14+ messages in thread
* Re: SELECT Query taking 200 ms on PostgreSQL compared to 4 ms on Oracle after migration.
@ 2021-04-03 15:08 aditya desai <admad123@gmail.com>
parent: Justin Pryzby <pryzby@telsasoft.com>
0 siblings, 1 reply; 14+ messages in thread
From: aditya desai @ 2021-04-03 15:08 UTC (permalink / raw)
To: Justin Pryzby <pryzby@telsasoft.com>; Pgsql Performance <pgsql-performance@lists.postgresql.org>
Hi Justin,
Yes, force_parallel_mode is on. Should we set it off?
Regards,
Aditya.
On Sat, Apr 3, 2021 at 7:46 PM Justin Pryzby <pryzby@telsasoft.com> wrote:
> On Sat, Apr 03, 2021 at 04:08:01PM +0200, Pavel Stehule wrote:
> > so 3. 4. 2021 v 15:38 odesílatel aditya desai <admad123@gmail.com>
> napsal:
> > > "Gather (cost=1000.43..1002.75 rows=1 width=127) (actual
> > > time=174.318..198.539 rows=1 loops=1)"
> > > " Workers Planned: 1"
> > > " Workers Launched: 1"
> > > " Single Copy: true"
> > > " -> Index Scan using address1_i7 on address1 dom (cost=0.43..2.65
> rows=1
> > > width=127) (actual time=0.125..0.125 rows=1 loops=1)"
> > > " Index Cond: (address_key = 6113763)"
> > > "Planning Time: 0.221 ms"
> > > "Execution Time: 198.601 ms"
> >
> > You should have broken configuration - there is not any reason to start
> > parallelism - probably some option in postgresql.conf has very bad
> value.
> > Second - it's crazy to see 200 ms just on interprocess communication -
> > maybe your CPU is overutilized.
>
> It seems like force_parallel_mode is set, which is for debugging and not
> for
> "forcing things to go faster". Maybe we should rename the parameter, like
> parallel_mode_testing=on.
>
> http://rhaas.blogspot.com/2018/06/using-forceparallelmode-correctly.html
>
> --
> Justin
>
^ permalink raw reply [nested|flat] 14+ messages in thread
* Re: SELECT Query taking 200 ms on PostgreSQL compared to 4 ms on Oracle after migration.
@ 2021-04-03 15:12 Bruce Momjian <bruce@momjian.us>
parent: aditya desai <admad123@gmail.com>
0 siblings, 2 replies; 14+ messages in thread
From: Bruce Momjian @ 2021-04-03 15:12 UTC (permalink / raw)
To: aditya desai <admad123@gmail.com>; +Cc: Justin Pryzby <pryzby@telsasoft.com>; Pgsql Performance <pgsql-performance@lists.postgresql.org>
On Sat, Apr 3, 2021 at 08:38:18PM +0530, aditya desai wrote:
> Hi Justin,
> Yes, force_parallel_mode is on. Should we set it off?
Yes. I bet someone set it without reading our docs:
https://www.postgresql.org/docs/13/runtime-config-query.html#RUNTIME-CONFIG-QUERY-OTHER
--> Allows the use of parallel queries for testing purposes even in cases
--> where no performance benefit is expected.
We might need to clarify this sentence to be clearer it is _only_ for
testing. Also, I suggest you review _all_ changes that have been made
to the server since I am worried other unwise changes might also have
been made.
---------------------------------------------------------------------------
>
> Regards,
> Aditya.
>
> On Sat, Apr 3, 2021 at 7:46 PM Justin Pryzby <pryzby@telsasoft.com> wrote:
>
> On Sat, Apr 03, 2021 at 04:08:01PM +0200, Pavel Stehule wrote:
> > so 3. 4. 2021 v 15:38 odesílatel aditya desai <admad123@gmail.com>
> napsal:
> > > "Gather (cost=1000.43..1002.75 rows=1 width=127) (actual
> > > time=174.318..198.539 rows=1 loops=1)"
> > > " Workers Planned: 1"
> > > " Workers Launched: 1"
> > > " Single Copy: true"
> > > " -> Index Scan using address1_i7 on address1 dom (cost=0.43..2.65 rows
> =1
> > > width=127) (actual time=0.125..0.125 rows=1 loops=1)"
> > > " Index Cond: (address_key = 6113763)"
> > > "Planning Time: 0.221 ms"
> > > "Execution Time: 198.601 ms"
> >
> > You should have broken configuration - there is not any reason to start
> > parallelism - probably some option in postgresql.conf has very bad
> value.
> > Second - it's crazy to see 200 ms just on interprocess communication -
> > maybe your CPU is overutilized.
>
> It seems like force_parallel_mode is set, which is for debugging and not
> for
> "forcing things to go faster". Maybe we should rename the parameter, like
> parallel_mode_testing=on.
>
> http://rhaas.blogspot.com/2018/06/using-forceparallelmode-correctly.html
>
> --
> Justin
>
--
Bruce Momjian <bruce@momjian.us> https://momjian.us
EDB https://enterprisedb.com
If only the physical world exists, free will is an illusion.
^ permalink raw reply [nested|flat] 14+ messages in thread
* Re: SELECT Query taking 200 ms on PostgreSQL compared to 4 ms on Oracle after migration.
@ 2021-04-03 15:22 aditya desai <admad123@gmail.com>
parent: Bruce Momjian <bruce@momjian.us>
1 sibling, 0 replies; 14+ messages in thread
From: aditya desai @ 2021-04-03 15:22 UTC (permalink / raw)
To: Bruce Momjian <bruce@momjian.us>; Pgsql Performance <pgsql-performance@lists.postgresql.org>
Thanks Bruce!! Will set it off and retry.
On Sat, Apr 3, 2021 at 8:42 PM Bruce Momjian <bruce@momjian.us> wrote:
> On Sat, Apr 3, 2021 at 08:38:18PM +0530, aditya desai wrote:
> > Hi Justin,
> > Yes, force_parallel_mode is on. Should we set it off?
>
> Yes. I bet someone set it without reading our docs:
>
>
> https://www.postgresql.org/docs/13/runtime-config-query.html#RUNTIME-CONFIG-QUERY-OTHER
>
> --> Allows the use of parallel queries for testing purposes even in
> cases
> --> where no performance benefit is expected.
>
> We might need to clarify this sentence to be clearer it is _only_ for
> testing. Also, I suggest you review _all_ changes that have been made
> to the server since I am worried other unwise changes might also have
> been made.
>
> ---------------------------------------------------------------------------
>
> >
> > Regards,
> > Aditya.
> >
> > On Sat, Apr 3, 2021 at 7:46 PM Justin Pryzby <pryzby@telsasoft.com>
> wrote:
> >
> > On Sat, Apr 03, 2021 at 04:08:01PM +0200, Pavel Stehule wrote:
> > > so 3. 4. 2021 v 15:38 odesílatel aditya desai <admad123@gmail.com>
> > napsal:
> > > > "Gather (cost=1000.43..1002.75 rows=1 width=127) (actual
> > > > time=174.318..198.539 rows=1 loops=1)"
> > > > " Workers Planned: 1"
> > > > " Workers Launched: 1"
> > > > " Single Copy: true"
> > > > " -> Index Scan using address1_i7 on address1 dom
> (cost=0.43..2.65 rows
> > =1
> > > > width=127) (actual time=0.125..0.125 rows=1 loops=1)"
> > > > " Index Cond: (address_key = 6113763)"
> > > > "Planning Time: 0.221 ms"
> > > > "Execution Time: 198.601 ms"
> > >
> > > You should have broken configuration - there is not any reason to
> start
> > > parallelism - probably some option in postgresql.conf has very bad
> > value.
> > > Second - it's crazy to see 200 ms just on interprocess
> communication -
> > > maybe your CPU is overutilized.
> >
> > It seems like force_parallel_mode is set, which is for debugging and
> not
> > for
> > "forcing things to go faster". Maybe we should rename the
> parameter, like
> > parallel_mode_testing=on.
> >
> >
> http://rhaas.blogspot.com/2018/06/using-forceparallelmode-correctly.html
> >
> > --
> > Justin
> >
>
> --
> Bruce Momjian <bruce@momjian.us> https://momjian.us
> EDB https://enterprisedb.com
>
> If only the physical world exists, free will is an illusion.
>
>
^ permalink raw reply [nested|flat] 14+ messages in thread
* Re: SELECT Query taking 200 ms on PostgreSQL compared to 4 ms on Oracle after migration.
@ 2021-04-03 15:24 Bruce Momjian <bruce@momjian.us>
parent: Bruce Momjian <bruce@momjian.us>
1 sibling, 0 replies; 14+ messages in thread
From: Bruce Momjian @ 2021-04-03 15:24 UTC (permalink / raw)
To: aditya desai <admad123@gmail.com>; +Cc: Justin Pryzby <pryzby@telsasoft.com>; Pgsql Performance <pgsql-performance@lists.postgresql.org>
On Sat, Apr 3, 2021 at 11:12:01AM -0400, Bruce Momjian wrote:
> On Sat, Apr 3, 2021 at 08:38:18PM +0530, aditya desai wrote:
> > Hi Justin,
> > Yes, force_parallel_mode is on. Should we set it off?
>
> Yes. I bet someone set it without reading our docs:
>
> https://www.postgresql.org/docs/13/runtime-config-query.html#RUNTIME-CONFIG-QUERY-OTHER
>
> --> Allows the use of parallel queries for testing purposes even in cases
> --> where no performance benefit is expected.
>
> We might need to clarify this sentence to be clearer it is _only_ for
> testing. Also, I suggest you review _all_ changes that have been made
> to the server since I am worried other unwise changes might also have
> been made.
This brings up an issue we see occasionally. You can either leave
everything as default, get advice on which defaults to change, or study
each setting and then change defaults. Changing defaults without study
often leads to poor configurations, as we are seeing here.
The lucky thing is that you noticed a slow query and found the
misconfiguration, but I am sure there are many servers where
misconfiguration is never detected. I wish I knew how to improve this
situation, but user education seems to be all we can do.
--
Bruce Momjian <bruce@momjian.us> https://momjian.us
EDB https://enterprisedb.com
If only the physical world exists, free will is an illusion.
^ permalink raw reply [nested|flat] 14+ messages in thread
* Re: SELECT Query taking 200 ms on PostgreSQL compared to 4 ms on Oracle after migration.
@ 2021-04-03 15:30 aditya desai <admad123@gmail.com>
parent: aditya desai <admad123@gmail.com>
0 siblings, 0 replies; 14+ messages in thread
From: aditya desai @ 2021-04-03 15:30 UTC (permalink / raw)
To: Bruce Momjian <bruce@momjian.us>; Pgsql Performance <pgsql-performance@lists.postgresql.org>
adding the group.
aad_log_min_messages | warning
| configuration file
application_name | psql
| client
archive_command |
c:\postgres\bin\xlogcopy\xlogcopy.exe archive blob "%f" "%p" |
configuration file
archive_mode | on
| configuration file
archive_timeout | 15min
| configuration file
authentication_timeout | 30s
| configuration file
autovacuum_analyze_scale_factor | 0.05
| configuration file
autovacuum_naptime | 15s
| configuration file
autovacuum_vacuum_scale_factor | 0.05
| configuration file
bgwriter_delay | 20ms
| configuration file
bgwriter_flush_after | 512kB
| configuration file
bgwriter_lru_maxpages | 100
| configuration file
checkpoint_completion_target | 0.9
| configuration file
checkpoint_flush_after | 256kB
| configuration file
checkpoint_timeout | 5min
| configuration file
client_encoding | UTF8
| client
connection_ID |
5b59f092-444c-49df-b5d6-a7a0028a7855 | client
connection_PeerIP |
fd40:4d4a:11:5067:6d11:500:a07:5144 | client
connection_Vnet | on
| client
constraint_exclusion | partition
| configuration file
data_sync_retry | on
| configuration file
DateStyle | ISO, MDY
| configuration file
default_text_search_config | pg_catalog.english
| configuration file
dynamic_shared_memory_type | windows
| configuration file
effective_cache_size | 160GB
| configuration file
enable_seqscan | off
| configuration file
force_parallel_mode | off
| configuration file
from_collapse_limit | 15
| configuration file
full_page_writes | off
| configuration file
hot_standby | on
| configuration file
hot_standby_feedback | on
| configuration file
join_collapse_limit | 15
| configuration file
lc_messages | English_United States.1252
| configuration file
lc_monetary | English_United States.1252
| configuration file
lc_numeric | English_United States.1252
| configuration file
lc_time | English_United States.1252
| configuration file
listen_addresses | *
| configuration file
log_checkpoints | on
| configuration file
log_connections | on
| configuration file
log_destination | stderr
| configuration file
log_file_mode | 0640
| configuration file
log_line_prefix | %t-%c-
| configuration file
log_min_messages_internal | info
| configuration file
log_rotation_age | 1h
| configuration file
log_rotation_size | 100MB
| configuration file
log_timezone | UTC
| configuration file
logging_collector | on
| configuration file
maintenance_work_mem | 1GB
| configuration file
max_connections | 1900
| configuration file
max_parallel_workers_per_gather | 16
| configuration file
max_replication_slots | 10
| configuration file
max_stack_depth | 2MB
| environment variable
max_wal_senders | 10
| configuration file
max_wal_size | 26931MB
| configuration file
min_wal_size | 4GB
| configuration file
pg_qs.query_capture_mode | top
| configuration file
pgms_wait_sampling.query_capture_mode | all
| configuration file
pgstat_udp_port | 20224
| command line
port | 20224
| command line
random_page_cost | 1.1
| configuration file
shared_buffers | 64GB
| configuration file
ssl | on
| configuration file
ssl_ca_file | root.crt
| configuration file
superuser_reserved_connections | 5
| configuration file
TimeZone | EET
| configuration file
track_io_timing | on
| configuration file
wal_buffers | 128MB
| configuration file
wal_keep_segments | 25
| configuration file
wal_level | replica
| configuration file
work_mem | 16MB
| configuration file
On Sat, Apr 3, 2021 at 8:59 PM aditya desai <admad123@gmail.com> wrote:
> Hi Bruce,
> Please find the below output.force_parallel_mode if off now.
>
> aad_log_min_messages | warning
> | configuration file
> application_name | psql
> | client
> archive_command |
> c:\postgres\bin\xlogcopy\xlogcopy.exe archive blob "%f" "%p" |
> configuration file
> archive_mode | on
> | configuration file
> archive_timeout | 15min
> | configuration file
> authentication_timeout | 30s
> | configuration file
> autovacuum_analyze_scale_factor | 0.05
> | configuration file
> autovacuum_naptime | 15s
> | configuration file
> autovacuum_vacuum_scale_factor | 0.05
> | configuration file
> bgwriter_delay | 20ms
> | configuration file
> bgwriter_flush_after | 512kB
> | configuration file
> bgwriter_lru_maxpages | 100
> | configuration file
> checkpoint_completion_target | 0.9
> | configuration file
> checkpoint_flush_after | 256kB
> | configuration file
> checkpoint_timeout | 5min
> | configuration file
> client_encoding | UTF8
> | client
> connection_ID |
> 5b59f092-444c-49df-b5d6-a7a0028a7855 | client
> connection_PeerIP |
> fd40:4d4a:11:5067:6d11:500:a07:5144 | client
> connection_Vnet | on
> | client
> constraint_exclusion | partition
> | configuration file
> data_sync_retry | on
> | configuration file
> DateStyle | ISO, MDY
> | configuration file
> default_text_search_config | pg_catalog.english
> | configuration file
> dynamic_shared_memory_type | windows
> | configuration file
> effective_cache_size | 160GB
> | configuration file
> enable_seqscan | off
> | configuration file
> force_parallel_mode | off
> | configuration file
> from_collapse_limit | 15
> | configuration file
> full_page_writes | off
> | configuration file
> hot_standby | on
> | configuration file
> hot_standby_feedback | on
> | configuration file
> join_collapse_limit | 15
> | configuration file
> lc_messages | English_United States.1252
> | configuration file
> lc_monetary | English_United States.1252
> | configuration file
> lc_numeric | English_United States.1252
> | configuration file
> lc_time | English_United States.1252
> | configuration file
> listen_addresses | *
> | configuration file
> log_checkpoints | on
> | configuration file
> log_connections | on
> | configuration file
> log_destination | stderr
> | configuration file
> log_file_mode | 0640
> | configuration file
> log_line_prefix | %t-%c-
> | configuration file
> log_min_messages_internal | info
> | configuration file
> log_rotation_age | 1h
> | configuration file
> log_rotation_size | 100MB
> | configuration file
> log_timezone | UTC
> | configuration file
> logging_collector | on
> | configuration file
> maintenance_work_mem | 1GB
> | configuration file
> max_connections | 1900
> | configuration file
> max_parallel_workers_per_gather | 16
> | configuration file
> max_replication_slots | 10
> | configuration file
> max_stack_depth | 2MB
> | environment variable
> max_wal_senders | 10
> | configuration file
> max_wal_size | 26931MB
> | configuration file
> min_wal_size | 4GB
> | configuration file
> pg_qs.query_capture_mode | top
> | configuration file
> pgms_wait_sampling.query_capture_mode | all
> | configuration file
> pgstat_udp_port | 20224
> | command line
> port | 20224
> | command line
> random_page_cost | 1.1
> | configuration file
> shared_buffers | 64GB
> | configuration file
> ssl | on
> | configuration file
> ssl_ca_file | root.crt
> | configuration file
> superuser_reserved_connections | 5
> | configuration file
> TimeZone | EET
> | configuration file
> track_io_timing | on
> | configuration file
> wal_buffers | 128MB
> | configuration file
> wal_keep_segments | 25
> | configuration file
> wal_level | replica
> | configuration file
> work_mem | 16MB
> | configuration file
>
>
> Regards,
> Aditya.
>
>
>
> On Sat, Apr 3, 2021 at 8:34 PM Bruce Momjian <bruce@momjian.us> wrote:
>
>> On Sat, Apr 3, 2021 at 08:29:22PM +0530, aditya desai wrote:
>> > Hi Michael,
>> > Thanks for your response.
>> > Is this table partitioned? - No
>> > How long ago was migration done? - 27th March 2021
>> > Has vacuum freeze and analyze of tables been done? - We ran vacuum
>> analyze.
>> > Was index created after populating data or reindexed after perhaps? -
>> Index
>> > was created after data load and reindex was executed on all tables
>> yesterday.
>> > Version is PostgreSQL-11
>>
>> FYI, the output of these queries will show u what changes have been made
>> to the configuration file:
>>
>> SELECT version();
>>
>> SELECT name, current_setting(name), source
>> FROM pg_settings
>> WHERE source NOT IN ('default', 'override');
>>
>> --
>> Bruce Momjian <bruce@momjian.us> https://momjian.us
>> EDB https://enterprisedb.com
>>
>> If only the physical world exists, free will is an illusion.
>>
>>
^ permalink raw reply [nested|flat] 14+ messages in thread
* Re: SELECT Query taking 200 ms on PostgreSQL compared to 4 ms on Oracle after migration.
@ 2021-04-03 15:33 aditya desai <admad123@gmail.com>
parent: Pavel Stehule <pavel.stehule@gmail.com>
1 sibling, 0 replies; 14+ messages in thread
From: aditya desai @ 2021-04-03 15:33 UTC (permalink / raw)
To: Pavel Stehule <pavel.stehule@gmail.com>; Pgsql Performance <pgsql-performance@lists.postgresql.org>
I will gather all information and get back to you
On Sat, Apr 3, 2021 at 9:00 PM Pavel Stehule <pavel.stehule@gmail.com>
wrote:
>
>
> so 3. 4. 2021 v 17:15 odesílatel aditya desai <admad123@gmail.com> napsal:
>
>> Hi Pavel,
>> Thanks for response. Please see below.
>> work_mem=16MB
>> maintenance_work_mem=1GB
>> effective_cache_size=160GB
>> shared_buffers=64GB
>> force_parallel_mode=ON
>>
>
> force_parallel_mode is very bad idea. efective_cache_size=160GB can be too
> much too. work_mem 16 MB is maybe too low. The configuration looks a little
> bit chaotic :)
>
> How much has RAM your server? How much CPU cores are there? What is
> max_connections?
>
> Regards
>
> Pavel
>
>
>
>> Regards,
>> Aditya.
>>
>>
>> On Sat, Apr 3, 2021 at 7:38 PM Pavel Stehule <pavel.stehule@gmail.com>
>> wrote:
>>
>>>
>>>
>>> so 3. 4. 2021 v 15:38 odesílatel aditya desai <admad123@gmail.com>
>>> napsal:
>>>
>>>> Hi,
>>>> We migrated our Oracle Databases to PostgreSQL. One of the simple
>>>> select query that takes 4 ms on Oracle is taking around 200 ms on
>>>> PostgreSQL. Could you please advise. Please find query and query plans
>>>> below. Gather cost seems high. Will increasing
>>>> max_parallel_worker_per_gather help?
>>>>
>>>> explain analyse SELECT bom.address_key dom2137,bom.address_type_key
>>>> dom1727,bom.start_date dom1077,bom.end_date dom828,bom.address_status_key
>>>> dom1955,bom.address_role_key dom1711,bom.delivery_point_created
>>>> dom2362,bom.postcode dom873,bom.postcode_name dom1390,bom.street_name
>>>> dom1186,bom.premises_number_1 dom1777,bom.premises_number_2
>>>> dom1778,bom.premises_letter_1 dom1784,bom.premises_letter_2
>>>> dom1785,bom.premises_separator dom1962,bom.stairway dom892,bom.po_box
>>>> dom653,bom.apartment_number dom1732,bom.apartment_letter
>>>> dom1739,bom.street_key dom1097,bom.address_use_key dom1609,bom.language_key
>>>> dom1272,bom.address_family_id dom1796,bom.cur_address_key
>>>> dom2566,bom.created_by dom1052,bom.modified_by dom1158,bom.creation_time
>>>> dom1392,bom.modification_time dom1813 FROM DEPT.address dom WHERE
>>>> address_key = 6113763
>>>>
>>>> [
>>>> {
>>>> "Plan": {
>>>> "Node Type": "Gather",
>>>> "Parallel Aware": false,
>>>> "Actual Rows": 1,
>>>> "Actual Loops": 1,
>>>> "Workers Planned": 1,
>>>> "Workers Launched": 1,
>>>> "Single Copy": true,
>>>> "Plans": [
>>>> {
>>>> "Node Type": "Index Scan",
>>>> "Parent Relationship": "Outer",
>>>> "Parallel Aware": false,
>>>> "Scan Direction": "Forward",
>>>> "Index Name": "address1_i7",
>>>> "Relation Name": "address",
>>>> "Alias": "dom",
>>>> "Actual Rows": 1,
>>>> "Actual Loops": 1,
>>>> "Index Cond": "(address_key = 6113763)",
>>>> "Rows Removed by Index Recheck": 0
>>>> }
>>>> ]
>>>> },
>>>> "Triggers": []
>>>> }
>>>> ]
>>>>
>>>> "Gather (cost=1000.43..1002.75 rows=1 width=127) (actual
>>>> time=174.318..198.539 rows=1 loops=1)"
>>>> " Workers Planned: 1"
>>>> " Workers Launched: 1"
>>>> " Single Copy: true"
>>>> " -> Index Scan using address1_i7 on address1 dom (cost=0.43..2.65
>>>> rows=1 width=127) (actual time=0.125..0.125 rows=1 loops=1)"
>>>> " Index Cond: (address_key = 6113763)"
>>>> "Planning Time: 0.221 ms"
>>>> "Execution Time: 198.601 ms"
>>>>
>>>
>>> You should have broken configuration - there is not any reason to start
>>> parallelism - probably some option in postgresql.conf has very bad value.
>>> Second - it's crazy to see 200 ms just on interprocess communication -
>>> maybe your CPU is overutilized.
>>>
>>> Regards
>>>
>>> Pavel
>>>
>>>
>>>
>>>
>>>>
>>>>
>>>> Regards,
>>>> Aditya.
>>>>
>>>
^ permalink raw reply [nested|flat] 14+ messages in thread
end of thread, other threads:[~2021-04-03 15:33 UTC | newest]
Thread overview: 14+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2021-04-03 13:38 SELECT Query taking 200 ms on PostgreSQL compared to 4 ms on Oracle after migration. aditya desai <admad123@gmail.com>
2021-04-03 13:47 ` Bharath Rupireddy <bharath.rupireddyforpostgres@gmail.com>
2021-04-03 14:08 ` Pavel Stehule <pavel.stehule@gmail.com>
2021-04-03 14:16 ` Justin Pryzby <pryzby@telsasoft.com>
2021-04-03 15:08 ` aditya desai <admad123@gmail.com>
2021-04-03 15:12 ` Bruce Momjian <bruce@momjian.us>
2021-04-03 15:22 ` aditya desai <admad123@gmail.com>
2021-04-03 15:24 ` Bruce Momjian <bruce@momjian.us>
2021-04-03 15:33 ` aditya desai <admad123@gmail.com>
2021-04-03 14:10 ` Michael Lewis <mlewis@entrata.com>
2021-04-03 14:59 ` aditya desai <admad123@gmail.com>
2021-04-03 15:30 ` aditya desai <admad123@gmail.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox