ANALYZE'ing table hierarchies

From: nunks <nunks(dot)lol(at)gmail(dot)com>
To: pgsql-admin(at)postgresql(dot)org
Subject: ANALYZE'ing table hierarchies
Date: 2016-02-19 02:02:30
Message-ID: CACq6szTXye=LzyFHnfYe-zZFZ-=TTG45uECs_o5_qOArfB=RZQ@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-admin

Hello,

I have a question about running ANALYZE on table hierarchies.

The documentation page for partitioning mentions the need to manually issue
VACUUM and ANALYZEs for each partition (
http://www.postgresql.org/docs/9.4/static/ddl-partitioning.html#DDL-PARTITIONING-CAVEATS),
while the page for ANALYZE (
http://www.postgresql.org/docs/9.4/static/sql-analyze.html) mentions it
does gather statistics for the master table and its children.

When I ANALYZE a master table PostgreSQL says it is working on the entire
hierarchy and automatically ANALYZEs the child tables. However, the
last_analyze column stays empty for the child tables on pg_stat_user_tables.

I find this somewhat confusing, what is the expected behavior? I figure the
child tables have been properly ANALYZEd, but I can't find it registered
anywhere. It would be nice to be able to parse pg_stat_user_tables for
analyze statistics without having to worry about hierarchies, since each
partition seems to have its own statistics for everything else.

nunks=# analyze verbose tb05;

INFO: analyzing "public.tb05"

INFO: "tb05": scanned 0 of 0 pages, containing 0 live rows and 0 dead
rows; 0 rows in sample, 0 estimated total rows

INFO: analyzing "public.tb05" inheritance tree

INFO: "tb05_2016": scanned 165 of 165 pages, containing 7465 live
rows and 455 dead rows; 7465 rows in sample, 7465 estimated total rows

INFO: "tb05_2015": scanned 381 of 381 pages, containing 16281 live
rows and 1504 dead rows; 16281 rows in sample, 16281 estimated total rows

ANALYZE

nunks=# select relname, last_analyze from pg_stat_user_tables where
relname like 'tb05%';

relname | last_analyze

-----------+-------------------------------

tb05 | 2016-02-18 22:47:32.770076-02

tb05_2016 |

tb05_2015 |

(3 rows)

nunks=# \d+ tb05;

Table
"public.tb05"

Column | Type | Modifiers |
Storage | Stats target | Description

---------------------+-----------------------------+-----------+----------+--------------+-------------

a | character varying(8) | not null |
extended | |

b | date | not null | plain
| |

c | timestamp without time zone | not null | plain
| |

d | timestamp without time zone | not null | plain
| |

e | bigint | not null | plain
| |

f | bigint | not null | plain
| |

g | bigint | not null | plain
| |

h | bigint | not null | plain
| |

i | bigint | not null | plain
| |

j | bigint | not null | plain
| |

k | bigint | not null | plain
| |

l | bigint | not null | plain
| |

m | bigint | not null | plain
| |

n | bigint | not null | plain
| |

o | bigint | not null | plain
| |

p | bigint | not null | plain
| |

q | bigint | not null | plain
| |

r | bigint | not null | plain
| |

Indexes:

"tb05_pkey" PRIMARY KEY, btree (a, b)

Triggers:

tr_partition_tb05 BEFORE INSERT ON tb05 FOR EACH ROW EXECUTE
PROCEDURE fn_partition_tb05()

Child tables: tb05_2015,

tb05_2016

Thanks!

Nunks

Responses

Browse pgsql-admin by date

  From Date Subject
Next Message ALEXANDER JOSE 2016-02-19 02:39:17 Status DISCARD ALL
Previous Message drum.lucas@gmail.com 2016-02-19 01:48:31 Re: [TIPS] Tuning PostgreSQL 9.2