Performance differences between fdw and view+fdw

From: Elías David <elias(dot)moreno(dot)tec(at)gmail(dot)com>
To: pgsql-admin <pgsql-admin(at)postgresql(dot)org>
Subject: Performance differences between fdw and view+fdw
Date: 2015-08-03 03:46:57
Message-ID: CANNE3r0pz+hOmHQd6EkvVtr3y+yXGx+vDkg12niOQw5iO-h9Lg@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-admin

Hello all,

I'm trying to understand a behavior I'm currently seeing while testing
mongo's fdw (using the mongo-c-driver 1.0 version).

Say I have a foreign table with 50 columns and 14000 rows, I also have a
view that does basically the same thing as querying the foreign table
directly except for a few castings.

Working with the foreign table directly, a query like select * from
foreign_table takes like 7 seconds, doing the same query but against the
view takes like 8.5 seconds

Another example, a select * from foreign_table where col = 'something'
takes like 12 ms but doing the same against the view takes like 110ms.

The only thing I could put the blame on when using the view is that inside
is the same query as working with the foreign table but I use casts for a
few columns like select col::text from foreign_table...

I don't know if anyone has tried mongo_fdw or something similar but I would
like to know if this behavior is normal and more importantly why the view
seems inefficient compared to query the foreign table directly (and without
casts)

Thanks in advance!!

Browse pgsql-admin by date

  From Date Subject
Next Message jkilborn 2015-08-04 12:46:37 postgres not deleting trigger file
Previous Message John Scalia 2015-07-31 12:40:33 Re: Autovacuum behavior