Re: Slow response to my query - Mailing list 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
pgsql-novice by date: