Re: Slow response to my query - Mailing list pgsql-novice

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