1

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?

James Z
  • 12,209
  • 10
  • 24
  • 44
YogeshR
  • 1,606
  • 2
  • 22
  • 43
  • 1
    The plan is perfect, and 1 second to get 300000 rows from joining 6 tables is excellent. – Laurenz Albe Jul 31 '18 at 14:15
  • 1
    Assuming that the total size of the six rows is 1Kb, you are trying to retrieve about 300Mb of data. It is equal to about 10 minutes of HD video or about 300 minutes of mp3 music... – Abelisto Jul 31 '18 at 14:40

0 Answers0