Re: pg12 partitions show bad performance vs pg96
*8 ms seems pretty slow to planning that query. Does the planning timedrop
if you execute this multiple times in the same session? Does thetime change
if you try again without any foreign keys? *
No one is using the system besides me, therefore after running the query
one time
most of the data is in the cache... If I run it multiple times the query
time is reduced :
Planning Time: 0.361 ms
Execution Time: 0.110 ms
*Can you share the results of that?*
Sure. I did the same procedure but this time I inserted 100m records
instead of 10m. This time the results were by far worse in pg12 :
PG12 :
postgres=# explain analyze select * from iot_data da,iot_device de where
de.name in ('50a') and de.id = da.device and da.metadata=50 and
lower(da.data) like '50';
QUERY
PLAN
Gather (cost=1002.26..1563512.35 rows=10 width=44) (actual
time=95161.056..95218.764 rows=0 loops=1)
Workers Planned: 2
Workers Launched: 2
-> Hash Join (cost=2.26..1562511.35 rows=4 width=44) (actual
time=94765.860..94765.861 rows=0 loops=3)
Hash Cond: (da_2.device = de.id)
-> Parallel Append (cost=0.00..1562506.90 rows=814 width=37)
(actual time=94765.120..94765.120 rows=0 loops=3)
-> Parallel Seq Scan on iot_data_2 da_2
(cost=0.00..584064.14 rows=305 width=37) (actual time=36638.829..36638.829
rows=0 loops=3)
Filter: ((metadata = 50) AND (lower(data) ~~
'50'::text))
Rows Removed by Filter: 12460009
-> Parallel Seq Scan on iot_data_1 da_1
(cost=0.00..504948.69 rows=262 width=36) (actual time=43990.427..43990.427
rows=0 loops=2)
Filter: ((metadata = 50) AND (lower(data) ~~
'50'::text))
Rows Removed by Filter: 16158316
-> Parallel Seq Scan on iot_data_0 da
(cost=0.00..473490.00 rows=247 width=37) (actual time=86396.665..86396.665
rows=0 loops=1)
Filter: ((metadata = 50) AND (lower(data) ~~
'50'::text))
Rows Removed by Filter: 30303339
-> Hash (cost=2.25..2.25 rows=1 width=7) (never executed)
-> Seq Scan on iot_device de (cost=0.00..2.25 rows=1
width=7) (never executed)
Filter: (name = '50a'::text)
Planning Time: 45.724 ms
Execution Time: 95252.712 ms
(20 rows)
PG96 :
postgres=# explain analyze select * from iot_data da,iot_device de where
de.name in ('50a') and de.id = da.device and da.metadata=50 and
lower(da.data) like '50';
QUERY PLAN
-
Nested Loop (cost=0.00..2583361.51 rows=20 width=44) (actual
time=18345.229..18345.229 rows=0 loops=1)
Join Filter: (da.device = de.id)
-> Seq Scan on iot_device de (cost=0.00..2.25 rows=1 width=7) (actual
time=0.022..0.037 rows=1 loops=1)
Filter: (name = '50a'::text)
Rows Removed by Filter: 99
-> Seq Scan on iot_data da (cost=0.00..2583334.84 rows=1954 width=37)
(actual time=18345.187..18345.187 rows=0 loops=1)
Filter: ((metadata = 50) AND (lower(data) ~~ '50'::text))
Rows Removed by Filter: 1
Planning time: 35.450 ms
Execution time: 18345.301 ms
(10 rows)
*The most basic guidelines for table partitioning are, don't partitionyour
tables unless it's a net win. If partitioning was alwaysfaster, we'd just
have designed Postgres to implicitly partition allof your tables for you.
There are some other guidelines in [1].*
Isnt creating partition should increase the execution time ? I mean,
instead of running on a table with 10m records, I can run over a partition
with 3m records. isnt less data means better performance for simple queries
like the one I used ?
I read the best practice for the docs, and I think that I met most of them
- I chose the right partition key(in this case it was device),
Regarding the amount of partitions - I choose 3 just to test the results. I
didnt create a lot of partitions, and my logic tells me that querying a
table with 3m records should be faster than 10m records.. Am I missing
something ?
Re: pg12 partitions show bad performance vs pg96
> > I tried to do even something simpler, run the query with only the > partition column in the where clause and the results werent good for pg12 : > PG12 : postgres=# explain analyze select * from iot_data where device=51; QUERY PLAN --- Gather (cost=1000.00..514086.40 rows=1027284 width=37) (actual time=6.777..61558.272 rows=1010315 loops=1) Workers Planned: 2 Workers Launched: 2 -> Parallel Seq Scan on iot_data_0 (cost=0.00..410358.00 rows=428035 width=37) (actual time=1.152..61414.483 rows=336772 loops=3) Filter: (device = 51) Rows Removed by Filter: 9764341 Planning Time: 15.720 ms Execution Time: 61617.851 ms (8 rows) PG9.6 postgres=# explain analyze select * from iot_data where device=51; QUERY PLAN - Seq Scan on iot_data (cost=0.00..2083334.60 rows=976667 width=37) (actual time=21.922..16753.492 rows=1010315 loops=1) Filter: (device = 51) Rows Removed by Filter: 98989685 Planning time: 0.119 ms Execution time: 16810.787 ms (5 rows) > Besides hardware, anything else worth checking ? the machine are > identical in aspect of resources. >
Re: pg12 partitions show bad performance vs pg96
On Mon, Mar 09, 2020 at 12:31:15PM +0200, Mariel Cherkassky wrote: > > I tried to do even something simpler, run the query with only the > > partition column in the where clause and the results werent good for pg12 : > > PG12 : > postgres=# explain analyze select * from iot_data where device=51; > QUERY PLAN > --- > Gather (cost=1000.00..514086.40 rows=1027284 width=37) (actual > time=6.777..61558.272 rows=1010315 loops=1) >Workers Planned: 2 >Workers Launched: 2 >-> Parallel Seq Scan on iot_data_0 (cost=0.00..410358.00 rows=428035 > width=37) (actual time=1.152..61414.483 rows=336772 loops=3) For whatever reason, your storage/OS seem to be handling parallel reads poorly. I would SET max_parallel_workers_per_gather=0 and retest (but also look into improving the storage). Also, it's not required, but I think a typical partitioning schema would have an index on the column being partitioned. I see you have an index on iot_data(metadata,lower(data)), so I still wonder whether you'd have better results partitioned on metadata, or otherwise maybe adding an index on "device". But I don't know what your typical queries are. > PG9.6 > postgres=# explain analyze select * from iot_data where device=51; >QUERY PLAN > - > Seq Scan on iot_data (cost=0.00..2083334.60 rows=976667 width=37) (actual > time=21.922..16753.492 rows=1010315 loops=1) -- Justin
Re: pg12 partitions show bad performance vs pg96
OK so I found the problem but other problem appeared.
I found out that the pg12 machine had some limits on the vm settings in
aspect of cpu and memory. Now both machines are exactly the same in aspect
of all hardware and dont have any limit.
CPU - 8
RAM - 32GB.
I tested it with cold cache :
service postgresql stop;
echo 1 > /proc/sys/vm/drop_caches;
service postgresql start;
psql -d postgres -U postgres;
I used two simples queries, one that implicitly comparing the partition key
with a const value and another one that joins other table by the partition
column(and in this query the problem).
The first query : results are better with pg12 :
explain analyze select * from iot_data where device=51;
PG96
QUERY PLAN
Seq Scan on iot_data (cost=0.00..2083334.60 rows=976667 width=37) (actual
time=1.560..67144.164 rows=1010315 loops=1)
Filter: (device = 51)
Rows Removed by Filter: 98989685
Planning time: 9.219 ms
Execution time: 67,228.431 ms
(5 rows)
PG12 - 3 PARTITIONS
QUERY PLAN
---
Gather (cost=1000.00..514086.40 rows=1027284 width=37) (actual
time=3.871..15022.118 rows=1010315 loops=1)
Workers Planned: 2
Workers Launched: 2
-> Parallel Seq Scan on iot_data_0 (cost=0.00..410358.00 rows=428035
width=37) (actual time=1.670..14815.480 rows=336772 loops=3)
Filter: (device = 51)
Rows Removed by Filter: 9764341
Planning Time: 27.292 ms
Execution Time: 15085.317 ms
(8 rows)
The second query with pg12 :
QUERY : explain analyze select * from iot_data da,iot_device de where
de.name in ('50a') and de.id = da.device and da.metadata=50 and
lower(da.data) like '50';
PG96
postgres=# explain analyze select * from iot_data da,iot_device de where
de.name in ('50a') and de.id = da.device and da.metadata=50 and
lower(da.data) like '50';
QUERY PLAN
-
Nested Loop (cost=0.00..2583361.51 rows=20 width=44) (actual
time=44894.312..44894.312 rows=0 loops=1)
Join Filter: (da.device = de.id)
-> Seq Scan on iot_device de (cost=0.00..2.25 rows=1 width=7) (actual
time=0.018..0.028 rows=1 loops=1)
Filter: (name = '50a'::text)
Rows Removed by Filter: 99
-> Seq Scan on iot_data da (cost=0.00..2583334.84 rows=1954 width=37)
(actual time=44894.279..44894.279 rows=0 loops=1)
Filter: ((metadata = 50) AND (lower(data) ~~ '50'::text))
Rows Removed by Filter: 1
Planning time: 11.313 ms
Execution time: 44894.357 ms
(10 rows)
PG12 - 3 PARTITIONS
QUERY
PLAN
Gather (cost=1002.26..1563512.35 rows=10 width=44) (actual
time=22306.091..22309.209 rows=0 loops=1)
Workers Planned: 2
Workers Launched: 2
-> Hash Join (cost=2.26..1562511.35 rows=4 width=44) (actual
time=22299.412..22299.413 rows=0 loops=3)
Hash Cond: (da_2.device = de.id)
-> Parallel Append (cost=0.00..1562506.90 rows=814 width=37)
(actual time=22299.411..22299.411 rows=0 loops=3)
-> Parallel Seq Scan on iot_data_2 da_2
(cost=0.00..584064.14 rows=305 width=37) (actual time=9076.535..9076.535
rows=0 loops=3)
Filter: ((metadata = 50) AND (lower(data) ~~
'50'::text))
Rows Removed by Filter: 12460009
-> Parallel Seq Scan on iot_data_1 da_1
(cost=0.00..504948.69 rows=262 width=36) (actual time=10296.751..10296.751
rows=0 loops=2)
Filter: ((metadata = 50) AND (lower(data) ~~
'50'::text))
Rows Removed by Filter: 16158316
-> Parallel Seq Scan on iot_data_0 da
(cost=0.00..473490.00 rows=247 width=37) (actual time=19075.081..19075.081
rows=0 loops=1)
Filter: ((metadata = 50) AND (lower(data) ~~
'50'::text))
Rows Removed by Filter: 30303339
-> Hash (cost=2.25..2.25 rows=1 width=7) (never executed)
-> Seq Scan on iot_device de (cost=0.00..2.25 rows=1
width=7) (never executed)
Filter: (name = '50a'::text)
Planning Time: 30.429 ms
Execution Time: 22309.364 ms
(20 rows)
I tried disabling max_parallel_workers_gathers but It just decreased the
db`s performance.
Now regarding the main issue here - as u can see when I used the second
query, I didnt mentioned the partition column specificly but
Re: pg12 partitions show bad performance vs pg96
> Also, it's not required, but I think a typical partitioning schema would > have > an index on the column being partitioned. I see you have an index on > iot_data(metadata,lower(data)), so I still wonder whether you'd have better > results partitioned on metadata, or otherwise maybe adding an index on > "device". But I don't know what your typical queries are. > > I understood now why u suggested an index on the partition column. It depends on how many distinct values of the partition column I'll have in that partition. Thanks for the suggestion , good idea !
Re: pg12 partitions show bad performance vs pg96
On Tue, 10 Mar 2020 at 02:08, Mariel Cherkassky
wrote:
> PG12 - 3 PARTITIONS
>
> QUERY
> PLAN
>
> Gather (cost=1002.26..1563512.35 rows=10 width=44) (actual
> time=22306.091..22309.209 rows=0 loops=1)
>Workers Planned: 2
>Workers Launched: 2
>-> Hash Join (cost=2.26..1562511.35 rows=4 width=44) (actual
> time=22299.412..22299.413 rows=0 loops=3)
> Hash Cond: (da_2.device = de.id)
> -> Parallel Append (cost=0.00..1562506.90 rows=814 width=37)
> (actual time=22299.411..22299.411 rows=0 loops=3)
>-> Parallel Seq Scan on iot_data_2 da_2
> (cost=0.00..584064.14 rows=305 width=37) (actual time=9076.535..9076.535
> rows=0 loops=3)
> Filter: ((metadata = 50) AND (lower(data) ~~ '50'::text))
> Rows Removed by Filter: 12460009
>-> Parallel Seq Scan on iot_data_1 da_1
> (cost=0.00..504948.69 rows=262 width=36) (actual time=10296.751..10296.751
> rows=0 loops=2)
> Filter: ((metadata = 50) AND (lower(data) ~~ '50'::text))
> Rows Removed by Filter: 16158316
>-> Parallel Seq Scan on iot_data_0 da (cost=0.00..473490.00
> rows=247 width=37) (actual time=19075.081..19075.081 rows=0 loops=1)
> Filter: ((metadata = 50) AND (lower(data) ~~ '50'::text))
> Rows Removed by Filter: 30303339
> -> Hash (cost=2.25..2.25 rows=1 width=7) (never executed)
>-> Seq Scan on iot_device de (cost=0.00..2.25 rows=1
> width=7) (never executed)
> Filter: (name = '50a'::text)
> Planning Time: 30.429 ms
> Execution Time: 22309.364 ms
> (20 rows)
>From what I can work out, the DDL you used here is:
-- you didn't seem to specify the DDL for iot_device, so I used:
create table iot_device (
id bigint primary key,
name text not null
);
insert into iot_device select x,x::Text || 'a' from generate_Series(1,100) x;
create table iot_data(id serial ,data text,metadata bigint,device
bigint references iot_device(id),primary key(id,device)) partition by
hash(device);
create table iot_data_0 partition of iot_data for values with (MODULUS
3, remainder 0);
create table iot_data_1 partition of iot_data for values with (MODULUS
3, remainder 1);
create table iot_data_2 partition of iot_data for values with (MODULUS
3, remainder 2);
insert into iot_data select
generate_series(1,1000),random()*10,random()*254,random()*99+1;
create index on iot_data(metadata,lower(data));
vacuum analyze iot_data;
In which case, you're getting a pretty different plan than I am. (I
admit that I only tried on current master and not PG12.2, however, I
see no reason that PG12.2 shouldn't produce the same plan)
I get:
# explain analyze select * from iot_data da,iot_device de where
de.name in ('50a') and de.id = da.device and da.metadata=50 and
lower(da.data) like '50';
QUERY PLAN
Nested Loop (cost=0.56..28.04 rows=1 width=49) (actual
time=0.058..0.058 rows=0 loops=1)
Join Filter: (da.device = de.id)
-> Seq Scan on iot_device de (cost=0.00..2.25 rows=1 width=11)
(actual time=0.013..0.016 rows=1 loops=1)
Filter: (name = '50a'::text)
Rows Removed by Filter: 99
-> Append (cost=0.56..25.76 rows=3 width=38) (actual
time=0.040..0.040 rows=0 loops=1)
-> Index Scan using iot_data_0_metadata_lower_idx on
iot_data_0 da_1 (cost=0.56..8.58 rows=1 width=38) (actual
time=0.020..0.020 rows=0 loops=1)
Index Cond: ((metadata = 50) AND (lower(data) = '50'::text))
Filter: (lower(data) ~~ '50'::text)
-> Index Scan using iot_data_1_metadata_lower_idx on
iot_data_1 da_2 (cost=0.56..8.58 rows=1 width=38) (actual
time=0.010..0.010 rows=0 loops=1)
Index Cond: ((metadata = 50) AND (lower(data) = '50'::text))
Filter: (lower(data) ~~ '50'::text)
-> Index Scan using iot_data_2_metadata_lower_idx on
iot_data_2 da_3 (cost=0.56..8.58 rows=1 width=38) (actual
time=0.009..0.009 rows=0 loops=1)
Index Cond: ((metadata = 50) AND (lower(data) = '50'::text))
Filter: (lower(data) ~~ '50'::text)
Planning Time: 0.280 ms
Execution Time: 0.094 ms
(17 rows)
Are you certain that you added an index on iot_data (metadata, lower(data)); ?
