Re: Slow response to my query
Goke Aruna <goksie@gmail.com>
From: Goke Aruna <goksie@gmail.com>
To: Bzzzz <lazyvirus@gmx.com>
Cc: Keith <keith@keithf4.com>, Steven Pousty <steve.pousty@gmail.com>, pgsql-novice@lists.postgresql.org, Babatunde Adeyemi <barbietunnie@gmail.com>
Date: 2019-11-29T15:42:37Z
Lists: pgsql-novice
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@gmx.com> wrote:
> On Fri, 29 Nov 2019 10:01:16 -0500
> Keith <keith@keithf4.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
>