Horton

Thread

  1. Slow response to my query

    Goke Aruna <goksie@gmail.com> — 2019-11-28T04:35:44Z

    I have a HP DL380 server  g8 with 32GB RAM and 48 *2.5Ghz CPU. Running on
CentOS Linux 7.4.

I have a postgresql 12 database. One of my tables has 42 columns and has
about 500million records.
However, any attempt to select even 1 column from the table take hours at
least 1.5hours to respond.

Kindly help on documentation on how to optimize it to return the response
in milliseconds.

Regards

  2. Re: Slow response to my query

    Bzzzz <lazyvirus@gmx.com> — 2019-11-28T04:42:13Z

    On Thu, 28 Nov 2019 05:35:44 +0100
Goke Aruna <goksie@gmail.com> wrote:

Hi,

> I have a HP DL380 server  g8 with 32GB RAM and 48 *2.5Ghz CPU. Running
> on CentOS Linux 7.4.
>
> I have a postgresql 12 database. One of my tables has 42 columns and
> has about 500million records.
> However, any attempt to select even 1 column from the table take hours
> at least 1.5hours to respond.

Did you test your installation with pgbench?

> Kindly help on documentation on how to optimize it to return the
> response in milliseconds.

Do you have any indexes?

What is your test query?

Jean-Yves

  3. Re: Slow response to my query

    Goke Aruna <goksie@gmail.com> — 2019-11-28T09:38:22Z

    Thanks Jean-Yves,
I will install pgbench and revert.
however, do you have ant specific test to run with the pgbench?

I have about 6 of my columns indexed.

I will share the test query once i got the queries from the UI man.

Regards

On Thu, Nov 28, 2019 at 5:42 AM Bzzzz <lazyvirus@gmx.com> wrote:

> On Thu, 28 Nov 2019 05:35:44 +0100
> Goke Aruna <goksie@gmail.com> wrote:
>
> Hi,
>
> > I have a HP DL380 server  g8 with 32GB RAM and 48 *2.5Ghz CPU. Running
> > on CentOS Linux 7.4.
> >
> > I have a postgresql 12 database. One of my tables has 42 columns and
> > has about 500million records.
> > However, any attempt to select even 1 column from the table take hours
> > at least 1.5hours to respond.
>
> Did you test your installation with pgbench?
>
> > Kindly help on documentation on how to optimize it to return the
> > response in milliseconds.
>
> Do you have any indexes?
>
> What is your test query?
>
> Jean-Yves
>

  4. Re: Slow response to my query

    Bzzzz <lazyvirus@gmx.com> — 2019-11-28T15:13:19Z

    On Thu, 28 Nov 2019 10:38:22 +0100
Goke Aruna <goksie@gmail.com> wrote:

> however, do you have ant specific test to run with the pgbench?

Nope, I don't have the man in mind - read it and adapt your test to your
problem.

BTW, you did not say it, but I suppose you're using rust not SSDz?

> I have about 6 of my columns indexed.

Are they involved in your query?

> I will share the test query once i got the queries from the UI man.

? Without the table and it's indexes structures plus the query, it's like
a car without an engine (or a chauffeur)…

Also, read this:
https://hakibenita.com/be-careful-with-cte-in-postgre-sql
and that:
https://www.2ndquadrant.com/en/blog/on-rocks-and-sand/

Jean-Yves

  5. Re: Slow response to my query

    Steven Pousty <steve.pousty@gmail.com> — 2019-11-28T16:11:43Z

    Sounds like you should do an
EXPLAIN
in front of your query and see what the query planner is thinking. If you
can spare the 1.5 hours do an EXPLAIN ANALYZE. It should be safe as long as
you are only doing a select query.
Thanks
Steve

On Thu, Nov 28, 2019 at 7:13 AM Bzzzz <lazyvirus@gmx.com> wrote:

> On Thu, 28 Nov 2019 10:38:22 +0100
> Goke Aruna <goksie@gmail.com> wrote:
>
> > however, do you have ant specific test to run with the pgbench?
>
> Nope, I don't have the man in mind - read it and adapt your test to your
> problem.
>
> BTW, you did not say it, but I suppose you're using rust not SSDz?
>
> > I have about 6 of my columns indexed.
>
> Are they involved in your query?
>
> > I will share the test query once i got the queries from the UI man.
>
> ? Without the table and it's indexes structures plus the query, it's like
> a car without an engine (or a chauffeur)…
>
> Also, read this:
> https://hakibenita.com/be-careful-with-cte-in-postgre-sql
> and that:
> https://www.2ndquadrant.com/en/blog/on-rocks-and-sand/
>
> Jean-Yves
>
>
>

  6. Re: Slow response to my query

    Goke Aruna <goksie@gmail.com> — 2019-11-29T11:09:04Z

    The output of the pgbench on the database is as contained below:

The* drive is SSD* and the command that is mostly used is

   1. *select in_carrier, og_carrier, sum(ceil_duration) as
   ceil_duration_aggr from "allcalls" where "call_type" = 'INTL' and
   "og_carrier" in ('9MOBILE', 'AIRTEL', 'GLO', 'MTN') and extract(month from
   "callday") = '11' and extract(year from "callday") = 2019 group by
   "in_carrier", "og_carrier"*
   2. * select in_carrier, og_carrier, sum(ceil_duration) as
   ceil_duration_aggr from "allcalls" where extract(month from "callday") =
   '11' and extract(year from "callday") = 2019 group by "in_carrier",
   "og_carrier" *


*PGBENCH*
bash-4.2$  pgbench -c 5 -j 2 -t 20000 mybill
starting vacuum...end.
transaction type: <builtin: TPC-B (sort of)>
scaling factor: 1
query mode: simple
number of clients: 5
number of threads: 2
number of transactions per client: 20000
number of transactions actually processed: 100000/100000
latency average = 0.796 ms
tps = *6281*.067340 (including connections establishing)
tps = *6282*.242375 (excluding connections establishing)

bash-4.2$  pgbench -c 5 -j 2 -t 200000 mybill
starting vacuum...end.
transaction type: <builtin: TPC-B (sort of)>
scaling factor: 1
query mode: simple
number of clients: 5
number of threads: 2
number of transactions per client: 200000
number of transactions actually processed: 1000000/1000000
latency average = 0.846 ms
tps = *5911*.740108 (including connections establishing)
tps =* 5911*.854614 (excluding connections establishing)



On Thu, Nov 28, 2019 at 5:12 PM Steven Pousty <steve.pousty@gmail.com>
wrote:

> Sounds like you should do an
> EXPLAIN
> in front of your query and see what the query planner is thinking. If you
> can spare the 1.5 hours do an EXPLAIN ANALYZE. It should be safe as long as
> you are only doing a select query.
> Thanks
> Steve
>
> On Thu, Nov 28, 2019 at 7:13 AM Bzzzz <lazyvirus@gmx.com> wrote:
>
>> On Thu, 28 Nov 2019 10:38:22 +0100
>> Goke Aruna <goksie@gmail.com> wrote:
>>
>> > however, do you have ant specific test to run with the pgbench?
>>
>> Nope, I don't have the man in mind - read it and adapt your test to your
>> problem.
>>
>> BTW, you did not say it, but I suppose you're using rust not SSDz?
>>
>> > I have about 6 of my columns indexed.
>>
>> Are they involved in your query?
>>
>> > I will share the test query once i got the queries from the UI man.
>>
>> ? Without the table and it's indexes structures plus the query, it's like
>> a car without an engine (or a chauffeur)…
>>
>> Also, read this:
>> https://hakibenita.com/be-careful-with-cte-in-postgre-sql
>> and that:
>> https://www.2ndquadrant.com/en/blog/on-rocks-and-sand/
>>
>> Jean-Yves
>>
>>
>>

  7. Re: Slow response to my query

    Bzzzz <lazyvirus@gmx.com> — 2019-11-29T14:09:44Z

    On Fri, 29 Nov 2019 12:09:04 +0100
Goke Aruna <goksie@gmail.com> wrote:

> The* drive is SSD* and the command that is mostly used is

Ok.
 
>    1. *select in_carrier, og_carrier, sum(ceil_duration) as
>    ceil_duration_aggr from "allcalls" where "call_type" = 'INTL' and
>    "og_carrier" in ('9MOBILE', 'AIRTEL', 'GLO', 'MTN') and
> extract(month from "callday") = '11' and extract(year from "callday")
> = 2019 group by "in_carrier", "og_carrier"*
>
>
> SELECT in_carrier, og_carrier,
>     SUM(ceil_duration) AS ceil_duration_aggr
> FROM allcalls
> WHERE call_type = 'INTL'
>     AND og_carrier IN ('9MOBILE', 'AIRTEL', 'GLO', 'MTN')
>     AND EXTRACT(month FROM callday) = 11
>     AND EXTRACT(year FROM callday) = 2019
> GROUP BY in_carrier, og_carrier

Why are regular [lower case] columns identifiers in between double-quotes
that are normally used to process weird column names such as :
"CamelColName"?
(takes time to process.)

Why are call_type & og_carrier in the plain instead of being foreign
keys?
(integer are usually processed faster, not to mention carriers names
have good chances to be used elsewhere in the DB.)

Why do you force an implicite cast of a double precision float
(pg_typeof(EXTRACT(month FROM callday))) to a string ('11') in your
comparison??
(cast is a very costly operation)

As the EXTRACT/2 Fn is very much used & you have 500M rows (wild
guess as it is a phone carrier app: still growing), you logically have
created all corresponding indexes on your table??
(if you where using Pg V.12, you would be able to create auto-generated
columns auto-calculating these values, which wouldn't spare you the
indexes, but would definitely spare the EXTRACT/2 calculation time
which is very costly in this context.)

>    2. * select in_carrier, og_carrier, sum(ceil_duration) as
>    ceil_duration_aggr from "allcalls" where extract(month from
> "callday") = '11' and extract(year from "callday") = 2019 group by
> "in_carrier", "og_carrier" *
> 
> 
> SELECT in_carrier, og_carrier,
>     SUM(ceil_duration) AS ceil_duration_aggr
> FROM allcalls
> WHERE EXTRACT(month FROM callday) = 11
>     AND EXTRACT(year FROM callday) = 2019
> GROUP BY in_carrier, og_carrier

Same questions as above.

> *PGBENCH*
[…]
Seems quite correct for a SSD.

> > Sounds like you should do an
> > EXPLAIN
> > in front of your query and see what the query planner is thinking.
> > If you can spare the 1.5 hours do an EXPLAIN ANALYZE. It should be
> > safe as long as you are only doing a select query.
> > Thanks
> > Steve

Steve's point is the next step.

Jean-Yves

  8. Re: Slow response to my query

    Bzzzz <lazyvirus@gmx.com> — 2019-11-29T14:21:25Z

    On Fri, 29 Nov 2019 12:09:04 +0100
Goke Aruna <goksie@gmail.com> wrote:

Oops, I forgot,

as you're not using V.12, it might not be a bad idea to create  new
columns and trigger(s) to pre-calculate all of the EXTRACT/2 you need.

Jean-Yves

  9. Re: Slow response to my query

    Goke Aruna <goksie@gmail.com> — 2019-11-29T14:38:53Z

    Thanks Jean-Yves / Steven,
am using V12.

On Fri, Nov 29, 2019 at 3:21 PM Bzzzz <lazyvirus@gmx.com> wrote:

> On Fri, 29 Nov 2019 12:09:04 +0100
> Goke Aruna <goksie@gmail.com> wrote:
>
> Oops, I forgot,
>
> as you're not using V.12, it might not be a bad idea to create  new
> columns and trigger(s) to pre-calculate all of the EXTRACT/2 you need.
>
> Jean-Yves
>

  10. Re: Slow response to my query

    Bzzzz <lazyvirus@gmx.com> — 2019-11-29T14:56:28Z

    On Fri, 29 Nov 2019 15:38:53 +0100
Goke Aruna <goksie@gmail.com> wrote:

> Thanks Jean-Yves / Steven,
> am using V12.

Depending on the way you mostly use your columns, you can also do the
opposite: store date/timestamp into "pieces" in different columns
(century, month, day, hours, minutes, seconds, milliseconds, time zone
shift) and reconstitute a whole date/ts into an auto-generated column.

Sometimes, you have to break the rules, especially with a large number of
rows, because pre-calculation is way better than post in this case.

Depending on your needs, you might also be interested into that:
https://severalnines.com/database-blog/guide-partitioning-data-postgresql

BTW, don't leave us dry, share your progress and results *<;-)

Jean-Yves

> On Fri, Nov 29, 2019 at 3:21 PM Bzzzz <lazyvirus@gmx.com> wrote:
>
> > On Fri, 29 Nov 2019 12:09:04 +0100
> > Goke Aruna <goksie@gmail.com> wrote:
> >
> > Oops, I forgot,
> >
> > as you're not using V.12, it might not be a bad idea to create  new
> > columns and trigger(s) to pre-calculate all of the EXTRACT/2 you
> > need.
> >
> > Jean-Yves
> >

  11. Re: Slow response to my query

    Keith <keith@keithf4.com> — 2019-11-29T15:01:16Z

    On Fri, Nov 29, 2019 at 9:56 AM Bzzzz <lazyvirus@gmx.com> wrote:

> On Fri, 29 Nov 2019 15:38:53 +0100
> Goke Aruna <goksie@gmail.com> wrote:
>
> > Thanks Jean-Yves / Steven,
> > am using V12.
>
> Depending on the way you mostly use your columns, you can also do the
> opposite: store date/timestamp into "pieces" in different columns
> (century, month, day, hours, minutes, seconds, milliseconds, time zone
> shift) and reconstitute a whole date/ts into an auto-generated column.
>
> Sometimes, you have to break the rules, especially with a large number of
> rows, because pre-calculation is way better than post in this case.
>
> Depending on your needs, you might also be interested into that:
> https://severalnines.com/database-blog/guide-partitioning-data-postgresql
>
> BTW, don't leave us dry, share your progress and results *<;-)
>
> Jean-Yves
>

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

  12. Re: Slow response to my query

    Bzzzz <lazyvirus@gmx.com> — 2019-11-29T15:23:37Z

    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

  13. Re: Slow response to my query

    Goke Aruna <goksie@gmail.com> — 2019-11-29T15:42:37Z

    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
>

  14. Re: Slow response to my query

    Goke Aruna <goksie@gmail.com> — 2019-11-29T15:47:44Z

     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
>>
>

  15. Re: Slow response to my query

    Bzzzz <lazyvirus@gmx.com> — 2019-11-29T16:19:10Z

    On Fri, 29 Nov 2019 16:47:44 +0100
Goke Aruna <goksie@gmail.com> wrote:

> 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.

It depends on many parms, ie:

are you debit or IOPS bounded,
how fast is your DB growing,
how many simultaneous accesses you have/need,
what is the distribution between reads & writes,
what is the alignement & size of one row,
etc.

After that,
which stripe size,
etc.

Once your speed problem will be definitely solved, you might also
consider ZFS, it is a bit slower than RAID but it adds data integrity to
data redundancy (but it need fiddling sometimes.)

> > *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

I'm not accustomed to partitioning, but all your scans are <Seq>uentials,
so you might miss one/some index(es) - specialists will tell you more
about that, but from what I skimmed from Keith link, my contentionis
you're missing index(es.)

Also, into Debian, there's a: "postgresql-12-hypopg" package that brings
an extension with which you can create hypothetical indexes which in turn
will tell you if your queries need the real ones or not, this should
also exist into your distro and could help.

JY

  16. Re: Slow response to my query

    Goke Aruna <goksie@gmail.com> — 2019-11-29T16:35:03Z

    This is great, thank you so much!

are you debit or IOPS bounded,
     IOPS
how fast is your DB growing,
     Like 1.5GB per hour
how many simultaneous accesses you have/need,
     Application - 5 and probably additional 5 for operator.
what is the distribution between reads & writes,
        70 read while 30 writ
what is the alignement & size of one row,
      Most used table has 42 columns while next after it has 18 columns
etc.

After that,
which stripe size, 32

Thanks for your guide

On Fri, 29 Nov 2019, 5:19 PM Bzzzz <lazyvirus@gmx.com> wrote:

> On Fri, 29 Nov 2019 16:47:44 +0100
> Goke Aruna <goksie@gmail.com> wrote:
>
> > 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.
>
> It depends on many parms, ie:
>
> are you debit or IOPS bounded,
> how fast is your DB growing,
> how many simultaneous accesses you have/need,
> what is the distribution between reads & writes,
> what is the alignement & size of one row,
> etc.
>
> After that,
> which stripe size,
> etc.
>
> Once your speed problem will be definitely solved, you might also
> consider ZFS, it is a bit slower than RAID but it adds data integrity to
> data redundancy (but it need fiddling sometimes.)
>
> > > *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
>
> I'm not accustomed to partitioning, but all your scans are <Seq>uentials,
> so you might miss one/some index(es) - specialists will tell you more
> about that, but from what I skimmed from Keith link, my contentionis
> you're missing index(es.)
>
> Also, into Debian, there's a: "postgresql-12-hypopg" package that brings
> an extension with which you can create hypothetical indexes which in turn
> will tell you if your queries need the real ones or not, this should
> also exist into your distro and could help.
>
> JY
>

  17. Re: Slow response to my query

    Bzzzz <lazyvirus@gmx.com> — 2019-11-29T17:13:15Z

    On Fri, 29 Nov 2019 17:35:03 +0100
Goke Aruna <goksie@gmail.com> wrote:

> are you debit or IOPS bounded,
>      IOPS

Then prefer 1|10, others are bounded to the R/W speed of only the
slowest disk while 1|10 is distributed among 1/2 disk Nb, so faster for
reads.
NB: This is less true with ZFS as other factors are at work.

Note that if you plan to use ZFS, extending an array of disks will mean
most of new writings will happen only onto the new disks until they reach
the same filling level than others - often crucial whatever the RAIDZ
level is.

Also do not forget about backupS!

> how fast is your DB growing,
>      Like 1.5GB per hour

Hmm, this lead to a bit more than 1TB/month30d, you'll either need more
SSDz or larger.

This is here that you can see carriers' names in the plain is
eating too place for nothing: one regular INTEGER (int4) = 4 bytes
(used as a foreign key) or even less if you do not have a lot of
carriers and the y fit into 32,768 (small int), when each character is
_at least_ one byte and often more in UTF8.

Also, the link I supplied you with (the one w/ rock and sand) is about
aligning row according to Pg internal constraints (in terms of memory) -
as it is said in this article, you could quite easily reach -20% on table
place taken on disk - this is almost a nonsense for any small app, but
with your number of rows, this becomes almost mandatory to scan faster at
a lower cost with less than an hour of tests/calculations to reorder
columns in your main table.

> how many simultaneous accesses you have/need,
>      Application - 5 and probably additional 5 for operator.

Ok almost nothing, connections speaking, but with a big debit each.

> what is the distribution between reads & writes,
>         70 read while 30 writ
> what is the alignement & size of one row,
>       Most used table has 42 columns while next after it has 18 columns

No, see above about what kind of alignment it is.

> etc.
>
> After that,
> which stripe size, 32

I do not use RAID anymore (and used it only in software version), so I do
not know if it is a good size taking in account the Pg page size (8kB),
the SSD sector (normally 2kB) and the clustering (min size of a
logical sector.)

JY

  18. Re: Slow response to my query

    Goke Aruna <goksie@gmail.com> — 2019-11-30T07:07:27Z

    This is great, thank you!
Very educative. I am still reading on the zfs.
>From your explanation, you prefer software raid to hardware raid, though
greater preference for ZFS.

Do you have a pointer to what I can read on how this is achieved be it LVM
or ZFS?

By the way, based on your experience, what Linux variants will you
recommend for postgresql and what version.
I have read from severalnines.com site that CentOS 7 should be avoided as
much as possible unfortunately I am currently running it.

I really appreciate your guide.

Regards

On Fri, Nov 29, 2019 at 6:13 PM Bzzzz <lazyvirus@gmx.com> wrote:

> On Fri, 29 Nov 2019 17:35:03 +0100
> Goke Aruna <goksie@gmail.com> wrote:
>
> > are you debit or IOPS bounded,
> >      IOPS
>
> Then prefer 1|10, others are bounded to the R/W speed of only the
> slowest disk while 1|10 is distributed among 1/2 disk Nb, so faster for
> reads.
> NB: This is less true with ZFS as other factors are at work.
>
> Note that if you plan to use ZFS, extending an array of disks will mean
> most of new writings will happen only onto the new disks until they reach
> the same filling level than others - often crucial whatever the RAIDZ
> level is.
>
> Also do not forget about backupS!
>
> > how fast is your DB growing,
> >      Like 1.5GB per hour
>
> Hmm, this lead to a bit more than 1TB/month30d, you'll either need more
> SSDz or larger.
>
> This is here that you can see carriers' names in the plain is
> eating too place for nothing: one regular INTEGER (int4) = 4 bytes
> (used as a foreign key) or even less if you do not have a lot of
> carriers and the y fit into 32,768 (small int), when each character is
> _at least_ one byte and often more in UTF8.
>
> Also, the link I supplied you with (the one w/ rock and sand) is about
> aligning row according to Pg internal constraints (in terms of memory) -
> as it is said in this article, you could quite easily reach -20% on table
> place taken on disk - this is almost a nonsense for any small app, but
> with your number of rows, this becomes almost mandatory to scan faster at
> a lower cost with less than an hour of tests/calculations to reorder
> columns in your main table.
>
> > how many simultaneous accesses you have/need,
> >      Application - 5 and probably additional 5 for operator.
>
> Ok almost nothing, connections speaking, but with a big debit each.
>
> > what is the distribution between reads & writes,
> >         70 read while 30 writ
> > what is the alignement & size of one row,
> >       Most used table has 42 columns while next after it has 18 columns
>
> No, see above about what kind of alignment it is.
>
> > etc.
> >
> > After that,
> > which stripe size, 32
>
> I do not use RAID anymore (and used it only in software version), so I do
> not know if it is a good size taking in account the Pg page size (8kB),
> the SSD sector (normally 2kB) and the clustering (min size of a
> logical sector.)
>
> JY
>

  19. Re: Slow response to my query

    Keith <keith@keithf4.com> — 2019-11-30T16:19:26Z

    > By the way, based on your experience, what Linux variants will you
> recommend for postgresql and what version.
> I have read from severalnines.com site that CentOS 7 should be avoided as
> much as possible unfortunately I am currently running it.
>
> I really appreciate your guide.
>
> Regards
>


Zero reasons to avoid CentOS itself. It's a perfectly good server OS for
whatever your needs. Unless you have a RHEL license, then of course you
should use the Enterprise version you're paying for. What should be avoided
is using the default PostgreSQL package that 7 (or even 8) comes with. 7 is
still all the way back at 9.2, which has long gone out of support. For all
RHEL related releases (CentOS, Fedora, Oracle, etc),  use the PGDG
repositories which contain up to date versions of PG for all of them.

https://www.postgresql.org/download/linux/redhat/

Keith
http://www.keithf4.com

  20. Re: Slow response to my query

    Bzzzz <lazyvirus@gmx.com> — 2019-11-30T17:58:35Z

    On Sat, 30 Nov 2019 08:07:27 +0100
Goke Aruna <goksie@gmail.com> wrote:

> This is great, thank you!

You're welcome.

> Very educative. I am still reading on the zfs.
> From your explanation, you prefer software raid to hardware raid,
> though greater preference for ZFS.

You can't really compare RAID to RAIDZ, they're different paradigms.
RAID is redundancy only, while RAIDZ is redundancy + data integrity.

Of course, nothing comes without a cost (be it $$$ and/or speed) AND as
usual, YMMV from your own needs (an objective decision way it to make a
Ben (from Benjy Franklin): for each system, you take a sheet of paper,
separate it in 2 with a line, put positive points to the left and
negative to the right, balance them, add the points to see on which
side it is leaning AND respect this result (!).)

> Do you have a pointer to what I can read on how this is achieved be it
> LVM or ZFS?

Not really, I read a lot about that (hundreds of articles) before even
testing, so dig the web and read a lot - avoid old papers, as ZoL is
evolving quite fast.

> By the way, based on your experience, what Linux variants will you
> recommend for postgresql and what version.

Debian _stable_ is world's best (I'm a little biased on this one as I
use it for 20 years now;)
But not if you do not have very good administration skills and to also
be avoid if you want to boot your system on ZFS (which is not such a
good idea anyway.)

> I have read from severalnines.com site that CentOS 7 should be avoided
> as much as possible unfortunately I am currently running it.

Yeah, it is veeery baaad… compared to Debian ;-p)
No, it is indeed a good distro, as almost any other one that is a root
one ; it is also often used as a base for some applications on CD or live
CDz.

If you don't know which one to pick, test them thoroughly one by one
taking your time (time only fully acknowledge things for which you took…
your time) - also take time to read articles and know if it's community's
large and alive (watch their mailing-lists archives, do they have a
large number of posts per month or not) and avoid derivatives (zzzzzz,
based upon aaaaaa.)
Take all your time, because your choice is something that'll almost
surely follow you for a looong time.

Jean-Yves

  21. Re: Slow response to my query

    Goke Aruna <goksie@gmail.com> — 2019-11-30T19:00:03Z

    Thanks for the tip!

On Sat, Nov 30, 2019 at 6:58 PM Bzzzz <lazyvirus@gmx.com> wrote:

> On Sat, 30 Nov 2019 08:07:27 +0100
> Goke Aruna <goksie@gmail.com> wrote:
>
> > This is great, thank you!
>
> You're welcome.
>
> > Very educative. I am still reading on the zfs.
> > From your explanation, you prefer software raid to hardware raid,
> > though greater preference for ZFS.
>
> You can't really compare RAID to RAIDZ, they're different paradigms.
> RAID is redundancy only, while RAIDZ is redundancy + data integrity.
>
> Of course, nothing comes without a cost (be it $$$ and/or speed) AND as
> usual, YMMV from your own needs (an objective decision way it to make a
> Ben (from Benjy Franklin): for each system, you take a sheet of paper,
> separate it in 2 with a line, put positive points to the left and
> negative to the right, balance them, add the points to see on which
> side it is leaning AND respect this result (!).)
>
> > Do you have a pointer to what I can read on how this is achieved be it
> > LVM or ZFS?
>
> Not really, I read a lot about that (hundreds of articles) before even
> testing, so dig the web and read a lot - avoid old papers, as ZoL is
> evolving quite fast.
>
> > By the way, based on your experience, what Linux variants will you
> > recommend for postgresql and what version.
>
> Debian _stable_ is world's best (I'm a little biased on this one as I
> use it for 20 years now;)
> But not if you do not have very good administration skills and to also
> be avoid if you want to boot your system on ZFS (which is not such a
> good idea anyway.)
>
> > I have read from severalnines.com site that CentOS 7 should be avoided
> > as much as possible unfortunately I am currently running it.
>
> Yeah, it is veeery baaad… compared to Debian ;-p)
> No, it is indeed a good distro, as almost any other one that is a root
> one ; it is also often used as a base for some applications on CD or live
> CDz.
>
> If you don't know which one to pick, test them thoroughly one by one
> taking your time (time only fully acknowledge things for which you took…
> your time) - also take time to read articles and know if it's community's
> large and alive (watch their mailing-lists archives, do they have a
> large number of posts per month or not) and avoid derivatives (zzzzzz,
> based upon aaaaaa.)
> Take all your time, because your choice is something that'll almost
> surely follow you for a looong time.
>
> Jean-Yves
>