GP 5.10 vs 4.3.26 Performance

[Karthik Raparthi]

Hi,

 

Recently migrated Dev server from GP 4.3.26 to 5.10

 

Suddenly I see a query was performing very bad occasionally. Few times it runs in 5 secs and on other hand it takes hours like (11 hours and didn't return any result)

 

Table A : 80M

Table B : 10K to 100K (varies by day)

 

Inserts into a Temporary table   Select distinct columns from table A join table B on 5 columns

 

 

Inserts into a Temporary table Select distinct columns from table A join table B on 5 columns   Insert (cost=0.00..437.01 rows=1 width=40) -> Result (cost=0.00..437.00 rows=1 width=44) -> GroupAggregate (cost=0.00..437.00 rows=1 width=40) Group By: 5 columns -> Sort (cost=0.00..437.00 rows=1 width=30) Sort Key: 5 columns -> Nested Loop (cost=0.00..437.00 rows=1 width=30) Join Filter: true -> Broadcast Motion 24:24 (slice1; segments: 24) (cost=0.00..431.00 rows=1 width=22) -> Table Scan on stg (cost=0.00..431.00 rows=1 width=22) -> Index Scan using idx_on tableA (cost=0.00..6.00 rows=1 width=30) Index Cond: tablea.id = tableb.id Filter: 5 columns Settings: effective_cache_size=2GB Optimizer status: PQO version 2.65.0

 

[Faisal Ali]

Hi Karthik,

 

I will try to first look on the "Broadcast Motion 24:24" step, since you have mentioned "TableB" I assume the "stg" table is the tableB you are referring.

 

"Broadcast Motion 24:24" motion is very good to sub node of the plan which returns few amount of data but for 100k records it does a broadcast motion then its bad, which also means the stats are stale or sample data ( used for analyze ) was a bad sample since here it shows the rows returns by stg is 1 , when you mentioned its 10k to 100K.

 

You can increase the sample data using "default_statistics_target"

 

Thanks