CREATE STATISTICS What is it for? Tomas Vondra - PowerPoint PPT Presentation

pgconf.eu 2018 Lisbon, October 23-26, 2018 CREATE STATISTICS What is it for? Tomas Vondra <tomas.vondra@2ndquadrant.com> https://github.com/tvondra/create-statistics-talk https://www. 2ndQuadrant.com

pgconf.eu 2018 Lisbon, October 23-26, 2018 Agenda ● Quick intro into planning and estimates. ● Estimates with correlated columns. ● CREATE STATISTICS to the rescue! – functional dependencies – ndistinct ● Future improvements. https://github.com/tvondra/create-statistics-talk https://www. 2ndQuadrant.com

pgconf.eu 2018 Lisbon, October 23-26, 2018 ZIP_CODES C R E A T E T A B L E z i p _ c o d e s ( p o s t a l _ c o d e V A R C H A R ( 2 0 ) , p l a c e _ n a m e V A R C H A R ( 1 8 0 ) , s t a t e _ n a m e V A R C H A R ( 1 0 0 ) , p r o v i n c e _ n a m e V A R C H A R ( 1 0 0 ) , c o m m u n i t y _ n a m e V A R C H A R ( 1 0 0 ) , l a t i t u d e R E A L , l o n g i t u d e R E A L ) ; c a t c r e a t e - t a b l e . s q l | p s q l t e s t c a t z i p - c o d e s - p o r t u g a l . c s v | p s q l t e s t - c " c o p y z i p _ c o d e s f r o m s t d i n " - - h t t p : / / d o w n l o a d . g e o n a m e s . o r g / e x p o r t / z i p / https://github.com/tvondra/create-statistics-talk https://www. 2ndQuadrant.com

pgconf.eu 2018 Lisbon, October 23-26, 2018 EXPLAIN E X P L A I N ( A N A L Y Z E , T I M I N G o f f ) S E L E C T * F R O M z i p _ c o d e s W H E R E p l a c e _ n a m e = ' L i s b o a ' ; Q U E R Y P L A N - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - S e q S c a n o n z i p _ c o d e s ( c o s t = 0 . 0 0 . . 4 8 6 0 . 7 6 r o w s = 8 5 8 8 w i d t h = 5 6 ) ( a c t u a l r o w s = 9 1 6 6 l o o p s = 1 ) F i l t e r : ( ( p l a c e _ n a m e ) : : t e x t = ' L i s b o a ' : : t e x t ) R o w s R e m o v e d b y F i l t e r : 1 9 7 7 7 5 https://github.com/tvondra/create-statistics-talk https://www. 2ndQuadrant.com

pgconf.eu 2018 Lisbon, October 23-26, 2018 reltuples , relpages S E L E C T r e l t u p l e s , r e l p a g e s F R O M p g _ c l a s s W H E R E r e l n a m e = ' z i p _ c o d e s ' ; r e l t u p l e s | r e l p a g e s - - - - - - - - - - - + - - - - - - - - - - 2 0 6 9 4 1 | 2 2 7 4 https://github.com/tvondra/create-statistics-talk https://www. 2ndQuadrant.com

pgconf.eu 2018 Lisbon, October 23-26, 2018 S E L E C T * F R O M p g _ s t a t s W H E R E t a b l e n a m e = ' z i p _ c o d e s ' A N D a t t n a m e = ' p l a c e _ n a m e ' ; - - - - - - - - - - - - - - - - - - + - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - s c h e m a n a m e | p u b l i c t a b l e n a m e | z i p _ c o d e s a t t n a m e | p l a c e _ n a m e . . . | . . . m o s t _ c o m m o n _ v a l s | { L i s b o a , P o r t o , " V i l a N o v a d e G a i a " , M a i a , . . . } m o s t _ c o m m o n _ f r e q s | { 0 . 0 4 1 5 , 0 . 0 2 0 6 3 3 3 , 0 . 0 0 8 9 6 6 6 7 , 0 . 0 0 8 9 3 3 3 3 , . . . } . . . | . . . https://github.com/tvondra/create-statistics-talk https://www. 2ndQuadrant.com

pgconf.eu 2018 Lisbon, October 23-26, 2018 E X P L A I N ( A N A L Y Z E , T I M I N G o f f ) S E L E C T * F R O M z i p _ c o d e s W H E R E p l a c e _ n a m e = ' L i s b o a ' ; Q U E R Y P L A N - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - S e q S c a n o n z i p _ c o d e s ( c o s t = 0 . 0 0 . . 4 8 6 0 . 7 6 r o w s = 8 5 8 8 w i d t h = 5 6 ) ( a c t u a l r o w s = 9 1 6 6 l o o p s = 1 ) r e l t u p l e s | 2 0 6 9 4 1 m o s t _ c o m m o n _ v a l s | { L i s b o a , … } m o s t _ c o m m o n _ f r e q s | { 0 . 0 4 1 5 , … } 2 0 6 9 4 1 * 0 . 0 4 1 5 = 8 5 8 8 . 0 5 1 5 https://github.com/tvondra/create-statistics-talk https://www. 2ndQuadrant.com

pgconf.eu 2018 Lisbon, October 23-26, 2018 E X P L A I N ( A N A L Y Z E , T I M I N G o f f ) S E L E C T * F R O M z i p _ c o d e s W H E R E s t a t e _ n a m e = ' L i s b o a ' ; Q U E R Y P L A N - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - S e q S c a n o n z i p _ c o d e s ( c o s t = 0 . 0 0 . . 4 8 6 0 . 7 6 r o w s = 3 4 2 6 3 w i d t h = 5 6 ) ( a c t u a l r o w s = 3 5 2 3 0 l o o p s = 1 ) r e l t u p l e s | 2 0 6 9 4 1 m o s t _ c o m m o n _ v a l s | { L i s b o a , … } m o s t _ c o m m o n _ f r e q s | { 0 . 1 6 5 5 6 7 , … } 2 0 6 9 4 1 * 0 . 1 6 5 5 6 7 = 3 4 2 6 2 . 6 https://github.com/tvondra/create-statistics-talk https://www. 2ndQuadrant.com

pgconf.eu 2018 Lisbon, October 23-26, 2018 Underestimate E X P L A I N ( A N A L Y Z E , T I M I N G o f f ) S E L E C T * F R O M z i p _ c o d e s W H E R E p l a c e _ n a m e = ' L i s b o a ' A N D s t a t e _ n a m e = ' L i s b o a ' ; Q U E R Y P L A N - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - S e q S c a n o n z i p _ c o d e s ( c o s t = 0 . 0 0 . . 5 3 7 8 . 1 1 r o w s = 1 4 2 2 w i d t h = 5 6 ) ( a c t u a l r o w s = 9 1 6 5 l o o p s = 1 ) F i l t e r : ( ( ( p l a c e _ n a m e ) : : t e x t = ' L i s b o a ' : : t e x t ) A N D ( ( s t a t e _ n a m e ) : : t e x t = ' L i s b o a ' : : t e x t ) ) R o w s R e m o v e d b y F i l t e r : 1 9 7 7 7 6 https://github.com/tvondra/create-statistics-talk https://www. 2ndQuadrant.com

pgconf.eu 2018 Lisbon, October 23-26, 2018 P ( A & B ) = P ( A ) * P ( B ) https://github.com/tvondra/create-statistics-talk https://www. 2ndQuadrant.com

pgconf.eu 2018 Lisbon, October 23-26, 2018 S E L E C T * F R O M z i p _ c o d e s W H E R E p l a c e _ n a m e = ' L i s b o a ' A N D s t a t e _ n a m e = ' L i s b o a ' ; P ( p l a c e _ n a m e = ' L i s b o a ' & c o u n t y _ n a m e = ' L i s b o a ' ) = P ( p l a c e _ n a m e = ' L i s b o a ' ) * P ( s t a t e _ n a m e = ' L i s b o a ' ) = 0 . 0 4 1 5 * 0 . 1 6 5 5 6 7 = 0 . 0 0 6 8 7 1 0 3 0 5 2 0 6 9 4 1 * 0 . 0 0 6 8 7 1 0 3 0 5 = 1 4 2 1 . 8 9 8 https://github.com/tvondra/create-statistics-talk https://www. 2ndQuadrant.com

pgconf.eu 2018 Lisbon, October 23-26, 2018 Underestimate E X P L A I N ( A N A L Y Z E , T I M I N G o f f ) S E L E C T * F R O M z i p _ c o d e s W H E R E p l a c e _ n a m e = ' L i s b o a ' A N D s t a t e _ n a m e = ' L i s b o a ' ; Q U E R Y P L A N - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - S e q S c a n o n z i p _ c o d e s ( c o s t = 0 . 0 0 . . 5 3 7 8 . 1 1 r o w s = 1 4 2 2 w i d t h = 5 6 ) ( a c t u a l r o w s = 9 1 6 5 l o o p s = 1 ) F i l t e r : ( ( ( p l a c e _ n a m e ) : : t e x t = ' L i s b o a ' : : t e x t ) A N D ( ( s t a t e _ n a m e ) : : t e x t = ' L i s b o a ' : : t e x t ) ) R o w s R e m o v e d b y F i l t e r : 1 9 7 7 7 6 https://github.com/tvondra/create-statistics-talk https://www. 2ndQuadrant.com