Search Postgresql Archives

Re: Scalability with large numbers of tables

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



"Greg Sabino Mullane" <greg@xxxxxxxxxxxx> 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

---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

               http://archives.postgresql.org

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux