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 agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.