Re: Scalability with large numbers of tables

"Greg Sabino Mullane" <greg(at)turnstep(dot)com> writes:
> Christopher Browne replied:
>> If you've got tens of thousands of relations, the tab completion code
>> has to draw the whole list of relations from pg_class into memory and
>> "marshal" it into a form usable by GNU Readline.

> Well, it's actually not quite that bad. The tab-completion code has a
> hard-coded limit (literally) of 1000 relations in the SQL it sends to
> the backend, so over 1000 any slowdown is simply a limitation on how fast
> Postgres can execute the query and serve it up to psql.

Actually it is that bad :-( because the query that is generated is

SELECT bad-nasty-select
UNION
SELECT another-nasty-select
UNION
SELECT still-another-one
LIMIT 1000

and since UNION eliminates duplicates, the backend has to do the whole
thing before the LIMIT kicks in.

I'm not sure if we could use UNION ALL --- does readline care if there
are duplicates? --- but if not it'd help to put LIMITs on the individual
UNION arms.

(SELECT nasty-select LIMIT 1000)
UNION
(SELECT nasty-select LIMIT 1000)
UNION
(SELECT nasty-select LIMIT 1000)
LIMIT 1000

The individual selects themselves aren't exactly optimized, either ---
for instance the WHERE clauses aren't designed to allow indexscans
to be used. Maybe we could use LIKEs instead of those ugly substring
tests.

In short, this is mostly the fault of the tab completion code and not
the backend.

regards, tom lane