pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedPerformance issue
13+ messages / 12 participants
[nested] [flat]
* Performance issue
@ 2003-09-24 05:48 peter <pmcgregor@advso.com>
0 siblings, 3 replies; 13+ messages in thread
From: peter @ 2003-09-24 05:48 UTC (permalink / raw)
To: pgsql-performance
Hello,
I have been trying to get my Postgres database to do faster inserts.
The environment is basically a single user situation.
The part that I would like to speed up is when a User copys a Project.
A Project consists of a number of Rooms(say 60). Each room contains a
number of items.
A project will contain say 20,000 records.
Anyway the copying process gets slower and slower, as more projects are
added to the database.
My statistics(Athlon 1.8Ghz)
----------------
20,000 items Takes on average 0.078seconds/room
385,000 items Takes on average .11seconds/room
690,000 items takes on average .270seconds/room
1,028,000 items Takes on average .475seconds/room
As can be seen the time taken to process each room increases. A commit
occurs when a room has been copied.
The hard drive is not being driven very hard. The hard drive light
only flashes about twice a second when there are a million records in
the database.
I thought that the problem could have been my plpgsql procedure because
I assume the code is interpreted.
However I have just rewriten the code using straight sql(with some temp
fields),
and the times turn out to be almost exactly the same as the plpgsql
version.
The read speed for the Application is fine. The sql planner seems to be
doing a good job. There has been only one problem
that I have found with one huge select, which was fixed by a cross join.
I am running Red hat 8. Some of my conf entries that I have changed
follow
shared_buffers = 3700
effective_cache_size = 4000
sort_mem = 32168
Are the increasing times reasonable?
The times themselves might look slow, but thats because there are a
number of tables involved in a Copy
I can increase the shared buffer sizes above 32M, but would this really
help?
TIA
peter Mcgregor
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: Performance issue
@ 2003-09-24 17:05 Richard Jones <rj@last.fm>
parent: Joseph Bove <jbove@vetstar.com>
0 siblings, 0 replies; 13+ messages in thread
From: Richard Jones @ 2003-09-24 17:05 UTC (permalink / raw)
To: pgsql-performance
get rid of any unnecessary indexes?
i've found that droping indexes and re-creating them isn't usually worth the
effort
mount the disk with the noatime option which saves you the time involved in
updating the last access time on files
make sure you're doing all the inserts in one transaction.. wrapping a bunch
of INSERTS in BEGIN & COMMIT speeds them up loads.
> At 05:48 PM 9/24/2003 +1200, peter wrote:
> >Hello,
> >
> >I have been trying to get my Postgres database to do faster inserts.
> >
> >The environment is basically a single user situation.
> >
> >The part that I would like to speed up is when a User copys a Project.
> >A Project consists of a number of Rooms(say 60). Each room contains a
> >number of items.
> >A project will contain say 20,000 records.
> >
> >Anyway the copying process gets slower and slower, as more projects are
> >added to the database.
> >
> >My statistics(Athlon 1.8Ghz)
> >----------------
> >20,000 items Takes on average 0.078seconds/room
> >385,000 items Takes on average .11seconds/room
> >690,000 items takes on average .270seconds/room
> >1,028,000 items Takes on average .475seconds/room
> >
> >As can be seen the time taken to process each room increases. A commit
> >occurs when a room has been copied.
> >The hard drive is not being driven very hard. The hard drive light only
> >flashes about twice a second when there are a million records in the
> > database.
> >
> >I thought that the problem could have been my plpgsql procedure because I
> >assume the code is interpreted.
> >However I have just rewriten the code using straight sql(with some temp
> >fields),
> >and the times turn out to be almost exactly the same as the plpgsql
> > version.
> >
> >The read speed for the Application is fine. The sql planner seems to be
> >doing a good job. There has been only one problem
> >that I have found with one huge select, which was fixed by a cross join.
> >
> > I am running Red hat 8. Some of my conf entries that I have changed
> > follow shared_buffers = 3700
> >effective_cache_size = 4000
> >sort_mem = 32168
> >
> >Are the increasing times reasonable?
> >The times themselves might look slow, but thats because there are a number
> >of tables involved in a Copy
> >
> >I can increase the shared buffer sizes above 32M, but would this really
> > help?
> >
> >TIA
> >
> >peter Mcgregor
> >
> >
> >---------------------------(end of broadcast)---------------------------
> >TIP 4: Don't 'kill -9' the postmaster
>
> ---------------------------(end of broadcast)---------------------------
> TIP 2: you can get off all lists at once with the unregister command
> (send "unregister YourEmailAddressHere" to majordomo@postgresql.org)
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: Performance issue
@ 2003-09-24 17:09 Joseph Bove <jbove@vetstar.com>
parent: peter <pmcgregor@advso.com>
2 siblings, 1 reply; 13+ messages in thread
From: Joseph Bove @ 2003-09-24 17:09 UTC (permalink / raw)
To: pgsql-performance
Peter,
One possibility is to drop all the indexes, do the insert and re-add the
indexes.
The more indexes that exist and the more rows that exist, the more costly
the insert.
Regards,
Joseph
At 05:48 PM 9/24/2003 +1200, peter wrote:
>Hello,
>
>I have been trying to get my Postgres database to do faster inserts.
>
>The environment is basically a single user situation.
>
>The part that I would like to speed up is when a User copys a Project.
>A Project consists of a number of Rooms(say 60). Each room contains a
>number of items.
>A project will contain say 20,000 records.
>
>Anyway the copying process gets slower and slower, as more projects are
>added to the database.
>
>My statistics(Athlon 1.8Ghz)
>----------------
>20,000 items Takes on average 0.078seconds/room
>385,000 items Takes on average .11seconds/room
>690,000 items takes on average .270seconds/room
>1,028,000 items Takes on average .475seconds/room
>
>As can be seen the time taken to process each room increases. A commit
>occurs when a room has been copied.
>The hard drive is not being driven very hard. The hard drive light only
>flashes about twice a second when there are a million records in the database.
>
>I thought that the problem could have been my plpgsql procedure because I
>assume the code is interpreted.
>However I have just rewriten the code using straight sql(with some temp
>fields),
>and the times turn out to be almost exactly the same as the plpgsql version.
>
>The read speed for the Application is fine. The sql planner seems to be
>doing a good job. There has been only one problem
>that I have found with one huge select, which was fixed by a cross join.
>
> I am running Red hat 8. Some of my conf entries that I have changed follow
>shared_buffers = 3700
>effective_cache_size = 4000
>sort_mem = 32168
>
>Are the increasing times reasonable?
>The times themselves might look slow, but thats because there are a number
>of tables involved in a Copy
>
>I can increase the shared buffer sizes above 32M, but would this really help?
>
>TIA
>
>peter Mcgregor
>
>
>---------------------------(end of broadcast)---------------------------
>TIP 4: Don't 'kill -9' the postmaster
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: Performance issue
@ 2003-09-24 17:47 Sean Chittenden <sean@chittenden.org>
parent: peter <pmcgregor@advso.com>
2 siblings, 0 replies; 13+ messages in thread
From: Sean Chittenden @ 2003-09-24 17:47 UTC (permalink / raw)
To: peter <pmcgregor@advso.com>; +Cc: pgsql-performance
> My statistics(Athlon 1.8Ghz)
> ----------------
> 20,000 items Takes on average 0.078seconds/room
> 385,000 items Takes on average .11seconds/room
> 690,000 items takes on average .270seconds/room
> 1,028,000 items Takes on average .475seconds/room
[snip]
> I am running Red hat 8. Some of my conf entries that I have changed
> follow
> shared_buffers = 3700
> effective_cache_size = 4000
> sort_mem = 32168
Have you twiddled with your wal_buffers or checkpoint_segments? Might
be something to look at.
-sc
--
Sean Chittenden
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: Performance issue
@ 2003-09-24 18:35 Rod Taylor <rbt@rbt.ca>
parent: peter <pmcgregor@advso.com>
2 siblings, 0 replies; 13+ messages in thread
From: Rod Taylor @ 2003-09-24 18:35 UTC (permalink / raw)
To: peter <pmcgregor@advso.com>; +Cc: pgsql-performance
> 20,000 items Takes on average 0.078seconds/room
> 385,000 items Takes on average .11seconds/room
> 690,000 items takes on average .270seconds/room
> 1,028,000 items Takes on average .475seconds/room
>
> As can be seen the time taken to process each room increases. A commit
> occurs when a room has been copied.
It probably isn't the insert that is getting slower, but a select.
Foreign keys to growing tables will exhibit this behaviour.
Since the time is doubling with the number of items, you might want to
check for a SELECT working with a sequential scan rather than an index
scan.
Attachments:
[application/pgp-signature] signature.asc (186B, ../../1064428549.20881.20.camel@jester/2-signature.asc)
download
^ permalink raw reply [nested|flat] 13+ messages in thread
* Performance issue
@ 2007-08-27 12:16 Willo van der Merwe <willo@studentvillage.co.za>
0 siblings, 0 replies; 13+ messages in thread
From: Willo van der Merwe @ 2007-08-27 12:16 UTC (permalink / raw)
To: pgsql-performance
Hi Guys,
I have something odd. I have Gallery2 running on PostgreSQL 8.1, and
recently I upgraded to 8.1.9-1.el4s1.1 (64bit). The issue here really is
how do I get PostgreSQL to work with their horrible code. The queries
they generate look something like :
SELECT blah, blah FROM table1, table2 WHERE <some relational stuff> AND
id IN (<here a list of 42000+ IDs are listed>)
On the previous version (which I can't recall what it was, but it was a
version 8.1) the queries executed fine, but suddenly now, these queries
are taking up-to 4 minutes to complete. I am convinced it's the
parsing/handling of the IN clause. It could, of course, be that the list
has grown so large that it can't fit into a buffer anymore. For obvious
reasons I can't run an EXPLAIN ANALYZE from a prompt. I vacuum and
reindex the database daily.
I'd prefer not to have to rewrite the code, so any suggestions would be
very welcome.
Kind regards
Willo van der Merwe
^ permalink raw reply [nested|flat] 13+ messages in thread
* Performance issue
@ 2007-08-27 13:41 Willo van der Merwe <willo@mirasol.co.za>
0 siblings, 3 replies; 13+ messages in thread
From: Willo van der Merwe @ 2007-08-27 13:41 UTC (permalink / raw)
To: pgsql-performance
Hi Guys,
I have something odd. I have Gallery2 running on PostgreSQL 8.1, and
recently I upgraded to 8.1.9-1.el4s1.1 (64bit). The issue here really is
how do I get PostgreSQL to work with their horrible code. The queries
they generate look something like :
SELECT blah, blah FROM table1, table2 WHERE <some relational stuff> AND
id IN (<here a list of 42000+ IDs are listed>)
On the previous version (which I can't recall what it was, but it was a
version 8.1) the queries executed fine, but suddenly now, these queries
are taking up-to 4 minutes to complete. I am convinced it's the
parsing/handling of the IN clause. It could, of course, be that the list
has grown so large that it can't fit into a buffer anymore. For obvious
reasons I can't run an EXPLAIN ANALYZE from a prompt. I vacuum and
reindex the database daily.
I'd prefer not to have to rewrite the code, so any suggestions would be
very welcome.
Kind regards
Willo van der Merwe
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: Performance issue
@ 2007-08-27 14:45 Bill Moran <wmoran@collaborativefusion.com>
parent: Willo van der Merwe <willo@mirasol.co.za>
2 siblings, 0 replies; 13+ messages in thread
From: Bill Moran @ 2007-08-27 14:45 UTC (permalink / raw)
To: Willo van der Merwe <willo@mirasol.co.za>; +Cc: pgsql-performance
In response to Willo van der Merwe <willo@mirasol.co.za>:
> Hi Guys,
>
> I have something odd. I have Gallery2 running on PostgreSQL 8.1, and
> recently I upgraded to 8.1.9-1.el4s1.1 (64bit). The issue here really is
> how do I get PostgreSQL to work with their horrible code. The queries
> they generate look something like :
> SELECT blah, blah FROM table1, table2 WHERE <some relational stuff> AND
> id IN (<here a list of 42000+ IDs are listed>)
>
> On the previous version (which I can't recall what it was, but it was a
> version 8.1) the queries executed fine, but suddenly now, these queries
> are taking up-to 4 minutes to complete. I am convinced it's the
> parsing/handling of the IN clause. It could, of course, be that the list
> has grown so large that it can't fit into a buffer anymore. For obvious
> reasons I can't run an EXPLAIN ANALYZE from a prompt.
Those reasons are not obvious to me. The explain analyze output is
going to be key to working this out -- unless it's something like
your postgresql.conf isn't properly tuned.
> I vacuum and
> reindex the database daily.
>
> I'd prefer not to have to rewrite the code, so any suggestions would be
> very welcome.
--
Bill Moran
Collaborative Fusion Inc.
http://people.collaborativefusion.com/~wmoran/
wmoran@collaborativefusion.com
Phone: 412-422-3463x4023
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: Performance issue
@ 2007-08-27 15:55 Tom Lane <tgl@sss.pgh.pa.us>
parent: Willo van der Merwe <willo@mirasol.co.za>
2 siblings, 0 replies; 13+ messages in thread
From: Tom Lane @ 2007-08-27 15:55 UTC (permalink / raw)
To: Willo van der Merwe <willo@mirasol.co.za>; +Cc: pgsql-performance
Willo van der Merwe <willo@mirasol.co.za> writes:
> I have something odd. I have Gallery2 running on PostgreSQL 8.1, and
> recently I upgraded to 8.1.9-1.el4s1.1 (64bit). The issue here really is
> how do I get PostgreSQL to work with their horrible code. The queries
> they generate look something like :
> SELECT blah, blah FROM table1, table2 WHERE <some relational stuff> AND
> id IN (<here a list of 42000+ IDs are listed>)
> On the previous version (which I can't recall what it was, but it was a
> version 8.1) the queries executed fine, but suddenly now, these queries
> are taking up-to 4 minutes to complete. I am convinced it's the
> parsing/handling of the IN clause.
You're wrong about that, because we have not done anything to change IN
planning in 8.1.x. You might need to re-ANALYZE or something; it sounds
to me more like the planner has changed strategies in the wrong direction.
FWIW, 8.2 should be vastly more efficient than 8.1 for this sort of
query --- any chance of an upgrade?
regards, tom lane
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: Performance issue
@ 2007-08-28 09:34 Willo van der Merwe <willo@mirasol.co.za>
parent: Willo van der Merwe <willo@mirasol.co.za>
2 siblings, 0 replies; 13+ messages in thread
From: Willo van der Merwe @ 2007-08-28 09:34 UTC (permalink / raw)
To: pgsql-performance
Hi Guys,
Following Tom Lane's advice I upgraded to 8.2, and that solved all my
problems. :D
Thank you so much for your input, I really appreciate it.
Kind regards
Willo van der Merwe
^ permalink raw reply [nested|flat] 13+ messages in thread
* Performance issue
@ 2020-06-14 22:45 Nagaraj Raj <nagaraj.sf@yahoo.com>
0 siblings, 2 replies; 13+ messages in thread
From: Nagaraj Raj @ 2020-06-14 22:45 UTC (permalink / raw)
To: Pgsql Performance <pgsql-performance@lists.postgresql.org>
Hello
My PostgreSQL server 10.11 running on windows which are running very slow. DB has two tables with ~200Mil records in each. user queries are very slow even explain analyze also taking a longer.
Could you please help me to tune this query and any suggestions to improve system performance?
Table structures:
Table1:
-- Records 213621151
CREATE TABLE test1
(
individual_entity_proxy_id bigint NOT NULL,
household_entity_proxy_id bigint,
individual_personal_link_sid bigint NOT NULL,
city_name character varying(100) COLLATE pg_catalog."default",
state_prov_cd character varying(40) COLLATE pg_catalog."default",
pstl_code character varying(40) COLLATE pg_catalog."default",
npa integer,
nxx integer,
email_domain character varying(400) COLLATE pg_catalog."default",
email_preference character varying(40) COLLATE pg_catalog."default",
direct_mail_preference character varying(40) COLLATE pg_catalog."default",
profane_wrd_ind character(1) COLLATE pg_catalog."default",
tmo_ofnsv_name_ind character(1) COLLATE pg_catalog."default",
census_block_id character varying(40) COLLATE pg_catalog."default",
has_first_name character(1) COLLATE pg_catalog."default",
has_middle_name character(1) COLLATE pg_catalog."default",
has_last_name character(1) COLLATE pg_catalog."default",
has_email_address character(1) COLLATE pg_catalog."default",
has_individual_address character(1) COLLATE pg_catalog."default",
email_address_sid bigint,
person_name_sid bigint,
physical_address_sid bigint,
telephone_number_sid bigint,
shared_email_with_customer_ind character(1) COLLATE pg_catalog."default",
shared_paddr_with_customer_ind character(1) COLLATE pg_catalog."default",
last_contacted_email_datetime timestamp without time zone,
last_contacted_dm_datetime timestamp without time zone,
last_contacted_digital_datetime timestamp without time zone,
last_contacted_anychannel_dttm timestamp without time zone,
hard_bounce_ind integer,
src_sys_id integer NOT NULL,
insrt_prcs_id bigint,
updt_prcs_id bigint,
stg_prcs_id bigint,
load_dttm timestamp without time zone NOT NULL,
updt_dttm timestamp without time zone,
md5_chk_sum character varying(200) COLLATE pg_catalog."default",
deld_from_src_ind character(1) COLLATE pg_catalog."default" NOT NULL,
orphan_ind character(1) COLLATE pg_catalog."default" NOT NULL,
CONSTRAINT pk_i_entity_proxy_id PRIMARY KEY (individual_entity_proxy_id)
);
CREATE INDEX indx_prospect_indv_entty_id
ON test1 USING btree
(individual_entity_proxy_id )
Table 2:
-- Records 260652202
CREATE TABLE test2
(
individual_entity_proxy_id bigint NOT NULL,
cstmr_prspct_ind character varying(40) COLLATE pg_catalog."default",
last_appnd_dttm timestamp without time zone,
last_sprsn_dttm timestamp without time zone,
infrrd_gender_code character varying(40) COLLATE pg_catalog."default",
govt_prison_ind character(1) COLLATE pg_catalog."default",
tax_bnkrpt_dcsd_ind character(1) COLLATE pg_catalog."default",
underbank_rank_nbr integer,
hvy_txn_rank_nbr integer,
prominence_nbr integer,
ocptn_code character varying(40) COLLATE pg_catalog."default",
educ_lvl_nbr integer,
gender_code character varying(40) COLLATE pg_catalog."default",
infrrd_hh_rank_nbr integer,
econmc_stable_nbr integer,
directv_sbscrbr_propnsty_code character varying(40) COLLATE pg_catalog."default",
dish_sbscrbr_propnsty_code character varying(40) COLLATE pg_catalog."default",
iphone_user_propnsty_code character varying(40) COLLATE pg_catalog."default",
smrt_hm_devc_propnsty_code character varying(40) COLLATE pg_catalog."default",
sml_busi_ownr_propnsty_code character varying(40) COLLATE pg_catalog."default",
tv_internet_bndl_propnsty_code character varying(40) COLLATE pg_catalog."default",
dog_ownr_propnsty_code character varying(40) COLLATE pg_catalog."default",
cat_ownr_propnsty_code character varying(40) COLLATE pg_catalog."default",
dine_out_propnsty_code character varying(40) COLLATE pg_catalog."default",
taco_bell_diner_propnsty_code character varying(40) COLLATE pg_catalog."default",
auto_insrnc_byr_propnsty_code character varying(40) COLLATE pg_catalog."default",
src_sys_id integer NOT NULL,
insrt_prcs_id bigint,
updt_prcs_id bigint,
stg_prcs_id bigint,
load_dttm timestamp without time zone NOT NULL,
updt_dttm timestamp without time zone,
deld_from_src_ind character(1) COLLATE pg_catalog."default" NOT NULL,
orphan_ind character(1) COLLATE pg_catalog."default" NOT NULL,
CONSTRAINT pk_entity_proxy_id PRIMARY KEY (individual_entity_proxy_id)
);
User query:
explain analyze select COUNT(*) as "DII_1"
from ( select distinct table0."individual_entity_proxy_id" as "INDIVIDUAL_ENTITY_PROXY_ID"
from test1 table0
inner join test2 table1
on table0."individual_entity_proxy_id" = table1."individual_entity_proxy_id"
where ((table0."shared_paddr_with_customer_ind" = 'N')
and (table0."profane_wrd_ind" = 'N')
and (table0."tmo_ofnsv_name_ind" = 'N')
and ((table0."last_contacted_dm_datetime" is null)
or (table0."last_contacted_dm_datetime" < TIMESTAMP '2020-03-15 0:00:00.000000'))
and (table0."has_individual_address" = 'Y')
and (table0."has_last_name" = 'Y')
and (table0."has_first_name" = 'Y')
and (table0."direct_mail_preference" is null))
and ((table1."tax_bnkrpt_dcsd_ind" = 'N')
and (table1."cstmr_prspct_ind" = 'Prospect')
and (table1."govt_prison_ind" = 'N')) ) TXT_1;
Explain Analyze :
"Aggregate (cost=5345632.91..5345632.92 rows=1 width=8) (actual time=442688.462..442688.462 rows=1 loops=1)"
" -> Unique (cost=150.13..4943749.39 rows=32150682 width=8) (actual time=0.022..439964.214 rows=32368180 loops=1)"
" -> Merge Join (cost=150.13..4863372.68 rows=32150682 width=8) (actual time=0.021..435818.276 rows=32368180 loops=1)"
" Merge Cond: (table0.individual_entity_proxy_id = table1.individual_entity_proxy_id)"
" -> Index Scan using indx_prospect_indv_entty_id on test1 table0 (cost=0.56..2493461.92 rows=32233405 width=8) (actual time=0.011..63009.551 rows=32368180 loops=1)"
" Filter: ((direct_mail_preference IS NULL) AND ((last_contacted_dm_datetime IS NULL) OR (last_contacted_dm_datetime < '2020-03-15 00:00:00'::timestamp without time zone)) AND (shared_paddr_with_customer_ind = 'N'::bpchar) AND (profane_wrd_ind = 'N'::bpchar) AND (tmo_ofnsv_name_ind = 'N'::bpchar) AND (has_individual_address = 'Y'::bpchar) AND (has_last_name = 'Y'::bpchar) AND (has_first_name = 'Y'::bpchar))"
" Rows Removed by Filter: 7709177"
" -> Index Scan using pk_entity_proxy_id on test2 table1 (cost=0.56..1867677.94 rows=40071417 width=8) (actual time=0.008..363534.437 rows=40077727 loops=1)"
" Filter: ((tax_bnkrpt_dcsd_ind = 'N'::bpchar) AND (govt_prison_ind = 'N'::bpchar) AND ((cstmr_prspct_ind)::text = 'Prospect'::text))"
" Rows Removed by Filter: 94756"
"Planning time: 0.400 ms"
"Execution time: 442688.523 ms"
Server config:
PostgreSQL v10.11
RAM: 380GB
vCore: 32
Shared_buffers: 65GB
work_mem:104857kB
maintenance_work_mem:256MB
effective_cache_size: 160GB
https://dba.stackexchange.com/questions/269138/postgresql-server-running-very-slow-at-minimal-work-l...
Thanks,
Raj
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: Performance issue
@ 2020-06-14 23:55 David Rowley <dgrowleyml@gmail.com>
parent: Nagaraj Raj <nagaraj.sf@yahoo.com>
1 sibling, 0 replies; 13+ messages in thread
From: David Rowley @ 2020-06-14 23:55 UTC (permalink / raw)
To: Nagaraj Raj <nagaraj.sf@yahoo.com>; +Cc: Pgsql Performance <pgsql-performance@lists.postgresql.org>
On Mon, 15 Jun 2020 at 10:46, Nagaraj Raj <nagaraj.sf@yahoo.com> wrote:
> CREATE TABLE test1
> (
...
> CONSTRAINT pk_i_entity_proxy_id PRIMARY KEY (individual_entity_proxy_id)
>
> );
> CREATE TABLE test2
> (
...
> CONSTRAINT pk_entity_proxy_id PRIMARY KEY (individual_entity_proxy_id)
> );
>
>
> User query:
>
> explain analyze select COUNT(*) as "DII_1"
> from ( select distinct table0."individual_entity_proxy_id" as "INDIVIDUAL_ENTITY_PROXY_ID"
> from test1 table0
> inner join test2 table1
> on table0."individual_entity_proxy_id" = table1."individual_entity_proxy_id"
Why do you use "select distinct". It seems to me that you're putting a
distinct clause on the primary key of test1 and joining to another
table in a way that cannot cause duplicates.
I imagine dropping that distinct will speed up the query quite a bit.
David
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: Performance issue
@ 2020-06-15 00:05 Justin Pryzby <pryzby@telsasoft.com>
parent: Nagaraj Raj <nagaraj.sf@yahoo.com>
1 sibling, 0 replies; 13+ messages in thread
From: Justin Pryzby @ 2020-06-15 00:05 UTC (permalink / raw)
To: Nagaraj Raj <nagaraj.sf@yahoo.com>; +Cc: Pgsql Performance <pgsql-performance@lists.postgresql.org>
On Sun, Jun 14, 2020 at 10:45:52PM +0000, Nagaraj Raj wrote:
> My PostgreSQL server 10.11 running on windows which are running very slow. DB has two tables with ~200Mil records in each. user queries are very slow even explain analyze also taking a longer.
>
> Could you please help me to tune this query and any suggestions to improve system performance?
> CREATE TABLE test1
> (
> individual_entity_proxy_id bigint NOT NULL,
...
> CONSTRAINT pk_i_entity_proxy_id PRIMARY KEY (individual_entity_proxy_id)
>
> );
> CREATE INDEX indx_prospect_indv_entty_id ON test1 USING btree (individual_entity_proxy_id )
This index is redundant with the primary key, which implicitly creates a unique
index.
The table structure seems strange: you have two tables with the same PK column,
which is how they're being joined. It seems like that's better expressed as a
single table with all the columns rather than separate tables (but see below).
> explain analyze select COUNT(*) as "DII_1"
> from ( select distinct table0."individual_entity_proxy_id" as "INDIVIDUAL_ENTITY_PROXY_ID"
> from test1 table0
> inner join test2 table1
I think this may be better written as something like:
| SELECT COUNT(id) FROM t0 WHERE EXISTS (SELECT 1 FROM t1 WHERE t1.id=t0.id AND ...) AND ...
It's guaranteed to be distinct since it's a PK column, so it doesn't need a
"Unique" node.
I think it might prefer an seq scan on t0, which might be good since it seems
to be returning over 10% of records.
> Explain Analyze :
>
> "Aggregate (cost=5345632.91..5345632.92 rows=1 width=8) (actual time=442688.462..442688.462 rows=1 loops=1)"
> " -> Unique (cost=150.13..4943749.39 rows=32150682 width=8) (actual time=0.022..439964.214 rows=32368180 loops=1)"
> " -> Merge Join (cost=150.13..4863372.68 rows=32150682 width=8) (actual time=0.021..435818.276 rows=32368180 loops=1)"
> " Merge Cond: (table0.individual_entity_proxy_id = table1.individual_entity_proxy_id)"
> " -> Index Scan using indx_prospect_indv_entty_id on test1 table0 (cost=0.56..2493461.92 rows=32233405 width=8) (actual time=0.011..63009.551 rows=32368180 loops=1)"
> " Filter: ((direct_mail_preference IS NULL) AND ((last_contacted_dm_datetime IS NULL) OR (last_contacted_dm_datetime < '2020-03-15 00:00:00'::timestamp without time zone)) AND (shared_paddr_with_customer_ind = 'N'::bpchar) AND (profane_wrd_ind = 'N'::bpchar) AND (tmo_ofnsv_name_ind = 'N'::bpchar) AND (has_individual_address = 'Y'::bpchar) AND (has_last_name = 'Y'::bpchar) AND (has_first_name = 'Y'::bpchar))"
> " Rows Removed by Filter: 7709177"
> " -> Index Scan using pk_entity_proxy_id on test2 table1 (cost=0.56..1867677.94 rows=40071417 width=8) (actual time=0.008..363534.437 rows=40077727 loops=1)"
> " Filter: ((tax_bnkrpt_dcsd_ind = 'N'::bpchar) AND (govt_prison_ind = 'N'::bpchar) AND ((cstmr_prspct_ind)::text = 'Prospect'::text))"
> " Rows Removed by Filter: 94756"
It might help to show explain(ANALYZE,BUFFERS).
It looks like test2/table1 index scan is a lot slower than table0.
Maybe table1 gets lots of updates, so isn't clustered on its primary key, so
the index scan is highly random. You could check the "correlation" of its PK
ID column:
https://wiki.postgresql.org/wiki/Slow_Query_Questions#Statistics:_n_distinct.2C_MCV.2C_histogram
If true, that would be a good reason to have separate tables.
> vCore: 32
Possibly it would be advantageous to use parallel query.
A better query+plan might allow that.
--
Justin
^ permalink raw reply [nested|flat] 13+ messages in thread
end of thread, other threads:[~2020-06-15 00:05 UTC | newest]
Thread overview: 13+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2003-09-24 05:48 Performance issue peter <pmcgregor@advso.com>
2003-09-24 17:09 ` Joseph Bove <jbove@vetstar.com>
2003-09-24 17:05 ` Richard Jones <rj@last.fm>
2003-09-24 17:47 ` Sean Chittenden <sean@chittenden.org>
2003-09-24 18:35 ` Rod Taylor <rbt@rbt.ca>
2007-08-27 12:16 Performance issue Willo van der Merwe <willo@studentvillage.co.za>
2007-08-27 13:41 Performance issue Willo van der Merwe <willo@mirasol.co.za>
2007-08-27 14:45 ` Bill Moran <wmoran@collaborativefusion.com>
2007-08-27 15:55 ` Tom Lane <tgl@sss.pgh.pa.us>
2007-08-28 09:34 ` Willo van der Merwe <willo@mirasol.co.za>
2020-06-14 22:45 Performance issue Nagaraj Raj <nagaraj.sf@yahoo.com>
2020-06-14 23:55 ` David Rowley <dgrowleyml@gmail.com>
2020-06-15 00:05 ` Justin Pryzby <pryzby@telsasoft.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