<div xmlns="http://www.w3.org/1999/xhtml">Dear Mariel,</div><div xmlns="http://www.w3.org/1999/xhtml"> </div><div xmlns="http://www.w3.org/1999/xhtml">1,4. Could you please check all child tables whether they all have <em>check constraints</em> or not? Does your main table store any data? Also could you please share output of following command.</div><div xmlns="http://www.w3.org/1999/xhtml">show constraint_exclusion;</div><div xmlns="http://www.w3.org/1999/xhtml"> </div><div xmlns="http://www.w3.org/1999/xhtml">2. Filtering on comments_daily_247 table over log_server_id is not big issue for your situation. Postgres applies filtering on your results because comments_daily_247_date_comment_id_idx composite index does not contain log_server_id.</div><div xmlns="http://www.w3.org/1999/xhtml"> </div><div xmlns="http://www.w3.org/1999/xhtml">3. I think it is not related with indexes.</div><div xmlns="http://www.w3.org/1999/xhtml"> </div><div xmlns="http://www.w3.org/1999/xhtml">Best regards.</div><div xmlns="http://www.w3.org/1999/xhtml">İyi çalışmalar.</div><div xmlns="http://www.w3.org/1999/xhtml">Samed YILDIRIM</div><div xmlns="http://www.w3.org/1999/xhtml"> </div><div xmlns="http://www.w3.org/1999/xhtml"> </div><div xmlns="http://www.w3.org/1999/xhtml"> </div><div xmlns="http://www.w3.org/1999/xhtml">09.10.2018, 12:20, "Mariel Cherkassky" <mariel(dot)cherkassky(at)gmail(dot)com>:</div><blockquote xmlns="http://www.w3.org/1999/xhtml" type="cite"><div><div><div><div>Hi,</div><div>I'm trying to understand the execution plan that is chosen for my query when I run a select on a partition table . I have on my main partition table rules that redirect the insert to the right son table.</div><div> </div><div>My scheme : </div><div>Postgresql 9.6.8</div><div> </div><div>mydb=# \d comments_daily</div><div> Table "public.fw_log_daily"</div><div> Column | Type | Modifiers</div><div>---------------+-----------------------+-----------</div><div> log_server_id | bigint | not null</div><div> comment_id | bigint | not null</div><div> date | date | not null</div><div> </div><div>Rules:</div><div> comments_daily_1 AS</div><div> ON INSERT TO fw_log_daily</div><div> WHERE new.log_server_id = 1::bigint DO INSTEAD INSERT INTO comments_daily_1 (log_server_id,comment_id, date)</div><div> VALUES (new.log_server_id, new.comment_id, new.date)</div><div> </div><div> comments_daily_2 AS</div><div> ON INSERT TO fw_log_daily</div><div> WHERE new.log_server_id = 1::bigint DO INSTEAD INSERT INTO comments_daily_2 (log_server_id, comment_id, date)</div><div> VALUES (new.log_server_id, new.comment_id, new.date)</div><div> </div><div> and so on...</div><div> </div><div> </div><div>The son table structure : </div><div>mydb=# \d comments_daily_247</div><div> Table "public.comments_daily_247"</div><div> Column | Type | Modifiers</div><div>---------------+-----------------------+-----------</div><div> log_server_id | bigint | not null</div><div> comment_id | bigint | not null</div><div> date | date | not null</div><div> </div><div>Indexes:</div><div> "comments_daily_247_date_device_id_idx" btree (date, device_id)</div><div>Check constraints:</div><div> "comments_daily_247_log_server_id_check" CHECK (log_server_id = 247::bigint)</div><div>Inherits: comments_daily</div></div><div> </div><div> </div><div> </div><div>the query : </div><div>mydb=# explain</div><div>SELECT * FROM comments_daily</div><div>where</div><div>log_server_id in (247)</div><div>AND</div><div>comments_daily.date >= '<span>2017-04-12</span>'</div><div>AND</div><div>comments_daily.date <= '<span>2017-04-12</span>'</div><div>AND</div><div>comment_id IN (1256);</div><div> QUERY PLAN</div><div>----------------------------------------------------------------------------------------------------------------------------------</div><div> Append (cost=0.00..47368.49 rows=2 width=186)</div><div> -> Seq Scan on comments_daily (cost=0.00..47360.30 rows=1 width=186)</div><div> Filter: ((date >= '<span>2017-04-12</span>'::date) AND (date <= '<span>2017-04-12</span>'::date) AND (log_server_id = 247) AND (comment_id = 1256))</div><div> -> Index Scan using comments_daily_247_date_comment_id_idx on comments_daily_247 (cost=0.15..8.19 rows=1 width=186)</div><div> Index Cond: ((date >= '<span>2017-04-12</span>'::date) AND (date <= '<span>2017-04-12</span>'::date) AND (comment_id = 1256))</div><div> Filter: (log_server_id = 247)</div><div>(6 rows)</div><div> </div><div>traffic_log_db=#</div></div><div> </div><div>I had 2 questions : </div><div>1)Why the filtering on the main comments_daily table is according to all the where clause and not only according the log_server_id?</div><div>2)Why the filtering on the son table is according to the log_server_id ? Is it because of the check constraint ?</div><div>3)Should I create another index to improve the performance ?</div><div>4)Any suggestions ?</div></div></blockquote>