Search Postgresql Archives

Optimise OR condiditions across multiple joins

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

 



Dear all,

Is there a way to efficiently perform OR conditions across multiple
joins?

For example, I have the following statement:

SELECT RECORD.id
FROM   RECORD
       left join string
              ON string.record_id = RECORD.id
                 AND string.layout_id = 6
       left join DATE
              ON DATE.record_id = RECORD.id
                 AND DATE.layout_id = 8
WHERE  ( string.value_index = 'beverley'
          OR DATE.value = '1980-11-16' );

Which when analysed produces the output at the end of this email. It
appears to be doing a slow filter across all values, once it's
completed the joins across all rows.

If I change the OR condition to an AND condition, then indexes are used
to good effect and the query is very fast.

Does anyone have any advice as to how I can efficiently use indexes for
the OR condition and substantially improve the query time? I can
provide full details of all my current indexes if that helps.

PostgreSQL 9.4.8 on Debian 8.5

Thanks,

Andy


 Hash Right Join  (cost=176359.93..266784.76 rows=135 width=8) (actual time=17189.826..17845.939 rows=15 loops=1)
   Hash Cond: (string.record_id = record.id)
   Filter: (((string.value_index)::text = 'beverley'::text) OR (date.value = '1980-11-16'::date))
   Rows Removed by Filter: 2094748
   ->  Bitmap Heap Scan on string  (cost=6683.45..76441.68 rows=305938 width=19) (actual time=45.417..681.269 rows=285099 loops=1)
         Recheck Cond: (layout_id = 6)
         Heap Blocks: exact=19548
         ->  Bitmap Index Scan on string_idx_layout_id  (cost=0.00..6606.97 rows=305938 width=0) (actual time=39.527..39.527 rows=285103 loops=1)
               Index Cond: (layout_id = 6)
   ->  Hash  (cost=133376.36..133376.36 rows=2088250 width=12) (actual time=15917.540..15917.540 rows=2094763 loops=1)
         Buckets: 16384  Batches: 32  Memory Usage: 2611kB
         ->  Hash Right Join  (cost=71333.62..133376.36 rows=2088250 width=12) (actual time=7041.758..12268.693 rows=2094763 loops=1)
               Hash Cond: (date.record_id = record.id)
               ->  Seq Scan on date  (cost=0.00..46095.40 rows=285676 width=12) (actual time=0.871..769.841 rows=285099 loops=1)
                     Filter: (layout_id = 8)
                     Rows Removed by Filter: 855297
               ->  Hash  (cost=37072.50..37072.50 rows=2088250 width=8) (actual time=7032.415..7032.415 rows=2094763 loops=1)
                     Buckets: 16384  Batches: 32  Memory Usage: 2576kB
                     ->  Seq Scan on record  (cost=0.00..37072.50 rows=2088250 width=8) (actual time=0.037..3398.638 rows=2094763 loops=1)



-- 
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