1

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!!

  • (stale comments deleted; see update) – user2297550 Dec 5 '18 at 2:08
  • Why not just create a summarized file using a tool of your choice (for example, python, rather than juggling awk), and then mapping in that summarized file using a FDW? – jjanes Dec 5 '18 at 19:20
  • @jjanes That's exactly what I'm doing, rather being forced to do. See the last paragraph in the question: "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!!" PS: the question itself seems to have been answered with sufficient confidence -- that it is not possible to do so in current versions. I am getting the additional feedback from answerers that it is not the job of postgresql, but imo it is a natural next step of pushing down operators to FDWs. – user2297550 Dec 6 '18 at 5:23
  • FDW are an extensibility feature. You (probably) don't need a new version of PostgreSQL, you just need an appropriate extension to be written. You might be able to use Multicorn to do it directly in Python. Or use file_fdw as exemplar code to do it in C: some of the comments in there (file_fdw.c line 572) even seem to anticipate such a usage. – jjanes Dec 6 '18 at 12:54
  • @jjanes I read the comments at file_fdw.c:572, and gee, can one say great minds think alike! Tom Lane himself :-) It seems all the machinery to connect to the planner is mostly in place. All that's needed is DDL to specify the sort order, and the lines near 572 could read the constraint and add a path! I am sorely tempted to code it up and submit a patch; however it is not quite the right time. I am fine with using my existing hacks and look forward to a future version doing this. Thank you again! – user2297550 Dec 11 '18 at 8:22
1

I'm afraid not.

There is no such thing as a "sorted csv" file format, all you can have is a csv file that just happens to be sorted. I could take your example file and move the top line to the bottom and it would still be a valid csv.

If the fdw made that trusted your assertion and you made a mistake it would return incorrect results which is obviously unacceptable.

So, you will either want to import that file if you are doing lots of processing, convert it to a sorted db file format that fdw supports or pre summarize the data if that is all you are doing.

  • I kind of expected it couldn't; thanks for the confirmation. (I can't upvote yet.) A sort-order violation is very easy to detect, so a smarter csv fdw could fail if the assertion were incorrect. (My fdw failed mid-query for other reasons of bad data in the CSV so fdw failing mid-query is inherently tolerable) – user2297550 Dec 4 '18 at 14:16
  • It could only detect it if it scanned the whole file first,defeating any performance improvement. PS - always listen carefully to ahwnn - he knows an awful lot – Richard Huxton Dec 4 '18 at 21:26
  • The significant performance gains would accrue robustly to the programmers who correctly asserted the sort order on correct data. Incorrect results would be presented to those who made incorrect assertions or had bad data. Huge CSV data sets such as the 100+ GB of Google n-grams data are produced by other automated programs, with exceptionally strong guarantees on sort order. (disagree?) Why penalize users of such massive data sets by having them unzip and load into a native format, merely because of the possibility of a different class of less than competent users getting bad results? – user2297550 Dec 5 '18 at 1:59
  • PS: I appreciate someone knowing a lot but that implicitly leads to the pitfall of summarily dismissing something reasonable as out of hand. I think my simplified example threw this question off track. If I had presented upfront the 100+ GB compressed google n-gram data that I wish to do a sum(count) ... group by phrase on, it would be have been treated less dismissively. Once someone takes a very strong position dismissing this, it is super difficult to backtrack. I know a bit myself and I would never dismiss a soul wrestling with so much data that the fdw shouldn't push down the group by. – user2297550 Dec 5 '18 at 2:07
  • There are lots of ways you can get wrong answers through negligent conduct. It is not obviously unacceptable for a niche extension to make use of well-documented prerequisites if doing so gives a sufficient benefit. I doubt such a FDW would get accepted as core PostgreSQL code, but that is no reason to prevent someone from making their own FDW, and distributing it through github or PGXN. The bigger challenge would be interfacing the FDW with the PostgreSQL planner in way to properly convey what operations can be done efficiently and what ones cannot, and collation safety. – jjanes Dec 5 '18 at 19:31

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.