On 16.03.2012 10:44, Etsuro Fujita wrote: > I have a plan to support 'Create index on foreign table' for 9.3. Here > is my plan. > > The index creation is supported for a flat file such as CSV and a remote > table on a RDB e.g., Postgres using CREATE INDEX. (I thought using a > new statement, CREATE FOREIGN INDEX, at first, but I think that CREATE > INDEX would be sufficient to define an index for the foreign table.) > For a flat file, CREATE INDEX constructs an index in the same way as an > index for a regular table.
I think this belongs completely in the remote data source. If you want to index flat files, create an extra file for it or something, and enhance the wrapper so that it can take advantage of it. Keeping the index inside the database while the data is somewhere else creates a whole new class of problems. For starters, how would you keep the index up-to-date when the flat file is modified? If you want to take advantage of PostgreSQL's indexing, you'll just have to just load the data into a regular table. > On the other hand, for a remote table, > CREATE INDEX collects information about the index on the specified > column(s) for the specified table that was created on the remote table. I don't see the point of this either. The planner asks the FDW for cost estimates, and if the FDW knows about indexes in the remote server, it can certainly adjust the estimates accordingly. But that's all internal to the FDW. It might want delegate the whole cost estimation to the remote server by running EXPLAIN there, or it could use its knowledge of indexes that exist there, but I don't see why the rest of the system would need to know what indexes there are in the remote system. If the FDW needs that information, it can query the remote server for it on first access, and cache the information for the lifetime of the session. -- Heikki Linnakangas EnterpriseDB http://www.enterprisedb.com -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
