Re: Query Plan Performance on Partitioned Table

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



# \d+ article_729
                                                         Table "public.article_729"
    Column    |            Type             |                       Modifiers                       | Storage  | Stats target | Description
--------------+-----------------------------+-------------------------------------------------------+----------+--------------+-------------
 aid          | bigint                      | not null default nextval('article_aid_seq'::regclass) | plain    |              |
 style        | smallint                    | not null default 0                                    | plain    |              |
 oaid         | bigint                      | default 0                                             | plain    |              |
 fid          | integer                     |                                                       | plain    |              |
 bid          | integer                     | default 0                                             | plain    |              |
 cid          | integer                     |                                                       | plain    |              |
 tid          | integer                     |                                                       | plain    |              |
 url          | text                        | default NULL::bpchar                                  | extended |              |
 tm_post      | timestamp without time zone |                                                       | plain    |              |
 tm_last_rply | timestamp without time zone |                                                       | plain    |              |
 author       | character varying(100)      | default NULL::bpchar                                  | extended |              |
 title        | character varying(255)      | default NULL::bpchar                                  | extended |              |
 content      | text                        |                                                       | extended |              |
 ab_content   | text                        |                                                       | extended |              |
 rply_cnt     | integer                     |                                                       | plain    |              |
 read_cnt     | integer                     |                                                       | plain    |              |
 url_hash     | character(32)               | not null                                              | extended |              |
 hash_plain   | text                        | default NULL::bpchar                                  | extended |              |
 title_hash   | character(32)               | default NULL::bpchar                                  | extended |              |
 guid         | character(32)               | default NULL::bpchar                                  | extended |              |
 neg_pos      | smallint                    | not null default 0                                    | plain    |              |
 match_code   | character(32)               | default NULL::bpchar                                  | extended |              |
 tm_spider    | timestamp without time zone |                                                       | plain    |              |
 tm_update    | timestamp without time zone |                                                       | plain    |              |
 stage        | smallint                    | not null default 0                                    | plain    |              |
 rply_cut     | integer                     | not null default 0                                    | plain    |              |
 read_cut     | integer                     | not null default 0                                    | plain    |              |
 src          | integer                     | default 0                                             | plain    |              |
 rfid         | integer                     |                                                       | plain    |              |
 labels       | integer[]                   |                                                       | extended |              |
 kwds         | integer[]                   |                                                       | extended |              |
 like_cnt     | integer                     |                                                       | plain    |              |
Indexes:
    "article_729_pkey" PRIMARY KEY, btree (aid), tablespace "indextbs"
    "article_729_url_hash" UNIQUE CONSTRAINT, btree (url_hash), tablespace "indextbs"
    "article_729_bid_titlehash_idx" btree (bid, title_hash), tablespace "indextbs"
    "article_729_fid_idx" btree (fid), tablespace "indextbs"
    "article_729_guid_idx" btree (guid), tablespace "indextbs"
    "article_729_labels_idx" gin (labels), tablespace "data1tbs"
    "article_729_mtcode_idx" btree (match_code), tablespace "indextbs"
    "article_729_rfid_author_idx" btree (rfid, author), tablespace "indextbs"
    "article_729_stage_idx" btree (stage), tablespace "data1tbs"
    "article_729_time_style_idx" btree (tm_post DESC, style), tablespace "data1tbs"
    "article_729_tm_spider_idx" btree (tm_spider), tablespace "indextbs"
    "article_729_tm_update_idx" btree (tm_update), tablespace "data1tbs"
Check constraints:
    "article_729_cid_check" CHECK (cid = 729)
Foreign-key constraints:
    "article_729_cid_fk" FOREIGN KEY (cid) REFERENCES company(cid) ON DELETE CASCADE
Triggers:
    trg_article_729_delete AFTER DELETE ON article_729 FOR EACH ROW EXECUTE PROCEDURE fn_article_delete()
    trg_article_729_insert AFTER INSERT ON article_729 FOR EACH ROW EXECUTE PROCEDURE fn_article_insert()
    trg_article_729_update AFTER UPDATE ON article_729 FOR EACH ROW EXECUTE PROCEDURE fn_article_update()
Inherits: article
Has OIDs: no

2015-08-11 22:00 GMT+08:00 Rural Hunter <ruralhunter@xxxxxxxxx>:
# \d article_729
                                     Table "public.article_729"
    Column    |            Type             |                       Modifiers                      
--------------+-----------------------------+-------------------------------------------------------
 aid          | bigint                      | not null default nextval('article_aid_seq'::regclass)
 style        | smallint                    | not null default 0
 oaid         | bigint                      | default 0
 fid          | integer                     |
 bid          | integer                     | default 0
 cid          | integer                     |
 tid          | integer                     |
 url          | text                        | default NULL::bpchar
 tm_post      | timestamp without time zone |
 tm_last_rply | timestamp without time zone |
 author       | character varying(100)      | default NULL::bpchar
 title        | character varying(255)      | default NULL::bpchar
 content      | text                        |
 ab_content   | text                        |
 rply_cnt     | integer                     |
 read_cnt     | integer                     |
 url_hash     | character(32)               | not null
 hash_plain   | text                        | default NULL::bpchar
 title_hash   | character(32)               | default NULL::bpchar
 guid         | character(32)               | default NULL::bpchar
 neg_pos      | smallint                    | not null default 0
 match_code   | character(32)               | default NULL::bpchar
 tm_spider    | timestamp without time zone |
 tm_update    | timestamp without time zone |
 stage        | smallint                    | not null default 0
 rply_cut     | integer                     | not null default 0
 read_cut     | integer                     | not null default 0
 src          | integer                     | default 0
 rfid         | integer                     |
 labels       | integer[]                   |
 kwds         | integer[]                   |
 like_cnt     | integer                     |
Indexes:
    "article_729_pkey" PRIMARY KEY, btree (aid), tablespace "indextbs"
    "article_729_url_hash" UNIQUE CONSTRAINT, btree (url_hash), tablespace "indextbs"
    "article_729_bid_titlehash_idx" btree (bid, title_hash), tablespace "indextbs"
    "article_729_fid_idx" btree (fid), tablespace "indextbs"
    "article_729_guid_idx" btree (guid), tablespace "indextbs"
    "article_729_labels_idx" gin (labels), tablespace "data1tbs"
    "article_729_mtcode_idx" btree (match_code), tablespace "indextbs"
    "article_729_rfid_author_idx" btree (rfid, author), tablespace "indextbs"
    "article_729_stage_idx" btree (stage), tablespace "data1tbs"
    "article_729_time_style_idx" btree (tm_post DESC, style), tablespace "data1tbs"
    "article_729_tm_spider_idx" btree (tm_spider), tablespace "indextbs"
    "article_729_tm_update_idx" btree (tm_update), tablespace "data1tbs"
Check constraints:
    "article_729_cid_check" CHECK (cid = 729)
Foreign-key constraints:
    "article_729_cid_fk" FOREIGN KEY (cid) REFERENCES company(cid) ON DELETE CASCADE
Triggers:
    trg_article_729_delete AFTER DELETE ON article_729 FOR EACH ROW EXECUTE PROCEDURE fn_article_delete()
    trg_article_729_insert AFTER INSERT ON article_729 FOR EACH ROW EXECUTE PROCEDURE fn_article_insert()
    trg_article_729_update AFTER UPDATE ON article_729 FOR EACH ROW EXECUTE PROCEDURE fn_article_update()
Inherits: article

2015-08-11 21:53 GMT+08:00 Maxim Boguk <maxim.boguk@xxxxxxxxx>:


On Tue, Aug 11, 2015 at 11:44 PM, Rural Hunter <ruralhunter@gmail.com> wrote:
# \dt+
​​
article_729
                      List of relations
 Schema |    Name     | Type  | Owner  |  Size  | Description
--------+-------------+-------+--------+--------+-------------
 public | article_729 | table | omuser1 | 655 MB |
(1 row)
The problem exists on not only this specific child table, but with all of them.

​Oops sorry, оf course I mean "\d+​
 ​ article_729
​" (to see criteria used for partitioning).​



--
Maxim Boguk
Senior Postgresql DBA
http://www.postgresql-consulting.ru/




[Postgresql General]     [Postgresql PHP]     [PHP Users]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Yosemite]

  Powered by Linux