I have a large CSV file sorted on the first column. Here is an extremely simplified version):
a 7 a 4 b 6 c 3 c 9 c 2
I want to group by the first column and sum the second.
How do I tell the PostgreSQL foreign data wrapper (fdw) to assume that the file is sorted on the first column, thereby not scanning the entire file before producing the output?
I am using version 11.1
Update: The above example is dramatically simplified. In the real life use case the first column has a very high number of values. It is the Google ngrams data set where first column is a phrase, second is the year, third is the occurrences in literature published that year. I want phrases with total count over all years greater than say 10,000. A simple python program can stream the result efficiently. So why not PostgreSQL? FDW could simulate an index over the table if informed of the sort order, just as it simulates the table from the CSV file, so I asked if it does something like that.
My main concern is efficiency. I previously did this using Unix command line tools such as
awk but I would like to avoid juggling Unix scripts which are pretty much specific hand crafted execution plans. Save me PostgreSQL!!