Re: [HACKERS] Gather Merge - Mailing list pgsql-hackers

På torsdag 09. mars 2017 kl. 18:09:45, skrev Robert Haas <robertmhaas@gmail.com>:

On Thu, Mar 9, 2017 at 11:25 AM, Rushabh Lathia
<rushabh.lathia@gmail.com> wrote:
> I don't see this failure with the patch. Even I forced the gather merge
> in the above query and that just working fine.
>
> Attaching patch, with the discussed changes.

Committed.

 

 

I'm still getting (as of 9c2635e26f6f4e34b3b606c0fc79d0e111953a74): 

ERROR:  GatherMerge child's targetlist doesn't match GatherMerge

 

from this query:

 

EXPLAIN ANALYSE SELECT
    em.entity_id
FROM origo_email_delivery del   JOIN origo_email_message em ON (del.message_id = em.entity_id)
WHERE 1 = 1
      AND del.owner_id = 3
      AND (         del.from_entity_id = 279519
          OR
          del.from_entity_id = 3 AND em.entity_id IN (             SELECT
                  ea_owner.message_id             FROM origo_email_address_owner ea_owner             WHERE ea_owner.recipient_id = 279519
          )     )

ORDER BY del.received_timestamp DESC
LIMIT 101 OFFSET 0;

 

Is this known or shall I provide more info/schema etc?

 

If I select del.entity_id, it works:

 

EXPLAIN ANALYSE SELECT
                    del.entity_id               FROM origo_email_delivery del                   JOIN origo_email_message em ON (del.message_id = em.entity_id)               WHERE 1 = 1
                      AND del.owner_id = 3
                      AND (                         del.from_entity_id = 279519
                          OR
                          del.from_entity_id = 3 AND em.entity_id IN (                             SELECT
                                  ea_owner.message_id                             FROM origo_email_address_owner ea_owner                             WHERE ea_owner.recipient_id = 279519
                          )                     )
               ORDER BY del.received_timestamp DESC
                LIMIT 101 OFFSET 0;

 

Plan is:

│ Limit  (cost=1259.72..15798.21 rows=101 width=16) (actual time=152.946..153.269 rows=34 loops=1)                                                                                                             │
│   ->  Gather Merge  (cost=1259.72..3139414.43 rows=21801 width=16) (actual time=152.945..153.264 rows=34 loops=1)                                                                                            │
│         Workers Planned: 4                                                                                                                                                                                   │
│         Workers Launched: 4                                                                                                                                                                                  │
│         ->  Nested Loop  (cost=259.66..3135817.66 rows=5450 width=16) (actual time=95.295..137.549 rows=7 loops=5)                                                                                           │
│               ->  Parallel Index Scan Backward using origo_email_received_idx on origo_email_delivery del  (cost=0.42..312163.56 rows=10883 width=32) (actual time=0.175..121.434 rows=6540 loops=5)         │
│                     Filter: ((owner_id = 3) AND ((from_entity_id = 279519) OR (from_entity_id = 3)))                                                                                                         │
│                     Rows Removed by Filter: 170355                                                                                                                                                           │
│               ->  Index Only Scan using origo_email_message_pkey on origo_email_message em  (cost=259.24..259.45 rows=1 width=8) (actual time=0.002..0.002 rows=0 loops=32699)                               │
│                     Index Cond: (entity_id = del.message_id)                                                                                                                                                 │
│                     Filter: ((del.from_entity_id = 279519) OR ((del.from_entity_id = 3) AND (hashed SubPlan 1)))                                                                                             │
│                     Rows Removed by Filter: 1                                                                                                                                                                │
│                     Heap Fetches: 0                                                                                                                                                                          │
│                     SubPlan 1                                                                                                                                                                                │
│                       ->  Index Scan using origo_email_address_owner_recipient_id_idx on origo_email_address_owner ea_owner  (cost=0.43..258.64 rows=69 width=8) (actual time=0.032..0.294 rows=175 loops=5) │
│                             Index Cond: (recipient_id = 279519)                                                                                                                                              │
│ Planning time: 1.372 ms                                                                                                                                                                                      │
│ Execution time: 170.859 ms                                                                                                                                                                                   │

 

 

--

Andreas Joseph Krogh