Re: Improving the performance of psql tab completion

Hackers,

I have a database with 94059 entries in pg_class. Things are mostly
working fine but psql tab completion is frustratingly slow (around 2.5
seconds on this box). I poked around in psql a bit and saw that the
main culprit was the table visibility condition check. Here's a
typical query (there are other portions unioned in that are not
relevant to performance):

SELECT pg_catalog.quote_ident(c.relname) FROM pg_catalog.pg_class c
WHERE c.relkind IN ('r', 'S', 'v', 'f')
AND substring(pg_catalog.quote_ident(c.relname),1,7)='pg_stat'
AND pg_catalog.pg_table_is_visible(c.oid)
AND c.relnamespace <> (SELECT oid FROM pg_catalog.pg_namespace WHERE
nspname = 'pg_catalog')

By swapping out
AND pg_catalog.pg_table_is_visible(c.oid)

with
AND c.relnamespace in(select oid from pg_namespace where nspname in
(select unnest(current_schemas(true))))

the response time of the tab completion query got knocked down to a
breezy 88ms. Now, this is a bit crude compared to what
RelationIsVisible is doing. In particular, besides checking the schema
path it's doing this:
/*
* If it is in the path, it might still not be
visible; it could be
* hidden by another relation of the same name earlier
in the path. So
* we must do a slow check for conflicting relations.
*/

...but isn't that overkill for tab completion? The simple query above
seems to exhibit the same behavior (for psql) but am I missing
something?

merlin

Merlin Moncure <mmoncure(at)gmail(dot)com> writes:
> ...but isn't pg_table_is_visible overkill for tab completion?

How much does this help?

update pg_proc set procost = 10 where proname = 'pg_table_is_visible';

regards, tom lane

On Wed, Oct 10, 2012 at 8:45 AM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> Merlin Moncure <mmoncure(at)gmail(dot)com> writes:
>> ...but isn't pg_table_is_visible overkill for tab completion?
>
> How much does this help?
>
> update pg_proc set procost = 10 where proname = 'pg_table_is_visible';

hm, it fixes the problem. Also, at least for 9.2, the procost is
still set at one (just looked). Well, thanks!

merlin

2012/10/10 Merlin Moncure <mmoncure(at)gmail(dot)com>:
> On Wed, Oct 10, 2012 at 8:45 AM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
>> Merlin Moncure <mmoncure(at)gmail(dot)com> writes:
>>> ...but isn't pg_table_is_visible overkill for tab completion?
>>
>> How much does this help?
>>
>> update pg_proc set procost = 10 where proname = 'pg_table_is_visible';
>
> hm, it fixes the problem. Also, at least for 9.2, the procost is
> still set at one (just looked). Well, thanks!

can we increase this value in 9.3. I though so default 10 is from 9.0,
but it is 1 still.

Regards

Pavel

>
> merlin
>
>
> --
> Sent via pgsql-hackers mailing list (pgsql-hackers(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-hackers

Merlin Moncure <mmoncure(at)gmail(dot)com> writes:
> On Wed, Oct 10, 2012 at 8:45 AM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
>> How much does this help?
>>
>> update pg_proc set procost = 10 where proname = 'pg_table_is_visible';

> hm, it fixes the problem. Also, at least for 9.2, the procost is
> still set at one (just looked). Well, thanks!

Yeah, I'm not sure why this got dropped on the floor last time it was
discussed, but I'm pretty sure we had consensus to ratchet up the costs
of all the foo_is_visible functions. The problem (at least when I try
your query here) is that the planner doesn't know enough to run the
is_visible test last among the filter conditions.

There was also some discussion of fixing the name-check to be indexable,
which the substring hack isn't. That would take a bit of work though.

Anyway, the procost change is trivial and would remain helpful even with
the other fix, so I'll go make that change in HEAD later today.

regards, tom lane

* Tom Lane (tgl(at)sss(dot)pgh(dot)pa(dot)us) wrote:
> There was also some discussion of fixing the name-check to be indexable,
> which the substring hack isn't. That would take a bit of work though.

Right. I still want to do it, but it still needs a few more "to-its",
as it were.

Thanks,

Stephen

On Fri, Oct 12, 2012 at 03:57:15PM -0400, Stephen Frost wrote:
> * Tom Lane (tgl(at)sss(dot)pgh(dot)pa(dot)us) wrote:
> > There was also some discussion of fixing the name-check to be indexable,
> > which the substring hack isn't. That would take a bit of work though.
>
> Right. I still want to do it, but it still needs a few more "to-its",
> as it were.

TODO item?

--
Bruce Momjian <bruce(at)momjian(dot)us> http://momjian.us
EnterpriseDB http://enterprisedb.com

+ It's impossible for everything to be true. +

* Bruce Momjian (bruce(at)momjian(dot)us) wrote:
> On Fri, Oct 12, 2012 at 03:57:15PM -0400, Stephen Frost wrote:
> > * Tom Lane (tgl(at)sss(dot)pgh(dot)pa(dot)us) wrote:
> > > There was also some discussion of fixing the name-check to be indexable,
> > > which the substring hack isn't. That would take a bit of work though.
> >
> > Right. I still want to do it, but it still needs a few more "to-its",
> > as it were.
>
> TODO item?

Yes, but it should link to the previous thread which included info about
what the right approach would be..

eg: http://archives.postgresql.org/pgsql-hackers/2012-08/msg00654.php

Or the top of that thread.

Thanks,

Stephen

On Fri, Oct 12, 2012 at 04:42:46PM -0400, Stephen Frost wrote:
> * Bruce Momjian (bruce(at)momjian(dot)us) wrote:
> > On Fri, Oct 12, 2012 at 03:57:15PM -0400, Stephen Frost wrote:
> > > * Tom Lane (tgl(at)sss(dot)pgh(dot)pa(dot)us) wrote:
> > > > There was also some discussion of fixing the name-check to be indexable,
> > > > which the substring hack isn't. That would take a bit of work though.
> > >
> > > Right. I still want to do it, but it still needs a few more "to-its",
> > > as it were.
> >
> > TODO item?
>
> Yes, but it should link to the previous thread which included info about
> what the right approach would be..
>
> eg: http://archives.postgresql.org/pgsql-hackers/2012-08/msg00654.php
>
> Or the top of that thread.

Added to TODO:

Improve speed of tab completion by using LIKE

http://www.postgresql.org/message-id/20121012060345.GA29214@toroid.org

--
Bruce Momjian <bruce(at)momjian(dot)us> http://momjian.us
EnterpriseDB http://enterprisedb.com

+ It's impossible for everything to be true. +