On Tue, 10 Mar 2020 at 02:08, Mariel Cherkassky
wrote:
> PG12 - 3 PARTITIONS
>
> QUERY
> PLAN
>
> 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
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 :
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;
>
>
> 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
*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
On Sun, Mar 08, 2020 at 06:05:26PM +0200, Mariel Cherkassky wrote:
> In pg12 I created a table with 3 hash partitiones :
> create table iot_data(id serial ,data text,metadata bigint,device bigint
> references iot_device(id),primary key(id,device)) partition by hash(device);
> and now for the
On Mon, 9 Mar 2020 at 05:05, Mariel Cherkassky
wrote:
> PG12 :
> Planning Time: 8.157 ms
> Execution Time: 2.920 ms
> (22 rows)
>
>
> PG96 :
> Planning time: 0.815 ms
> Execution time: 0.158 ms
> (12 rows)
8 ms seems pretty slow to planning that query. Does the planning time
drop if you
I realized that the planner goes to the right partition because "(never
executed)" is mentioned near the scan of the other partitions. However,
still i'm not sure why performance is better in pg96.
בתאריך יום א׳, 8 במרץ 2020 ב-18:05 מאת Mariel Cherkassky <
mariel.cherkas...@gmail.com>:
>
Hey,
I upgraded from 96 to 12 in our test env and I'm seeing that for queries
that involve join operation between a partition table and other tables
there is degradation is performance compared to pg96 performance.
My machine : 8cpu,16gb,regular hd,linux redhat 6
pg settings :
max_wal_size = 2GB
10 matches
Mail list logo
