Re: Improving Performance of Query ~ Filter by A, Sort by B

[Lincoln Swaine-Moore]

Tom and Jeff,

Thanks very much for the suggestions!

Here's what I've found so far after playing around for a few more days:

What is your default_statistics_target?  What can you tell us about the
> distribution of parent_id?  (exponential, power law, etc?).  Can you show
> the results for select * from pg_stats where tablename='a' and
> attname='parent_id' \x\g\x  ?


The default_statistics_target is 500, which I agree seems quite
insufficient for these purposes. I bumped this up to 2000, and saw some
improvement in the row count estimation, but pretty much the same query
plans. Unfortunately the distribution of counts is not intended to be
correlated to parent_id, which is one reason I imagine the histograms might
not be particularly effective unless theres one bucket for every value.
Here is the output you requested:

select * from pg_stats where tablename='a' and attname='parent_id';

schemaname | public
tablename  | a
attname| parent_id
inherited  | t
null_frac  | 0
avg_width  | 4
n_distinct | 18871
most_common_vals   | {15503,49787,49786,24595,49784,17549, ...} (2000
values)
most_common_freqs  |
{0.0252983,0.02435,0.0241317,0.02329,0.019095,0.0103967,0.00758833,0.004245,
...} (2000 values)
histogram_bounds   |
{2,12,17,24,28,36,47,59,74,80,86,98,108,121,135,141,147,160,169,177,190,204,
...} (2001 values)
correlation| -0.161576
most_common_elems  |
most_common_elem_freqs |
elem_count_histogram   |


Interestingly, the number of elements in these most_common_vals is as
expected (2000) for the parent table, but it's lower for the partition
tables, despite the statistics level being the same.

SELECT attstattarget
FROM   pg_attribute
WHERE  attrelid in ('a_partition1'::regclass, 'a'::regclass)
ANDattname = 'parent_id';
-[ RECORD 1 ]-+-
attstattarget | 2000
-[ RECORD 2 ]-+-
attstattarget | 2000


select * from pg_stats where tablename='a_partition1' and
attname='parent_id';

schemaname | public
tablename  | a_partition1
attname| parent_id
inherited  | f
null_frac  | 0
avg_width  | 4
n_distinct | 3969
most_common_vals   |
{15503,49787,49786,24595,49784,10451,20136,17604,9683, ...} (400-ish values)
most_common_freqs  |
{0.0807067,0.0769483,0.0749433,0.073565,0.0606433,0.0127917,0.011265,0.0112367,
...} (400-ish values)
histogram_bounds   | {5,24,27,27,33,38,41,69,74,74, ...} (1500-ish
values)
correlation| 0.402414
most_common_elems  |
most_common_elem_freqs |
elem_count_histogram   |

A few questions re: statistics:
 1) would it be helpful to bump column statistics to, say, 20k (the number
of distinct values of parent_id)?
 2) is the discrepancy between the statistics on the parent and child table
be expected? certainly I would think that the statistics would be
different, but I would've imagined they would have histograms of the same
size given the settings being the same.
 3) is there a way to manually specify the the distribution of rows to be
even? that is, set the frequency of each value to be ~ n_rows/n_distinct.
This isn't quite accurate, but is a reasonable assumption about the
distribution, and might generate better query plans.

The same thing could be obtained by doing a dummy operation, such as ORDER
> BY tmstmp + '0 seconds' DESC.  I prefer that method, as it is more
> obviously a tuning trick.  Adding in "id" looks more like a legitimate
> desire to break any ties that might occasionally occur in tmstmp.


I 100% agree that that is more clear. Thanks for the suggestion!

Finally, could you rewrite it as a join to a VALUES list, rather than as an
> in-list?


I should've mentioned this in my initial post, but I tried doing this
without much success.

You could try reversing the order and adding a column to be (tmstmp,
> parent_id, id) and keeping the table well vacuumed.  This would allow the
> slow plan to still walk the indexes in tmstmp order but do it as an
> index-only scan, so it could omit the extra trip to the table. That trip to
> the table must be awfully slow to explain the numbers you show later in the
> thread.


Just to clarify, do you mean building indexes like:
CREATE INDEX "a_tmstmp_parent_id_id_idx_[PART_KEY]" on
"a_partition[PART_KEY]" USING btree("tmstmp", "parent_id", "id")
That seems promising! Is the intuition here that we want the first key of
the index to be the one we are ultimately ordering by? Sounds like I make
have had that flipped initially. My understanding of this whole situation
(and please do correct me if this doesn't make sense) is the big bottleneck
here is reading pages from disk (when looking at stopped up queries, the
wait_event is DataFileRead), and so anything that can be done to minimize
the pages read will be valuable. Which is why I would love to get the query
plan to use the tmstmp index without having to f

Why HDD performance is better than SSD in this case

[Neto pr]

Dear,
Some of you can help me understand this.

This query plan is executed in the query below (query 9 of TPC-H
Benchmark, with scale 40, database with approximately 40 gb).

The experiment consisted of running the query on a HDD (Raid zero).
Then the same query is executed on an SSD (Raid Zero).

Why did the HDD (7200 rpm)  perform better?
HDD - TIME 9 MINUTES
SSD - TIME 15 MINUTES

As far as I know, the SSD has a reading that is 300 times faster than SSD.

--- Execution  Plans---
ssd 40g
https://explain.depesz.com/s/rHkh

hdd 40g
https://explain.depesz.com/s/l4sq

Query 

select
nation,
o_year,
sum(amount) as sum_profit
from
(
select
n_name as nation,
extract(year from o_orderdate) as o_year,
l_extendedprice * (1 - l_discount) - ps_supplycost *
l_quantity as amount
from
part,
supplier,
lineitem,
partsupp,
orders,
nation
where
s_suppkey = l_suppkey
and ps_suppkey = l_suppkey
and ps_partkey = l_partkey
and p_partkey = l_partkey
and o_orderkey = l_orderkey
and s_nationkey = n_nationkey
and p_name like '%orchid%'
) as profit
group by
nation,
o_year
order by
nation,
o_year desc

Re: Why HDD performance is better than SSD in this case

[Benjamin Scherrey]

What's the on disk cache size for each drive? The better HDD performance
problem won't be sustained with large amounts of data and several different
queries.

  - - Ben Scherrey

On Tue, Jul 17, 2018, 12:01 PM Neto pr  wrote:

> Dear,
> Some of you can help me understand this.
>
> This query plan is executed in the query below (query 9 of TPC-H
> Benchmark, with scale 40, database with approximately 40 gb).
>
> The experiment consisted of running the query on a HDD (Raid zero).
> Then the same query is executed on an SSD (Raid Zero).
>
> Why did the HDD (7200 rpm)  perform better?
> HDD - TIME 9 MINUTES
> SSD - TIME 15 MINUTES
>
> As far as I know, the SSD has a reading that is 300 times faster than SSD.
>
> --- Execution  Plans---
> ssd 40g
> https://explain.depesz.com/s/rHkh
>
> hdd 40g
> https://explain.depesz.com/s/l4sq
>
> Query 
>
> select
> nation,
> o_year,
> sum(amount) as sum_profit
> from
> (
> select
> n_name as nation,
> extract(year from o_orderdate) as o_year,
> l_extendedprice * (1 - l_discount) - ps_supplycost *
> l_quantity as amount
> from
> part,
> supplier,
> lineitem,
> partsupp,
> orders,
> nation
> where
> s_suppkey = l_suppkey
> and ps_suppkey = l_suppkey
> and ps_partkey = l_partkey
> and p_partkey = l_partkey
> and o_orderkey = l_orderkey
> and s_nationkey = n_nationkey
> and p_name like '%orchid%'
> ) as profit
> group by
> nation,
> o_year
> order by
> nation,
> o_year desc
>
>

Re: Why HDD performance is better than SSD in this case

[winston cheung]

Can you show the configuration of postgresql.conf?
Query configuration method:
Select name, setting from pg_settings where name ~ 'buffers|cpu|^enable';


On 2018年07月17日 13:17, Benjamin Scherrey wrote:
What's the on disk cache size for each drive? The better HDD 
performance problem won't be sustained with large amounts of data and 
several different queries.


  - - Ben Scherrey

On Tue, Jul 17, 2018, 12:01 PM Neto pr > wrote:


Dear,
Some of you can help me understand this.

This query plan is executed in the query below (query 9 of TPC-H
Benchmark, with scale 40, database with approximately 40 gb).

The experiment consisted of running the query on a HDD (Raid zero).
Then the same query is executed on an SSD (Raid Zero).

Why did the HDD (7200 rpm)  perform better?
HDD - TIME 9 MINUTES
SSD - TIME 15 MINUTES

As far as I know, the SSD has a reading that is 300 times faster
than SSD.

--- Execution  Plans---
ssd 40g
https://explain.depesz.com/s/rHkh

hdd 40g
https://explain.depesz.com/s/l4sq

Query 

select
    nation,
    o_year,
    sum(amount) as sum_profit
from
    (
        select
            n_name as nation,
            extract(year from o_orderdate) as o_year,
            l_extendedprice * (1 - l_discount) - ps_supplycost *
l_quantity as amount
        from
            part,
            supplier,
            lineitem,
            partsupp,
            orders,
            nation
        where
            s_suppkey = l_suppkey
            and ps_suppkey = l_suppkey
            and ps_partkey = l_partkey
            and p_partkey = l_partkey
            and o_orderkey = l_orderkey
            and s_nationkey = n_nationkey
            and p_name like '%orchid%'
    ) as profit
group by
    nation,
    o_year
order by
    nation,
    o_year desc



--
张文升 winston
PostgreSQL DBA

Re: Why HDD performance is better than SSD in this case

[Mark Kirkwood]

Can you post make and model of the SSD concerned? In general the cheaper 
consumer grade ones cannot do sustained read/writes at anything like 
their quoted max values.


regards

Mark


On 17/07/18 17:00, Neto pr wrote:

Dear,
Some of you can help me understand this.

This query plan is executed in the query below (query 9 of TPC-H
Benchmark, with scale 40, database with approximately 40 gb).

The experiment consisted of running the query on a HDD (Raid zero).
Then the same query is executed on an SSD (Raid Zero).

Why did the HDD (7200 rpm)  perform better?
HDD - TIME 9 MINUTES
SSD - TIME 15 MINUTES

As far as I know, the SSD has a reading that is 300 times faster than SSD.

--- Execution  Plans---
ssd 40g
https://explain.depesz.com/s/rHkh

hdd 40g
https://explain.depesz.com/s/l4sq

Query 

select
 nation,
 o_year,
 sum(amount) as sum_profit
from
 (
 select
 n_name as nation,
 extract(year from o_orderdate) as o_year,
 l_extendedprice * (1 - l_discount) - ps_supplycost *
l_quantity as amount
 from
 part,
 supplier,
 lineitem,
 partsupp,
 orders,
 nation
 where
 s_suppkey = l_suppkey
 and ps_suppkey = l_suppkey
 and ps_partkey = l_partkey
 and p_partkey = l_partkey
 and o_orderkey = l_orderkey
 and s_nationkey = n_nationkey
 and p_name like '%orchid%'
 ) as profit
group by
 nation,
 o_year
order by
 nation,
 o_year desc