
We've almost got UdmSearch up and running, and I'm noticing something odd:

-rw-------  1 pgsql  pgsql   204800 Jan  6 00:05 url
-rw-------  1 pgsql  pgsql  1622016 Jan  6 00:05 word_url

url is:

CREATE TABLE "url" (
        "rec_id" int4 DEFAULT nextval('next_url_id') PRIMARY KEY,
        "status" int4 NOT NULL DEFAULT 0,
        "url" character varying(128) NOT NULL,
        "content_type" character varying(32) NOT NULL DEFAULT '',
        "last_modified" character varying(32) NOT NULL DEFAULT '',
        "title" character varying(128) NOT NULL DEFAULT '',
        "text" character varying(255) NOT NULL DEFAULT '',
        "size" int4 NOT NULL DEFAULT 0,
        "indexed" int4 NOT NULL DEFAULT 0,
        "last_index_time" datetime NOT NULL DEFAULT 'Thu Dec 31 20:00:00 1970 GMT',
        "next_index_time" datetime NOT NULL DEFAULT 'Thu Dec 31 20:00:00 1970 GMT',
        "referrer" int4 NOT NULL DEFAULT 0,
        "tag" int4 NOT NULL DEFAULT 0,
        "hops" int4 NOT NULL DEFAULT 0,
        "keywords" character varying(255) NOT NULL DEFAULT '',
        "description" character varying(100) NOT NULL DEFAULT '',
        "crc" character varying(33) NOT NULL DEFAULT '');

and word_url is:

CREATE  INDEX "word_url" on "dict" using btree ( "word" "varchar_ops", "url_id" "int4_ops" );

=============

is it just me, or does an index ~6x the size of the data itself look
"odd"?

Its an older v6.5.0 database (haven't had time to upgrade *sigh*), so if
explains it, so be it...I'll do an upgrade ASAP...but if that doesn't?

Thanks...

Marc G. Fournier                   ICQ#7615664               IRC Nick: Scrappy
Systems Administrator @ hub.org 
primary: scrappy@hub.org           secondary: scrappy@{freebsd|postgresql}.org 


Thu, 6 Jan 2000 01:08:44 -0400 (AST)
The Hermit Hacker <scrappy@hub.org>
UdmSearch: tables vs indices ...
The Hermit Hacker wrote:
> 
> We've almost got UdmSearch up and running, and I'm noticing something odd:
> 
> -rw-------  1 pgsql  pgsql   204800 Jan  6 00:05 url
> -rw-------  1 pgsql  pgsql  1622016 Jan  6 00:05 word_url
> 
> url is:
> 
> CREATE TABLE "url" (
>         "rec_id" int4 DEFAULT nextval('next_url_id') PRIMARY KEY,
>         "status" int4 NOT NULL DEFAULT 0,
>         "url" character varying(128) NOT NULL,
>         "content_type" character varying(32) NOT NULL DEFAULT '',
>         "last_modified" character varying(32) NOT NULL DEFAULT '',
>         "title" character varying(128) NOT NULL DEFAULT '',
>         "text" character varying(255) NOT NULL DEFAULT '',
>         "size" int4 NOT NULL DEFAULT 0,
>         "indexed" int4 NOT NULL DEFAULT 0,
>         "last_index_time" datetime NOT NULL DEFAULT 'Thu Dec 31 20:00:00 1970 GMT',
>         "next_index_time" datetime NOT NULL DEFAULT 'Thu Dec 31 20:00:00 1970 GMT',
>         "referrer" int4 NOT NULL DEFAULT 0,
>         "tag" int4 NOT NULL DEFAULT 0,
>         "hops" int4 NOT NULL DEFAULT 0,
>         "keywords" character varying(255) NOT NULL DEFAULT '',
>         "description" character varying(100) NOT NULL DEFAULT '',
>         "crc" character varying(33) NOT NULL DEFAULT '');
> 
> and word_url is:
> 
> CREATE  INDEX "word_url" on "dict" using btree ( "word" "varchar_ops", "url_id" "int4_ops" );
> 
> =============
> 
> is it just me, or does an index ~6x the size of the data itself look
> "odd"?
> 
> Its an older v6.5.0 database (haven't had time to upgrade *sigh*), so if
> explains it, so be it...I'll do an upgrade ASAP...but if that doesn't?
> 
> Thanks...


According to your CREATE INDEX statement, word_url is on the
table dict, not url. Is dict a large dictionary of some sort?

Mike

Thu, 06 Jan 2000 00:23:32 -0500
Mike Mascari <mascarm@mascari.com>
Re: [HACKERS] UdmSearch: tables vs indices ...
On Thu, 6 Jan 2000, Mike Mascari wrote:

> According to your CREATE INDEX statement, word_url is on the
> table dict, not url. Is dict a large dictionary of some sort?

Damn...ya, thanks for pointing what should have been obvious :(  dict is
~10Meg and growing, word_url is now 7meg *sigh*

okay...ignore that one :(



Thu, 6 Jan 2000 01:31:27 -0400 (AST)
The Hermit Hacker <scrappy@hub.org>
Re: [HACKERS] UdmSearch: tables vs indices ...
On Thu, 6 Jan 2000, The Hermit Hacker wrote:

> 
> We've almost got UdmSearch up and running, and I'm noticing something odd:
> 
> -rw-------  1 pgsql  pgsql   204800 Jan  6 00:05 url
> -rw-------  1 pgsql  pgsql  1622016 Jan  6 00:05 word_url

Here's what I have on the test system I'm working with.  The apache
docs are the only thing in it (or that should be in it).

-rw-------   1 postgres  postgres  1671168 Dec 15 08:35 url
-rw-------   1 postgres  postgres   278528 Dec 15 08:35 url_crc
-rw-------   1 postgres  postgres   106496 Dec 15 08:35 url_pkey
-rw-------   1 postgres  postgres   335872 Dec 15 08:35 url_url
-rw-------   1 postgres  postgres  1179648 Dec 15 08:35 word_url

Vince.
-- 
==========================================================================
Vince Vielhaber -- KA8CSH    email: vev@michvhf.com    http://www.pop4.net
   128K ISDN: $24.95/mo or less - 56K Dialup: $17.95/mo or less at Pop4
        Online Campground Directory    http://www.camping-usa.com
       Online Giftshop Superstore    http://www.cloudninegifts.com
==========================================================================




Thu, 6 Jan 2000 06:06:01 -0500 (EST)
Vince Vielhaber <vev@michvhf.com>
Re: [HACKERS] UdmSearch: tables vs indices ...
On Thu, 6 Jan 2000, Vince Vielhaber wrote:

> On Thu, 6 Jan 2000, The Hermit Hacker wrote:
> 
> > 
> > We've almost got UdmSearch up and running, and I'm noticing something odd:
> > 
> > -rw-------  1 pgsql  pgsql   204800 Jan  6 00:05 url
> > -rw-------  1 pgsql  pgsql  1622016 Jan  6 00:05 word_url
> 
> Here's what I have on the test system I'm working with.  The apache
> docs are the only thing in it (or that should be in it).
> 
> -rw-------   1 postgres  postgres  1671168 Dec 15 08:35 url
> -rw-------   1 postgres  postgres   278528 Dec 15 08:35 url_crc
> -rw-------   1 postgres  postgres   106496 Dec 15 08:35 url_pkey
> -rw-------   1 postgres  postgres   335872 Dec 15 08:35 url_url
> -rw-------   1 postgres  postgres  1179648 Dec 15 08:35 word_url

Here is *just* http://www.postgresql.org/docs:

-rw-------  1 pgsql  pgsql   3039232 Jan  6 06:02 url
-rw-------  1 pgsql  pgsql  35602432 Jan  6 06:02 dict
-rw-------  1 pgsql  pgsql    303104 Jan  6 06:02 url_pkey
-rw-------  1 pgsql  pgsql    376832 Jan  6 06:02 url_crc
-rw-------  1 pgsql  pgsql   1294336 Jan  6 06:02 url_url
-rw-------  1 pgsql  pgsql  27385856 Jan  6 06:02 word_url
-rw-------  1 pgsql  pgsql      8192 Jan  6 06:01 next_url_id

They are generating what I think is a very very weird looking query on the
tables that appears to be just hanging the whole thing...can someone
explain to me what *this* would do:

		sum(case dict.word when '$t' then 1 else 0 end)

I'm trying to get more details out of Alexander, since I'm guessing that
the query itself could possibly be done cleaner, but they acknowledge that
their PostgreSQL knowledge tends to be rather "sparse", at best :)

More as it becomes available ...

Marc G. Fournier                   ICQ#7615664               IRC Nick: Scrappy
Systems Administrator @ hub.org 
primary: scrappy@hub.org           secondary: scrappy@{freebsd|postgresql}.org 


Thu, 6 Jan 2000 09:15:11 -0400 (AST)
The Hermit Hacker <scrappy@hub.org>
Re: [HACKERS] UdmSearch: tables vs indices ...
The Hermit Hacker <scrappy@hub.org> writes:
> explain to me what *this* would do:

> 		sum(case dict.word when '$t' then 1 else 0 end)

Looks to me like it generates the same result as

	select count(*) where dict.word = '$t';

			regards, tom lane

Thu, 06 Jan 2000 10:52:32 -0500
Tom Lane <tgl@sss.pgh.pa.us>
Re: [HACKERS] UdmSearch: tables vs indices ... 