I am running below PostgreSQL query
select * from
(Select * from person where lastmodifieddate >= '2018-06-20' and
lastmodifieddate <= '2018-06-25')p
left join persondetail pd on p.personid=pd.personid
left join personalert pa on p.personid=pa.personid
left join personstatus ps on p.personid=ps.personid
left join phone pho on p.personid=pho.personid
left join identification id on p.personid=id.personid
below is the execution plan of this query
Merge Left Join (cost=1.83..27414.97 rows=231867 width=782) (actual time=0.053..1039.895 rows=334185 loops=1)
Merge Cond: (person.personid = ps.personid)
-> Merge Left Join (cost=1.41..13323.99 rows=62319 width=703) (actual time=0.043..311.036 rows=45127 loops=1)
Merge Cond: (person.personid = pd.personid)
-> Merge Left Join (cost=0.99..4387.99 rows=21408 width=492) (actual time=0.032..116.078 rows=21443 loops=1)
Merge Cond: (person.personid = id.personid)
-> Merge Left Join (cost=0.71..3883.57 rows=21408 width=423) (actual time=0.026..86.285 rows=21366 loops=1)
Merge Cond: (person.personid = pho.personid)
-> Merge Left Join (cost=0.56..3804.59 rows=21408 width=357) (actual time=0.019..66.817 rows=21259 loops=1)
Merge Cond: (person.personid = pa.personid)
-> Index Scan using pk_person_personid on person (cost=0.29..3711.19 rows=21408 width=292) (actual time=0.011..46.957 rows=21200 loops=1)
Filter: ((lastmodifieddate >= '2018-06-20 00:00:00+00'::timestamp with time zone) AND (lastmodifieddate <= '2018-06-25 00:00:00+00'::timestamp with time zone))
Rows Removed by Filter: 15145
-> Index Scan using ix_personalert_personid on personalert pa (cost=0.27..37.15 rows=326 width=65) (actual time=0.005..0.379 rows=326 loops=1)
-> Index Scan using ix_phone_personid on phone pho (cost=0.14..23.47 rows=237 width=66) (actual time=0.004..0.252 rows=238 loops=1)
-> Index Scan using ix_identification_personid on identification id (cost=0.28..406.91 rows=5243 width=69) (actual time=0.005..6.719 rows=5340 loops=1)
-> Index Scan using ix_persondetail_personid on persondetail pd (cost=0.42..7994.79 rows=105800 width=211) (actual time=0.005..112.026 rows=106414 loops=1)
-> Materialize (cost=0.42..10795.24 rows=135227 width=79) (actual time=0.007..341.461 rows=423593 loops=1)
-> Index Scan using ix_personstatus_personid on personstatus ps (cost=0.42..10457.17 rows=135227 width=79) (actual time=0.005..121.219 rows=135220 loops=1)
Planning time: 1.125 ms
Execution time: 1161.237 ms
I have created indexes on personid columns in all tables used in the query. still, the query is taking a lot of time.
Size of Shared_buffer for PostgreSQL is 2GB and Effective_cache is 3GB. Total System memory is 8GB.
I am getting almost 350 000 records from this query.
How can i improve its performance?