I’m building a FDW around a column-store backend (similar to CStore but for genomic data!).
I have tables in the billions of rows, and have a common query pattern of asking for the table size (i.e. SELECT COUNT(*) FROM big_fdw_table; ).
This is a read-optimized system in which I know in constant time the exact dimensions of the table.
Is there any way to convince Postgres FDW to leverage the analyze row counts or even the “double* totalRowCount” returned from the AcquireSampleRows callback from my AnalyzeForeignTable function so that it does not do a full-table scan for a COUNT(*) etc?
My current fallback is to export a specialized function that returns the table row count for a given FDW table, but that then leaks into the user-application driving these queries.
Look at TABLESAMPLE, which does mostly what you're asking.
--
Simon Riggs http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
