CODEWITHSUNDEEP
postgresqlhibernatepartitioningdatabase-partitioning
Sachin Verma
Sachin Verma

Reputation: 3802

Hibernate with partitioned table

Using database postgresql 9.5.

I have a table employee_shift with rows 110966498, so to improve inserts I've partitioned this table on months for 20 years (Jan 2000 to Dec 2020 ie 240 partitioned tables so far).This is on a column date in table.

Now that my inserts are faster(done by native query) but my existing DAO layer is using HQL which hits the employee_shift table rather than hitting employee_shift_2010_10 (year_month), hence my select statements are relatively much much slower because it checks in all partitions.

Is there an way hibernate can directly hit the employee_shift_2010_10, if I use select statements using date column?

What are my other options to make my selects faster in this case?

Upvotes: 2

Views: 2914

Answers (1)

Roman Tkachuk
Roman Tkachuk

Reputation: 3266

Probably you do not set constraints for inherited table, or you use parametrize query. 

CREATE TABLE a (id serial PRIMARY KEY, ts timestamp);
CREATE INDEX a_ts ON a (ts);

CREATE TABLE a_2010 ( CONSTRAINT data_2011_check CHECK (ts >= '2010-01-01 00:00:00'::timestamp AND ts < '2011-01-01 00:00:00'::timestamp))  INHERITS (a);
CREATE TABLE a_2011 ( CONSTRAINT data_2012_check CHECK (ts >= '2011-01-01 00:00:00'::timestamp AND ts < '2012-01-01 00:00:00'::timestamp))  INHERITS (a);
CREATE TABLE a_2012 ( CONSTRAINT data_2013_check CHECK (ts >= '2012-01-01 00:00:00'::timestamp AND ts < '2013-01-01 00:00:00'::timestamp))  INHERITS (a);
CREATE TABLE a_2013 ( CONSTRAINT data_2014_check CHECK (ts >= '2013-01-01 00:00:00'::timestamp AND ts < '2014-01-01 00:00:00'::timestamp))  INHERITS (a);
CREATE INDEX a_ts_2010 ON a_2010 (ts);
CREATE INDEX a_ts_2011 ON a_2011 (ts);
CREATE INDEX a_ts_2012 ON a_2012 (ts);
CREATE INDEX a_ts_2013 ON a_2013 (ts);

After that as you can see postgresql check inherited table by constraint. Constraint must be not overlapped.

EXPLAIN ANALYZE SELECT * FROM a WHERE ts = '2011-02-01 00:00:00';


                                                       QUERY PLAN                                                        
-------------------------------------------------------------------------------------------------------------------------
 Append  (cost=0.00..14.79 rows=11 width=12) (actual time=0.006..0.006 rows=0 loops=1)
   ->  Seq Scan on a  (cost=0.00..0.00 rows=1 width=12) (actual time=0.003..0.003 rows=0 loops=1)
         Filter: (ts = '2011-02-01 00:00:00'::timestamp without time zone)
   ->  Bitmap Heap Scan on a_2011  (cost=4.23..14.79 rows=10 width=12) (actual time=0.003..0.003 rows=0 loops=1)
         Recheck Cond: (ts = '2011-02-01 00:00:00'::timestamp without time zone)
         ->  Bitmap Index Scan on a_ts_2011  (cost=0.00..4.23 rows=10 width=0) (actual time=0.003..0.003 rows=0 loops=1)
               Index Cond: (ts = '2011-02-01 00:00:00'::timestamp without time zone)
 Planning time: 1.148 ms
 Execution time: 0.046 ms
(9 rows)

Upvotes: 2

Related Questions