We have a very huge table with around 10 partitions in our system. The table data has multiple references in other tables. The database administrator has advised us to avoid performing any join query against the table. How can I efficiently fetch the data in a paginated way from the table where as the filter that I am passing contains the references of other tables as well. The database is postgres.
If you're not allowed to join, then why bother with an RDBMS? Not that you could save money on the DB, but you could loose the DBA. Even with things being the way they are, I'm not sure there is an efficient solution to fetch large amounts of data in a "paginated way". Depending on your client, you could use a bidirectional cursor, allowing the user to "page" forward an backwards. This "cursor" has to fit in memory somewhere.
Now, to respect the DBA's wishes, you would create your filter by fetching the id's from the "other" tables, and use those in your where clause with "=" or "in". If you need the "in", you would lose the possibility of using bind variables, so you'd preferably stick to one item from each of the "other" tables.