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

Re: [HACKERS] slow information schema with thausand users, seq.scan

From: Bruce Momjian <pgman(at)candle(dot)pha(dot)pa(dot)us>
To: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
Cc: andrew(at)supernews(dot)com, Peter Eisentraut <peter_e(at)gmx(dot)net>, PostgreSQL-patches <pgsql-patches(at)postgresql(dot)org>
Subject: Re: [HACKERS] slow information schema with thausand users, seq.scan
Date: 2006-02-12 19:32:12
Message-ID: 200602121932.k1CJWCI20835@candle.pha.pa.us (view raw or flat)
Thread:
Lists: pgsql-hackerspgsql-patches
I have remove the LEFT part of the join to pg_roles in
psql/description.c.  I assume this is too risky for 8.1.X.

---------------------------------------------------------------------------

Tom Lane wrote:
> Andrew - Supernews <andrew+nonews(at)supernews(dot)com> writes:
> > On 2006-02-06, Peter Eisentraut <peter_e(at)gmx(dot)net> wrote:
> >> It already has indexes.
> 
> > True, but they're not being used where you'd expect. This seems to be
> > something to do with the fact that it's not pg_authid which is being
> > accessed, but rather the view pg_roles.
> 
> I looked into this and it seems the problem is that the view doesn't
> get flattened into the main query because of the has_nullable_targetlist
> limitation in prepjointree.c.  That's triggered because pg_roles has
> 	'********'::text AS rolpassword
> which isn't nullable, meaning it would produce wrong behavior if
> referenced above the outer join.
> 
> Ultimately, the reason this is a problem is that the planner deals only
> in simple Vars while processing joins; it doesn't want to think about
> expressions.  I'm starting to think that it may be time to fix this,
> because I've run into several related restrictions lately, but it seems
> like a nontrivial project.
> 
> In the meantime, reducing the LEFT JOIN to pg_roles to a JOIN as per
> Peter's suggestion seems like the best short-term workaround.
> 
> 			regards, tom lane
> 
> ---------------------------(end of broadcast)---------------------------
> TIP 4: Have you searched our list archives?
> 
>                http://archives.postgresql.org
> 

-- 
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman(at)candle(dot)pha(dot)pa(dot)us               |  (610) 359-1001
  +  If your life is a hard drive,     |  13 Roberts Road
  +  Christ can be your backup.        |  Newtown Square, Pennsylvania 19073

In response to

Responses

pgsql-hackers by date

Next:From: Bruce MomjianDate: 2006-02-12 20:05:09
Subject: Re: Krb5 & multiple DB connections
Previous:From: Bruce MomjianDate: 2006-02-12 19:19:45
Subject: Re: streamlined standby procedure

pgsql-patches by date

Next:From: Bruce MomjianDate: 2006-02-12 19:51:22
Subject: Re: [GENERAL] Number format problem
Previous:From: Bruce MomjianDate: 2006-02-12 19:24:13
Subject: Re: Patch to readme

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