[SQL] selecting from integer[]

[Kelly]

select * from pg_user where usesysid=(select grolist[1] from pg_group 
where groname='mygroup');
select * from pg_user where usesysid=(select grolist[2] from pg_group 
where groname='mygroup');
select * from pg_user where usesysid=(select grolist[3] from pg_group 
where groname='mygroup');

Can those three queries be merged to one query? (and still gives me 
those three rows)
Or do I have to explicitly say grolist[1], grolist[2], etc

Thanks in advance :)




---(end of broadcast)---
TIP 6: Have you searched our list archives?

http://archives.postgresql.org

[SQL] cron job INSERT appears to bail.

[Allan Kelly]

Hi, I have mirrored our Web site to a client's intranet, using tried and tested
techniques. Every day I generate a new file containing some table drops, some
table creates and about 7000 insert statements. This is ftp'd up to our ISP,
where a cronjob uses 'psql -f' to update the Website data. Great, all is well.

The intranet mirror for the client later grabs the file from the ISP and
attempts the same 'psql -f' but appears to stop after a few inserts. Running the
'psql -f' from the command line works just fine.

So.. what is it about the cron job that is limiting? I know this is probably a
systems issue, rather than a PostgreSQL issue per se, but I am completely
stumped. And this is all a little embarassing!

Linux Mandrake 7.0, Intel platform, postgres 6.5.2

-- 

 # Allan Kellyhttp://www.plotsearch.co.uk
 # (+44) (0)1506 417130 x 229
 # [EMAIL PROTECTED] ..
 # /Software Engineer/i. ... .
 # --   *  . . .. .
 # "If you are a Visual Basic programmer,   *   . . .
 #  these details are none of your business."*   .  . .
 # Mr Bunny's Guide to Active X, by Carlton Egremont III  * . .
 # --  vi: set noet tw=80 sts=4 ts=8  : .

Re: [SQL] Slow self-join on a 100 million record table

[Andrew J. Kelly]

Mark,

I doubt very much you will ever get much faster results (without increasing
hardware) in a situation such as that.  Your queries don't look selective
enough to effectively use the indexes.  What is the query plan for each of
the individual selects and what does it look like as a whole?   How many
rows does each individual select return and how many for the final
statement?

-- 
Andrew J. Kelly
SQL Server MVP


"Mark Davies" <[EMAIL PROTECTED]> wrote in message
[EMAIL PROTECTED]">news:[EMAIL PROTECTED]...
> I have a database containing 100 million records, in which each record
> contains (in sequence) all of the words in a 100 million word
> collection of texts.  There are two columns: TheID (offset value) and
> TheWord (sequential words), e.g.:
>
> TheID  TheWord
>   -
>
> 1  I
> 2  saw
> 3  the
> 4  man
> 5  that
> 6  came
>  . . .
> 1 xxx
>
> To extract strings, I then use self-joins on this one table, in which
> [ID], [ID-1], [ID+1] etc are used to find preceding and following
> words, e.g.:
>
> select count(*),w1.w1,w2.w1,w3.w1 from
> ((select w1, ID+1 as ID from seq where w1 in ('the','that','this')) w1
> inner join
> (select w1, ID as ID from seq where w1 in ('man','woman','person')) w2
> on w2.ID = w1.ID)
> inner join
> (select w1, ID-1 as ID from seq where w1 in ('who','that','which')) w3
> on w3.ID=w1.ID
> group by w1.w1,w2.w1,w3.w1
>
> This would yield results like "the man that" (words 3-5 above),"that
> woman who","this man which", etc.
>
> The problem is, the self-join solution is extremely slow.  I have a
> SQL Server 7.0 database with a clustered index on TheWord (sequential
> words) and a normal index on TheID.  Even with all of this, however, a
> self-join query like the one just listed takes about 15 seconds on my
> machine (dual CPU 1.2GHz, 4GB RAM, three 10K rpm SCSI HD w/ RAID-0).
>
> Any suggestions?  Have I messed up in terms of the SQL statement?
> Thanks in advance for any help that you can give.
>
> Mark Davies
> Illinois State University
>
> P.S. Yes, I know about Full-Text Indexing in SQL Server, but it's not
> adequate for my purposes -- there's a lot more to the project than
> what I've described here.



---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/users-lounge/docs/faq.html