I have a scenario where there are 40000 tables in the database. but there are no huge unions. Very simple single table select queries are performing much slower than usual (table with just 600,000 rows and 10-15 columns).
Does the (large) number of tables in a database can affect query performance? If Yes, How?
If anything, the large number of tables would affect planning time.
EXPLAIN (ANALYZE, BUFFERS) your query to see the planning time and figure out where the time is spent.
If these tables are not being actively queried then no; they won't have much effect. Yes, you'll have more metadata about them for the database to get through in query planning, but 40,000 isn't unheard of. The data itself will just sit out there, on disk, taking up space but not doing much else.
However ...
If you have 40,000 processes, each reading its "own" table, dragging the relevant data pages into the Buffer Cache before that can be used, then yes; there could be a huge amount of contention for the Cache space, which will have a sizeable impact.
This is one reason why the "Table-per-*" model is a Bad Idea - you don't say whether you're using this, but it's remarkably easy to get to these sorts of numbers when you do. :-)
All that said: start with the basics. Look at the explain plan for the slow queries and see if there's anything obvious there - Table Scans, File Sorts, that kind of thing.
