Hi Jorge, Can you create a view in the source database such as CREATE VIEW xxx_id_attributes AS SELECT id, CAST(attributes->>'account_incident_id' AS integer) FROM xxx WHERE attributes->'account_incident_id' ~ '^[0-9]+$';
On the remote server, create a foreign table on the new view and perform your test. Just curious. -Greg On Wed, Apr 17, 2019 at 2:59 PM Jorge Torralba <jorge.torra...@gmail.com> wrote: > I made a copy of the table and altered the column from hstore to jsonb. > > Ran the following query with the same performance issues. > > SELECT id, attributes FROM xxx WHERE account_id = 1 AND timestamp >= > '2019-01-16 22:34:28.584' AND CAST(attributes ->> 'account_incident_id' as > integer) = 2617116 order by timestamp desc limit 10; > > Things to Note. > > Remove the CAST on the attributes column and the order by results in quick > performance > > Add order by performance dies > > Add CAST without the order by you can go out for dinner and still be > waiting for a result set. > > > > > > On Wed, Apr 17, 2019 at 7:02 AM Tom Lane <t...@sss.pgh.pa.us> wrote: > >> Thomas Kellerer <spam_ea...@gmx.net> writes: >> > Laurenz Albe schrieb am 17.04.2019 um 07:03: >> >> After debugging into this, it seems that the hstore operator -> cannot >> >> be pushed down because of collation problems. >> >> > Do you happen to know if the JSONB operator -> (or ->>) can be pushed >> down? >> >> A bit of experimentation says that jsonb -> integer can be pushed down, >> but not any of the variants involving a text fieldname or result. >> Presumably this is because of the heuristic that says not to push down >> a collation that didn't arise from the remote column. jsonb -> text >> isn't really collation-sensitive, of course, but postgres_fdw has no >> good way to know that, since the core code (outside of that operator >> itself) doesn't know it either. The assumption is that any function >> with at least one input of a collatable type is collation-sensitive. >> Here you're getting a default collation from the text literal, and >> postgres_fdw doesn't want to assume that the remote end would choose >> the same collation. >> >> regards, tom lane >> >> >> > > -- > Thanks, > > Jorge Torralba > ---------------------------- > > Note: This communication may contain privileged or other confidential > information. If you are not the intended recipient, please do not print, > copy, retransmit, disseminate or otherwise use the information. Please > indicate to the sender that you have received this email in error and > delete the copy you received. Thank You. >
