I am using Postgres file data wrapper(file_fdw) to perform query from a remote file. Interestingly, I saw that if Postgres perform a query on a binary file, it uses more main memory space than text file such as .csv. Can anyone confirm why is this happen? Is there any way to optimize this?
My file contains two column (id | geometry), here geometry represents polygon. So, my foreign table consists of two column. I have tested with join query like ST_Overlaps() and ST_Contains() on CSV file and Postgres compatible binary file.
SELECT COUNT(*) FROM ftable1 a, ftable2 b WHERE ST_Overlaps(a.geom, b.geom);
I have checked the memory usage by PostgreSQL using htop, I saw that if Postgres perform a query on a binary file, it uses more main memory space than CSV file. For example, if it is 500MB for CSV then almost 1GB for a binary file. why?