Search Postgresql Archives

Re: a JOIN to a VIEW seems slow

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

 



On Thu, Sep 14, 2017 at 8:17 AM, Pavel Stehule <pavel.stehule@xxxxxxxxx> wrote:
> 2017-09-14 15:09 GMT+02:00 Pavel Stehule <pavel.stehule@xxxxxxxxx>:
>>
>>
>>
>> 2017-09-14 14:59 GMT+02:00 Frank Millman <frank@xxxxxxxxxxxx>:
>>>
>>> Pavel Stehule wrote:
>>>
>>> 2017-09-14 10:14 GMT+02:00 Frank Millman <frank@xxxxxxxxxxxx>:
>>>>
>>>> Hi all
>>>>
>>>> This is a follow-up to a recent question I posted regarding a slow
>>>> query. I thought that the slowness was caused by the number of JOINs in the
>>>> query, but with your assistance I have found the true reason. I said in the
>>>> previous thread that the question had become academic, but now that I
>>>> understand things better, it is no longer academic as it casts doubt on my
>>>> whole approach.
>>>>
>>>> I have split my AR transaction table into three physical tables –
>>>> ar_tran_inv, ar_tran_crn, ar_tran_rec. I will probably add others at some
>>>> point, such as ar_tran_jnl.
>>>>
>>>> I then create a VIEW to view all transactions combined. The view is
>>>> created like this -
>>>>
>>>> CREATE VIEW ar_trans AS
>>>>   SELECT ‘ar_inv’ AS tran_type, row_id AS tran_row_id, tran_number ...
>>>> FROM ar_tran_inv WHERE posted = ‘1’
>>>>   UNION ALL
>>>>   SELECT ‘ar_crn’ AS tran_type, row_id AS tran_row_id, tran_number ...
>>>> FROM ar_tran_crn WHERE posted = ‘1’
>>>>   UNION ALL
>>>>   SELECT ‘ar_rec’ AS tran_type, row_id AS tran_row_id, tran_number ...
>>>> FROM ar_tran_rec WHERE posted = ‘1’
>>>>
>>>> I have another table called ‘ar_trans_due’, to keep track of outstanding
>>>> transactions. All of the three transaction types generate entries into this
>>>> table. To identify the source of the transaction, I have created columns in
>>>> ar_trans_due called ‘tran_type’ and ‘tran_row_id’. After inserting a row
>>>> into ‘ar_tran_inv’, I invoke this -
>>>>
>>>>   INSERT INTO ar_trans_due (tran_type, tran_row_id, ...) VALUES
>>>> (‘ar_inv’, ar_tran_inv.row_id, ...), and similar for the other transaction
>>>> types. It is handled by a Python program, and it all happens within a
>>>> transaction.
>>>>
>>>> When I view a row in ar_trans_due, I want to retrieve data from the
>>>> source transaction, so I have this -
>>>>
>>>>   SELECT * FROM ar_trans_due a
>>>>   LEFT JOIN ar_trans b ON b.tran_type = a.tran_type AND b.tran_row_id =
>>>> a.tran_row_id
>>>>
>>>> I understand that PostgreSQL must somehow follow a path from the view
>>>> ‘ar_trans’ to the physical table ‘ar_tran_inv’, but I assumed it would
>>>> execute the equivalent of SELECT * FROM ar_tran_inv WHERE row_id =
>>>> a.tran_row_id AND posted = ‘1’.
>>>>
>>>> If this was the case, it would be an indexed read, and very fast.
>>>> Instead, according to EXPLAIN, it performs a sequential scan of the
>>>> ‘ar_tran_inv’ table.
>>>>
>>>> It also scans ‘ar_tran_crn’ and ‘ar_tran_rec’, but EXPLAIN shows that it
>>>> uses a Bitmap Heap Scan on those. I assume that is because the tables are
>>>> currently empty.
>>>>
>>>> Is this analysis correct?
>>>
>>>
>>> please, send EXPLAIN ANALYZE result :)
>>>>
>>>>
>>>
>>>
>>> I tried to reduce this to its simplest form.
>>>
>>> Here is a SQL statement -
>>>
>>>     SELECT *
>>>     FROM ccc.ar_trans_due a
>>>     LEFT JOIN ccc.ar_trans b ON
>>>         b.tran_type = a.tran_type AND b.tran_row_id = a.tran_row_id
>>>     WHERE a.row_id = 1
>>>
>>> ar_trans_due is a physical table, ar_trans is a view.
>>>
>>> It takes about 28ms. Here is the explain -
>>> https://explain.depesz.com/s/8YY
>>>
>>>
>
>
> The PostgreSQL cannot to push join - in slow case, the UNIONS should be done
> first - and it requires full scan ar_tran_inv - used filter (posted AND
> (deleted_id = 0) is not too effective - maybe some composite or partial
> index helps.


In my testing JOINS can push through UNION ALL.   Why do we need to
materialize  union first?  What version is this?

merlin


-- 
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general




[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]

  Powered by Linux