Re: Slow response to my query

Hey Friends,
I want to setup a fresh Centos 7.5 OS for a new postgresql 11/12. on my dev
server that has 7 1TB SAS drives.
Kindly advise on which is better, RAID 1+0 or RAID 10 ADM or RAID 6.

I really appreciate your guide.

On Fri, Nov 29, 2019 at 4:42 PM Goke Aruna <goksie(at)gmail(dot)com> wrote:

> Thanks Steve, Jean-Yves, Keith,
>
> This is the explanation of one of my queries:
> what is your advise on this.
>
>
>
> *EXPLAIN SELECT count(*) AS aggregate FROM allcalls;*
>
> Finalize Aggregate (cost=2707819.51..2707819.52 rows=1 width=8)
> -> Gather (cost=2707819.30..2707819.51 rows=2 width=8)
> Workers Planned: 2
> -> Partial Aggregate (cost=2706819.30..2706819.31 rows=1 width=8)
> -> Parallel Append (cost=0.00..2635105.63 rows=28685466
> width=0)
> -> Parallel Seq Scan on allcalls_p20190603
> (cost=0.00..703632.78 rows=8035778 width=0)
> -> Parallel Seq Scan on allcalls_p20190611
> (cost=0.00..639557.82 rows=7182082 width=0)
> -> Parallel Seq Scan on allcalls_p20190601
> (cost=0.00..592956.85 rows=6943685 width=0)
> -> Parallel Seq Scan on allcalls_p20190602
> (cost=0.00..553063.66 rows=6514966 width=0)
> -> Parallel Seq Scan on allcalls_p20190530
> (cost=0.00..647.68 rows=9768 width=0)
> -> Parallel Seq Scan on allcalls_p20190531
> (cost=0.00..10.76 rows=76 width=0)
> -> Parallel Seq Scan on allcalls_p20190604
> (cost=0.00..10.76 rows=76 width=0)
> -> Parallel Seq Scan on allcalls_p20190605
> (cost=0.00..10.76 rows=76 width=0)
> -> Parallel Seq Scan on allcalls_p20190606
> (cost=0.00..10.76 rows=76 width=0)
> -> Parallel Seq Scan on allcalls_p20190607
> (cost=0.00..10.76 rows=76 width=0)
> -> Parallel Seq Scan on allcalls_p20190608
> (cost=0.00..10.76 rows=76 width=0)
> -> Parallel Seq Scan on allcalls_p20190609
> (cost=0.00..10.76 rows=76 width=0)
> -> Parallel Seq Scan on allcalls_p20190610
> (cost=0.00..10.76 rows=76 width=0)
> -> Parallel Seq Scan on allcalls_p20190612
> (cost=0.00..10.76 rows=76 width=0)
> -> Parallel Seq Scan on allcalls_p20190613
> (cost=0.00..10.76 rows=76 width=0)
> -> Parallel Seq Scan on allcalls_p20190615
> (cost=0.00..10.76 rows=76 width=0)
> -> Parallel Seq Scan on allcalls_p20190614
> (cost=0.00..10.71 rows=71 width=0)
> -> Parallel Seq Scan on allcalls_p20190616
> (cost=0.00..10.71 rows=71 width=0)
> -> Parallel Seq Scan on allcalls_p20190617
> (cost=0.00..10.29 rows=29 width=0)
> -> Parallel Seq Scan on allcalls_p20190501
> (cost=0.00..10.12 rows=12 width=0)
> -> Parallel Seq Scan on allcalls_p20190502
> (cost=0.00..10.12 rows=12 width=0)
> -> Parallel Seq Scan on allcalls_p20190503
> (cost=0.00..10.12 rows=12 width=0)
> -> Parallel Seq Scan on allcalls_p20190504
> (cost=0.00..10.12 rows=12 width=0)
> -> Parallel Seq Scan on allcalls_p20190505
> (cost=0.00..10.12 rows=12 width=0)
> -> Parallel Seq Scan on allcalls_p20190506
> (cost=0.00..10.12 rows=12 width=0)
> -> Parallel Seq Scan on allcalls_p20190507
> (cost=0.00..10.12 rows=12 width=0)
> -> Parallel Seq Scan on allcalls_p20190508
> (cost=0.00..10.12 rows=12 width=0)
> -> Parallel Seq Scan on allcalls_p20190509
> (cost=0.00..10.12 rows=12 width=0)
> -> Parallel Seq Scan on allcalls_p20190510
> (cost=0.00..10.12 rows=12 width=0)
> -> Parallel Seq Scan on allcalls_p20190511
> (cost=0.00..10.12 rows=12 width=0)
> -> Parallel Seq Scan on allcalls_p20190512
> (cost=0.00..10.12 rows=12 width=0)
> -> Parallel Seq Scan on allcalls_p20190513
> (cost=0.00..10.12 rows=12 width=0)
> -> Parallel Seq Scan on allcalls_p20190514
> (cost=0.00..10.12 rows=12 width=0)
> -> Parallel Seq Scan on allcalls_p20190515
> (cost=0.00..10.12 rows=12 width=0)
> -> Parallel Seq Scan on allcalls_p20190516
> (cost=0.00..10.12 rows=12 width=0)
> -> Parallel Seq Scan on allcalls_p20190517
> (cost=0.00..10.12 rows=12 width=0)
> -> Parallel Seq Scan on allcalls_p20190518
> (cost=0.00..10.12 rows=12 width=0)
> -> Parallel Seq Scan on allcalls_p20190519
> (cost=0.00..10.12 rows=12 width=0)
> -> Parallel Seq Scan on allcalls_p20190520
> (cost=0.00..10.12 rows=12 width=0)
> -> Parallel Seq Scan on allcalls_p20190521
> (cost=0.00..10.12 rows=12 width=0)
> -> Parallel Seq Scan on allcalls_p20190522
> (cost=0.00..10.12 rows=12 width=0)
> -> Parallel Seq Scan on allcalls_p20190523
> (cost=0.00..10.12 rows=12 width=0)
> -> Parallel Seq Scan on allcalls_p20190524
> (cost=0.00..10.12 rows=12 width=0)
> -> Parallel Seq Scan on allcalls_p20190525
> (cost=0.00..10.12 rows=12 width=0)
> -> Parallel Seq Scan on allcalls_p20190526
> (cost=0.00..10.12 rows=12 width=0)
> -> Parallel Seq Scan on allcalls_p20190527
> (cost=0.00..10.12 rows=12 width=0)
> -> Parallel Seq Scan on allcalls_p20190528
> (cost=0.00..10.12 rows=12 width=0)
> -> Parallel Seq Scan on allcalls_p20190529
> (cost=0.00..10.12 rows=12 width=0)
> -> Parallel Seq Scan on allcalls_p20190618
> (cost=0.00..10.12 rows=12 width=0)
> -> Parallel Seq Scan on allcalls_p20190619
> (cost=0.00..10.12 rows=12 width=0)
> -> Parallel Seq Scan on allcalls_p20190620
> (cost=0.00..10.12 rows=12 width=0)
> -> Parallel Seq Scan on allcalls_p20190621
> (cost=0.00..10.12 rows=12 width=0)
> -> Parallel Seq Scan on allcalls_p20190622
> (cost=0.00..10.12 rows=12 width=0)
> -> Parallel Seq Scan on allcalls_p20190623
> (cost=0.00..10.12 rows=12 width=0)
> -> Parallel Seq Scan on allcalls_p20190624
> (cost=0.00..10.12 rows=12 width=0)
> -> Parallel Seq Scan on allcalls_p20190625
> (cost=0.00..10.12 rows=12 width=0)
> -> Parallel Seq Scan on allcalls_p20190626
> (cost=0.00..10.12 rows=12 width=0)
> -> Parallel Seq Scan on allcalls_p20190627
> (cost=0.00..10.12 rows=12 width=0)
> -> Parallel Seq Scan on allcalls_p20190628
> (cost=0.00..10.12 rows=12 width=0)
> -> Parallel Seq Scan on allcalls_p20190629
> (cost=0.00..10.12 rows=12 width=0)
> -> Parallel Seq Scan on allcalls_p20190630
> (cost=0.00..10.12 rows=12 width=0)
> -> Parallel Seq Scan on allcalls_p20190701
> (cost=0.00..10.12 rows=12 width=0)
> -> Parallel Seq Scan on allcalls_p20190702
> (cost=0.00..10.12 rows=12 width=0)
> -> Parallel Seq Scan on allcalls_p20190703
> (cost=0.00..10.12 rows=12 width=0)
> -> Parallel Seq Scan on allcalls_p20190704
> (cost=0.00..10.12 rows=12 width=0)
> -> Parallel Seq Scan on allcalls_p20190705
> (cost=0.00..10.12 rows=12 width=0)
> -> Parallel Seq Scan on allcalls_p20190706
> (cost=0.00..10.12 rows=12 width=0)
> -> Parallel Seq Scan on allcalls_p20190707
> (cost=0.00..10.12 rows=12 width=0)
> -> Parallel Seq Scan on allcalls_p20190708
> (cost=0.00..10.12 rows=12 width=0)
> -> Parallel Seq Scan on allcalls_p20190709
> (cost=0.00..10.12 rows=12 width=0)
> -> Parallel Seq Scan on allcalls_p20190710
> (cost=0.00..10.12 rows=12 width=0)
> -> Parallel Seq Scan on allcalls_p20190711
> (cost=0.00..10.12 rows=12 width=0)
> -> Parallel Seq Scan on allcalls_p20190712
> (cost=0.00..10.12 rows=12 width=0)
> -> Parallel Seq Scan on allcalls_p20190713
> (cost=0.00..10.12 rows=12 width=0)
> -> Parallel Seq Scan on allcalls_p20190714
> (cost=0.00..10.12 rows=12 width=0)
> -> Parallel Seq Scan on allcalls_p20190715
> (cost=0.00..10.12 rows=12 width=0)
> -> Parallel Seq Scan on allcalls_p20190716
> (cost=0.00..10.12 rows=12 width=0)
> -> Parallel Seq Scan on allcalls_p20190717
> (cost=0.00..10.12 rows=12 width=0)
> -> Parallel Seq Scan on allcalls_p20190718
> (cost=0.00..10.12 rows=12 width=0)
> -> Parallel Seq Scan on allcalls_p20190719
> (cost=0.00..10.12 rows=12 width=0)
> -> Parallel Seq Scan on allcalls_p20190720
> (cost=0.00..10.12 rows=12 width=0)
> -> Parallel Seq Scan on allcalls_p20190721
> (cost=0.00..10.12 rows=12 width=0)
> -> Parallel Seq Scan on allcalls_p20190722
> (cost=0.00..10.12 rows=12 width=0)
> -> Parallel Seq Scan on allcalls_p20190723
> (cost=0.00..10.12 rows=12 width=0)
> -> Parallel Seq Scan on allcalls_p20190724
> (cost=0.00..10.12 rows=12 width=0)
> -> Parallel Seq Scan on allcalls_p20190725
> (cost=0.00..10.12 rows=12 width=0)
> -> Parallel Seq Scan on allcalls_p20190726
> (cost=0.00..10.12 rows=12 width=0)
> -> Parallel Seq Scan on allcalls_p20190727
> (cost=0.00..10.12 rows=12 width=0)
> -> Parallel Seq Scan on allcalls_p20190728
> (cost=0.00..10.12 rows=12 width=0)
> -> Parallel Seq Scan on allcalls_p20190729
> (cost=0.00..10.12 rows=12 width=0)
> -> Parallel Seq Scan on allcalls_p20190730
> (cost=0.00..10.12 rows=12 width=0)
> -> Parallel Seq Scan on allcalls_p20190731
> (cost=0.00..10.12 rows=12 width=0)
> -> Parallel Seq Scan on allcalls_p20190801
> (cost=0.00..10.12 rows=12 width=0)
> -> Parallel Seq Scan on allcalls_p20190802
> (cost=0.00..10.12 rows=12 width=0)
> -> Parallel Seq Scan on allcalls_p20190803
> (cost=0.00..10.12 rows=12 width=0)
> -> Parallel Seq Scan on allcalls_p20190804
> (cost=0.00..10.12 rows=12 width=0)
> -> Parallel Seq Scan on allcalls_p20190805
> (cost=0.00..10.12 rows=12 width=0)
> -> Parallel Seq Scan on allcalls_p20190806
> (cost=0.00..10.12 rows=12 width=0)
> -> Parallel Seq Scan on allcalls_p20190807
> (cost=0.00..10.12 rows=12 width=0)
> -> Parallel Seq Scan on allcalls_p20190808
> (cost=0.00..10.12 rows=12 width=0)
> -> Parallel Seq Scan on allcalls_p20190809
> (cost=0.00..10.12 rows=12 width=0)
> -> Parallel Seq Scan on allcalls_p20190810
> (cost=0.00..10.12 rows=12 width=0)
> -> Parallel Seq Scan on allcalls_p20190811
> (cost=0.00..10.12 rows=12 width=0)
> -> Parallel Seq Scan on allcalls_p20190812
> (cost=0.00..10.12 rows=12 width=0)
> -> Parallel Seq Scan on allcalls_p20190813
> (cost=0.00..10.12 rows=12 width=0)
> -> Parallel Seq Scan on allcalls_p20190814
> (cost=0.00..10.12 rows=12 width=0)
> -> Parallel Seq Scan on allcalls_p20190815
> (cost=0.00..10.12 rows=12 width=0)
> -> Parallel Seq Scan on allcalls_p20190816
> (cost=0.00..10.12 rows=12 width=0)
> -> Parallel Seq Scan on allcalls_p20190817
> (cost=0.00..10.12 rows=12 width=0)
> -> Parallel Seq Scan on allcalls_p20190818
> (cost=0.00..10.12 rows=12 width=0)
> -> Parallel Seq Scan on allcalls_p20190819
> (cost=0.00..10.12 rows=12 width=0)
> -> Parallel Seq Scan on allcalls_p20190820
> (cost=0.00..10.12 rows=12 width=0)
> -> Parallel Seq Scan on allcalls_p20190821
> (cost=0.00..10.12 rows=12 width=0)
> -> Parallel Seq Scan on allcalls_p20190822
> (cost=0.00..10.12 rows=12 width=0)
> -> Parallel Seq Scan on allcalls_p20190823
> (cost=0.00..10.12 rows=12 width=0)
> -> Parallel Seq Scan on allcalls_p20190824
> (cost=0.00..10.12 rows=12 width=0)
> -> Parallel Seq Scan on allcalls_p20190825
> (cost=0.00..10.12 rows=12 width=0)
> -> Parallel Seq Scan on allcalls_p20190826
> (cost=0.00..10.12 rows=12 width=0)
> -> Parallel Seq Scan on allcalls_p20190827
> (cost=0.00..10.12 rows=12 width=0)
> -> Parallel Seq Scan on allcalls_p20190828
> (cost=0.00..10.12 rows=12 width=0)
> -> Parallel Seq Scan on allcalls_p20190829
> (cost=0.00..10.12 rows=12 width=0)
> -> Parallel Seq Scan on allcalls_p20190830
> (cost=0.00..10.12 rows=12 width=0)
> -> Parallel Seq Scan on allcalls_p20190831
> (cost=0.00..10.12 rows=12 width=0)
> -> Parallel Seq Scan on allcalls_p20190901
> (cost=0.00..10.12 rows=12 width=0)
> -> Parallel Seq Scan on allcalls_p20190902
> (cost=0.00..10.12 rows=12 width=0)
> -> Parallel Seq Scan on allcalls_p20190903
> (cost=0.00..10.12 rows=12 width=0)
> -> Parallel Seq Scan on allcalls_p20190904
> (cost=0.00..10.12 rows=12 width=0)
> -> Parallel Seq Scan on allcalls_p20190905
> (cost=0.00..10.12 rows=12 width=0)
> -> Parallel Seq Scan on allcalls_p20190906
> (cost=0.00..10.12 rows=12 width=0)
> -> Parallel Seq Scan on allcalls_p20190907
> (cost=0.00..10.12 rows=12 width=0)
> -> Parallel Seq Scan on allcalls_p20190908
> (cost=0.00..10.12 rows=12 width=0)
> -> Parallel Seq Scan on allcalls_p20190909
> (cost=0.00..10.12 rows=12 width=0)
> -> Parallel Seq Scan on allcalls_p20190910
> (cost=0.00..10.12 rows=12 width=0)
> -> Parallel Seq Scan on allcalls_p20190911
> (cost=0.00..10.12 rows=12 width=0)
> -> Parallel Seq Scan on allcalls_p20190912
> (cost=0.00..10.12 rows=12 width=0)
> -> Parallel Seq Scan on allcalls_p20190913
> (cost=0.00..10.12 rows=12 width=0)
> -> Parallel Seq Scan on allcalls_p20190914
> (cost=0.00..10.12 rows=12 width=0)
> -> Parallel Seq Scan on allcalls_p20190915
> (cost=0.00..10.12 rows=12 width=0)
> -> Parallel Seq Scan on allcalls_p20190916
> (cost=0.00..10.12 rows=12 width=0)
> -> Parallel Seq Scan on allcalls_p20190917
> (cost=0.00..10.12 rows=12 width=0)
> -> Parallel Seq Scan on allcalls_p20190918
> (cost=0.00..10.12 rows=12 width=0)
> -> Parallel Seq Scan on allcalls_p20190919
> (cost=0.00..10.12 rows=12 width=0)
> -> Parallel Seq Scan on allcalls_p20190920
> (cost=0.00..10.12 rows=12 width=0)
> -> Parallel Seq Scan on allcalls_p20190921
> (cost=0.00..10.12 rows=12 width=0)
> -> Parallel Seq Scan on allcalls_p20190922
> (cost=0.00..10.12 rows=12 width=0)
> -> Parallel Seq Scan on allcalls_p20190923
> (cost=0.00..10.12 rows=12 width=0)
> -> Parallel Seq Scan on allcalls_p20190924
> (cost=0.00..10.12 rows=12 width=0)
> -> Parallel Seq Scan on allcalls_p20190925
> (cost=0.00..10.12 rows=12 width=0)
> -> Parallel Seq Scan on allcalls_p20190926
> (cost=0.00..10.12 rows=12 width=0)
> -> Parallel Seq Scan on allcalls_p20190927
> (cost=0.00..10.12 rows=12 width=0)
> -> Parallel Seq Scan on allcalls_p20190928
> (cost=0.00..10.12 rows=12 width=0)
> -> Parallel Seq Scan on allcalls_p20190929
> (cost=0.00..10.12 rows=12 width=0)
> -> Parallel Seq Scan on allcalls_p20190930
> (cost=0.00..10.12 rows=12 width=0)
> -> Parallel Seq Scan on allcalls_p20191001
> (cost=0.00..10.12 rows=12 width=0)
> -> Parallel Seq Scan on allcalls_p20191002
> (cost=0.00..10.12 rows=12 width=0)
> -> Parallel Seq Scan on allcalls_p20191003
> (cost=0.00..10.12 rows=12 width=0)
> -> Parallel Seq Scan on allcalls_p20191004
> (cost=0.00..10.12 rows=12 width=0)
> -> Parallel Seq Scan on allcalls_p20191005
> (cost=0.00..10.12 rows=12 width=0)
> -> Parallel Seq Scan on allcalls_p20191006
> (cost=0.00..10.12 rows=12 width=0)
> -> Parallel Seq Scan on allcalls_p20191007
> (cost=0.00..10.12 rows=12 width=0)
> -> Parallel Seq Scan on allcalls_p20191008
> (cost=0.00..10.12 rows=12 width=0)
> -> Parallel Seq Scan on allcalls_p20191009
> (cost=0.00..10.12 rows=12 width=0)
> -> Parallel Seq Scan on allcalls_p20191010
> (cost=0.00..10.12 rows=12 width=0)
> -> Parallel Seq Scan on allcalls_p20191011
> (cost=0.00..10.12 rows=12 width=0)
> -> Parallel Seq Scan on allcalls_p20191012
> (cost=0.00..10.12 rows=12 width=0)
> -> Parallel Seq Scan on allcalls_p20191013
> (cost=0.00..10.12 rows=12 width=0)
> -> Parallel Seq Scan on allcalls_p20191014
> (cost=0.00..10.12 rows=12 width=0)
> -> Parallel Seq Scan on allcalls_p20191015
> (cost=0.00..10.12 rows=12 width=0)
> -> Parallel Seq Scan on allcalls_p20191016
> (cost=0.00..10.12 rows=12 width=0)
> -> Parallel Seq Scan on allcalls_p20191017
> (cost=0.00..10.12 rows=12 width=0)
> -> Parallel Seq Scan on allcalls_p20191018
> (cost=0.00..10.12 rows=12 width=0)
> -> Parallel Seq Scan on allcalls_p20191019
> (cost=0.00..10.12 rows=12 width=0)
> -> Parallel Seq Scan on allcalls_p20191020
> (cost=0.00..10.12 rows=12 width=0)
> -> Parallel Seq Scan on allcalls_p20191021
> (cost=0.00..10.12 rows=12 width=0)
> -> Parallel Seq Scan on allcalls_p20191022
> (cost=0.00..10.12 rows=12 width=0)
> -> Parallel Seq Scan on allcalls_p20191023
> (cost=0.00..10.12 rows=12 width=0)
> -> Parallel Seq Scan on allcalls_p20191024
> (cost=0.00..10.12 rows=12 width=0)
> -> Parallel Seq Scan on allcalls_p20191025
> (cost=0.00..10.12 rows=12 width=0)
> -> Parallel Seq Scan on allcalls_p20191026
> (cost=0.00..10.12 rows=12 width=0)
> -> Parallel Seq Scan on allcalls_p20191027
> (cost=0.00..10.12 rows=12 width=0)
> -> Parallel Seq Scan on allcalls_p20191028
> (cost=0.00..10.12 rows=12 width=0)
> -> Parallel Seq Scan on allcalls_p20191029
> (cost=0.00..10.12 rows=12 width=0)
> -> Parallel Seq Scan on allcalls_p20191030
> (cost=0.00..10.12 rows=12 width=0)
> -> Parallel Seq Scan on allcalls_p20191031
> (cost=0.00..10.12 rows=12 width=0)
>
> On Fri, Nov 29, 2019 at 4:23 PM Bzzzz <lazyvirus(at)gmx(dot)com> wrote:
>
>> On Fri, 29 Nov 2019 10:01:16 -0500
>> Keith <keith(at)keithf4(dot)com> wrote:
>>
>> > Please do not advise the use of inheritance/trigger based partitioning
>> > anymore, especially on PG12. Partitioning is now part of core and is
>> > much more easily managed that way
>> >
>> > https://www.postgresql.org/docs/12/ddl-partitioning.html
>>
>> Thanks Keith, I missed this one :/
>>
>> JY
>>
>