Greetings,
* Job (j...@colliniconsulting.it) wrote:
> here is primary a partitioned table (for 20/2/2017 logs):
> flashstart=# \d webtraffic_archive_day_2017_02_20;
> Table
> "public.webtraffic_archive_day_2017_02_20"
> Column | Type |
> Modifiers
> -----------+-----------------------------+--------------------------------------------------------------------------------
> id | numeric(1000,1) | not null default
> function_get_next_sequence('webtraffic_archive_id_seq'::text)
> timestamp | timestamp without time zone |
> domain | character varying(255) |
> action | integer |
> profile | character varying(50) |
> accessi | integer |
> url | text |
> Indexes:
> "webtraffic_archive_day_2017_02_20_action_wbidx" btree (action)
> "webtraffic_archive_day_2017_02_20_domain_wbidx" btree (domain)
> "webtraffic_archive_day_2017_02_20_profile_wbidx" btree (profile)
> "webtraffic_archive_day_2017_02_20_timestamp_date_wbidx" brin
> (("timestamp"::date))
> "webtraffic_archive_day_2017_02_20_timestamp_time_wbidx" btree
> (("timestamp"::time without time zone))
> "webtraffic_archive_day_2017_02_20_timestamp_wbidx" brin ("timestamp")You don't have a CHECK constraint on your individual partitions, so PG has no idea which of the partitions contains data from what time-period. To have PostgreSQL's constraint exclusion work to eliminate entire partitions, you need to add a CHECK constraint on the partition key. In this case, it looks like you'd want something like: CHECK (timestamp >= partition_date AND timestamp < partition_date+1) Unfortunately, that'll require locking each table and scanning it to make sure that the CHECK constraint isn't violated. A couple of other notes- you probably don't need both a BRIN and a btree index on the same column. If the BRIN works well enough for you then you can drop the btree index. If it doesn't, then you might as well just keep the btree index and drop the BRIN. It seems equally unnecessary to have a BRIN on a cast of the column. You should also be using timestamp w/ timezone, really. > This is the query planner: > explain analyze > select * from webtraffic_archive where timestamp::date='2017-02-20' and > profile='f62467' If this is really what you're mostly doing, having constraint exclusion and an index on 'profile' would probably be enough, if you insist on continuing to have the table partitioned by day (which I continue to argue is a bad idea- based on the number of total rows you mentioned and the number of partitions, you have partitions with less than 20M rows each and that's really small, month-based partitions with a BRIN would probably work better). If you get to the point of having years worth of daily partitions, you'd going to see increases in planning time. Thanks! Stephen
signature.asc
Description: Digital signature
