I'm using a view based on two partitioned tables (they are partitioned based on same field/value) . When trying to access to a specific partition and using the explain analyse I can see postgres accesses directly to the partition I want (in both tables).

Then, created a view which is a join between both tables:

create myView as
FROM   tableA
       inner join tableB 
               ON tableA.DATE = tableB.DATE 
                  AND tableA.custid = tableB.custid

and got the view created. It's happening because tableB is an extended tableA because this one is a general one.

Now, I want to access my view using my partitioned field and retrieve data from tableA and tableB:

SELECT tableA.field1, 
FROM   myview 
WHERE  tableA.DATE >= '20190110' 
       AND tableB.DATE < '20190111'

I've millions of records, but when using my view it's not faster because seems postgresql isn't using my partition when retrieving data.

Can someone help me? I think when using Oracle it uses the partition, but I don't know how this works in postgresql.

All partitions are being created as:

FROM ('20190101')  TO ('201901012') 

All I want to do is to access faster to my view.


This post is about SQL Server but could this my solution? https://stackoverflow.com/questions/34695748/create-partitioned-view-on-linked-tables Create the view with the union all instead inner join?

Edit: I'm using Postgresql 10.3

Edit (2) : After reading @jjanes post, below my explain analyse results and queries:

explain analyze
select * from tableA
    where dateA >= '20190109' AND dateA < '20190110';

Append  (cost=0.00..156664.83 rows=5037122 width=7) (actual time=0.039..2471.406 rows=4113139 loops=1)
  ->  Seq Scan on tableA_20190109  (cost=0.00..156664.83 rows=5037122 width=7) (actual time=0.038..2161.243 rows=4113139 loops=1)
        Filter: ((dateA >= '2019-01-09 00:00:00'::timestamp without time zone) AND (dateA < '2019-01-10 00:00:00'::timestamp without time zone))
Planning time: 13.815 ms
Execution time: 2642.065 ms

explain analyze
select * from tableB
where dateB >= '20190109' AND dateB < '20190110';

Append  (cost=0.00..131893.64 rows=4539809 width=6598) (actual time=0.020..1503.950 rows=3302085 loops=1)
  ->  Seq Scan on tableB_20190109  (cost=0.00..131893.64 rows=4539809 width=6598) (actual time=0.020..1215.671 rows=3302085 loops=1)
        Filter: ((dateB >= '2019-01-09 00:00:00'::timestamp without time zone) AND (dateB < '2019-01-10 00:00:00'::timestamp without time zone))
Planning time: 125.185 ms
Execution time: 1658.126 ms

Tried The following without success (I mean, could not get the explain analyse result) (supposedly my view's query):

explain analyse
select * from myView
where dateB >= '20190109' AND dateB < '20190110';

explain analyse
select * from myView
where dateB = '20190109' ;

explain analyse
   FROM tableA u
     JOIN tableB t ON u.custid=t.custid AND t.date = u.date
     and dateB >= '20190109' AND dateB < '20190110';

"custid"is the PK of both tables.

  • 1
    Does it use the partitions if you skip the view and write the equivalent query against the base tables? – jjanes Jan 12 at 19:50
  • Please read dba.stackexchange.com/tags/postgresql-performance/info and edit your question appropriately, especially the part about including "explain (analyze)" – jjanes Jan 12 at 19:51
  • @jjanes Tried the query and could not get the explain plan. sorry but this is my first thread ever. Hope it's correct now. – diguex Jan 13 at 11:07
  • After some days... Got the results expected. Just edit my code to use the correct values as partition. – diguex Jan 30 at 15:45

Your Answer

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

Browse other questions tagged or ask your own question.