Thread: is this expected or am i on crack?

hello all,

first off, some information:
=====
=====
the table:
             Table "facts"
  Attribute  |     Type     | Modifier
-------------+--------------+----------
 keyword     | varchar(80)  |
 description | varchar(255) |
 url         | varchar(255) |


the sql:
select keyword from facts as f1
where 1 <> (select count(*) from facts as f2 where f1.keyword = f2.keyword)
order by keyword;

my system:
Linux kernel 2.2.x
p166
32M ram
200M+ swap
=====
=====

it took about 65 minutes to complete. i know that it is doing alot of work,
but it there a way that it could be sped up, like something i could configure
or something that i could do to make it faster?

just looking to see if the time it took to take this is to be expected?
postmaster was using from 78-98% of the cpu for the whole time.

in hind site i wish i would have made keyword not null primary key...
*sigh*, learn from mistakes i guess. now have to fix the duplicates and
move all the rows into a new table with keyword as primary key...

thanks for any help,
and developers: thanks for developing a real non-toy database for the masses,

-chris humphries

On Sun, 18 Feb 2001, Chris Humphries wrote:

>              Table "facts"
>   Attribute  |     Type     | Modifier
> -------------+--------------+----------
>  keyword     | varchar(80)  |
>  description | varchar(255) |
>  url         | varchar(255) |
>
> select keyword from facts as f1
> where 1 <> (select count(*) from facts as f2 where f1.keyword = f2.keyword)
> order by keyword;
>
> it took about 65 minutes to complete. i know that it is doing alot of work,
> but it there a way that it could be sped up, like something i could configure
> or something that i could do to make it faster?

Hm, this will probably run the subquery for every row in the table.  Try:

select keyword, count(keyword) from facts
group by keyword
having 1 <> count(keyword)
order by count(keyword) desc

Is this any faster?
--
Tod McQuillin

Chris Humphries <chumphries@siliconinc.net> writes:

> the sql:
> select keyword from facts as f1
> where 1 <> (select count(*) from facts as f2 where f1.keyword = f2.keyword)
> order by keyword;

Aside from the suggestion of not using a subquery, you could try
making an index on keyword, and doing a VACUUM of the table, before
you try your query -- whatever the query ends up being.  Indexes don't
have to be unique, after all.

Chris

--
chris@mt.sri.com -----------------------------------------------------
Chris Jones                                    SRI International, Inc.
                                                           www.sri.com

i got help with this from one of the developers on irc, thanks man,
sorry i forgot your handle:

(hope this is right)
select keyword, count(*)
from facts
where having count(*) > 1;

i think that is it, i dont have to table anymore,
building a better schemea and more stuff for everything,
but that was just sql ninja-ness man,

thanks,
Chris Humphries

ps -> open magazine (openmagazine.net) had an article in it that said
      good things about postgresql btw, just in case interested...it
      is a free subscription i think. and no this isnt spam, i dont
      work for them or anything, just an observation...



On Sun, Feb 18, 2001 at 02:54:08AM -0800, Chris Humphries wrote:
> hello all,
>
> first off, some information:
> =====
> =====
> the table:
>              Table "facts"
>   Attribute  |     Type     | Modifier
> -------------+--------------+----------
>  keyword     | varchar(80)  |
>  description | varchar(255) |
>  url         | varchar(255) |
>
>
> the sql:
> select keyword from facts as f1
> where 1 <> (select count(*) from facts as f2 where f1.keyword = f2.keyword)
> order by keyword;
>
> my system:
> Linux kernel 2.2.x
> p166
> 32M ram
> 200M+ swap
> =====
> =====
>
> it took about 65 minutes to complete. i know that it is doing alot of work,
> but it there a way that it could be sped up, like something i could configure
> or something that i could do to make it faster?
>
> just looking to see if the time it took to take this is to be expected?
> postmaster was using from 78-98% of the cpu for the whole time.
>
> in hind site i wish i would have made keyword not null primary key...
> *sigh*, learn from mistakes i guess. now have to fix the duplicates and
> move all the rows into a new table with keyword as primary key...
>
> thanks for any help,
> and developers: thanks for developing a real non-toy database for the masses,
>
> -chris humphries
>
>
>
>
>
>
>
>
>
>