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:47:44Z
Lists: pgsql-novice
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@gmail.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@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 >> >