Skip site navigation (1) Skip section navigation (2)

Re: Slow tab completion w/ lots of tables

From: Stephen Frost <sfrost(at)snowman(dot)net>
To: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
Cc: Robert Haas <robertmhaas(at)gmail(dot)com>, pgsql-hackers(at)postgresql(dot)org
Subject: Re: Slow tab completion w/ lots of tables
Date: 2012-08-21 17:48:48
Message-ID: 20120821174847.GL1267@tamriel.snowman.net (view raw or flat)
Thread:
Lists: pgsql-hackers
* Tom Lane (tgl(at)sss(dot)pgh(dot)pa(dot)us) wrote:
> The LIKE idea is interesting.  What you'd still need is to suppress the
> quote_ident function call so that it becomes just "relname LIKE 'foo%'".
> Which seems do-able if possibly rather ugly.  That would leave us with
> SELECT ... FROM foo<TAB> being fast but SELECT ... FROM "foo<TAB> being
> slow; not sure if that's good enough.  I do *not* want to add an index
> on quote_ident(relname) to pg_class though.  Quite aside from speed
> issues, I'm pretty sure that functional indexes on core system catalogs
> would be a huge can of worms.

That's the kind of concern that I was expecting, to be honest. :)  As
Kevin's pointed out, it's not likely to be needed anyway..  There's a
bit of an open question still regarding case-insensitive searching, but
perhaps we let that be slow and only done if we don't get any answers
back from a case-sensetive search?

For example, given these tables:

TEST
test

Doing:

select * from TE<tab>  -> "TEST"
select * from "TE<tab>  -> "TEST"
select * from te<tab>  -> test
select * from "te<tab>  -> test
select * from Te<tab>  -> test (but slow)
select * from "Te<tab>  -> test (but slow)

We'd essentially do: LIKE 'xx%', and then run quote_ident() on the
result (I assume we can replace the whole word, right?).  I'd also
strip off any ", for the purposes of searching with tab-completion.  I'm
not sure how easy it'd be to have a fall-back setup.  I do wonder if we
should do what I often recommend my dev do though, which is to have a
SQL or pl/pgsql function defined on the database-side, rather than
sending large/complex queries to the database from the application..

	Thanks,

		Stephen

In response to

Responses

pgsql-hackers by date

Next:From: Amit kapilaDate: 2012-08-21 17:51:16
Subject: Re: 9.2RC1 wraps this Thursday ...
Previous:From: Tom LaneDate: 2012-08-21 17:37:16
Subject: Re: Slow tab completion w/ lots of tables

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group