On Mon, Mar 18, 2002 at 07:32:36PM -0500, Sam Varshavchik wrote:
> Adam Sherman writes:
> > On 03/18/2002 04:17:28 PM -0600, Bruce Cannon wrote:
> > > Preliminary suggestion is to create a view within the database.
> > > This will likely work, but will effectively double the size of
> > > my table. We have plenty of server (dual PIII/866 with all the
> > > trimmings) but if there's another way I'd sure like to find it.
> > It will not increase the size of the able, as there is no data
> > duplication. You are effectively translating the incoming query
> > into the proper select statement on the fly.
> I'd be very suprised is pg's query optimizer will be able to
> optimized a select view in this case to anything other than a table
> scan, as opposed to an index lookup. This is not a practical
> solution.
Fortunately, pg has "EXPLAIN", so we can see what it's doing:
courier=> \d passwd
View "passwd"
Column | Type | Modifiers
---------+-----------------------+-----------
id | character varying(16) |
crypt | character varying(16) |
uid | integer |
gid | integer |
home | character varying |
maildir | character varying |
quota | character varying(32) |
View definition: SELECT users.login AS id, users.passwd AS crypt, 402
AS uid, 402 AS gid, ('/usr/vmail/'::"varchar" || users.login) AS home,
('/usr/vmail/'::"varchar" || users.login) AS maildir, users.quota FROM
users;
courier=> explain select * from passwd where id='foo';
NOTICE: QUERY PLAN:
Index Scan using users_pkey on users (cost=0.00..5.01 rows=1 width=46)
At least in my case, it's claiming to be doing an index scan. That's
exactly the same explanation as when selecting directly from the users
table. So, yay PostgreSQL!
Cheers,
Tom
--
-- Tom Rathborne [EMAIL PROTECTED] http://www.aceldama.com/~tomr/
Most people can't understand how others can
blow their noses differently than they do. -- Turgenev
_______________________________________________
courier-users mailing list
[EMAIL PROTECTED]
Unsubscribe: https://lists.sourceforge.net/lists/listinfo/courier-users
