I have been thinking about how to simulate an outer join. It seems the
best way is to do:
SELECT tab1.col1, tab2.col3
FROM tab1, tab2
WHERE tab1.col1 = tab2.col2
UNION ALL
SELECT tab1.col1, NULL
FROM tab1
WHERE tab1.col1 NOT IN (SELECT tab2
> Anyone know if read performance on a postgres database decreases at
> an increasing rate, as the number of stored records increase?
>
> It seems as if I'm missing something fundamental... maybe I am... is
> some kind of database cleanup necessary? With less than ten
> records, the grid
Have/do you perform reasonably regular vacuum's of the database?
Do you make use of indices to increase SELECT/UPDATE performance?
Have you checked out your queries using psql+EXPLAIN, to see that said
indices are being used?
What operating system are you using? hardware?
How are you s
By asking about missing something fundamental, you have invited
less-than-expert feedback (i.e. feedback from me).
'adding a record doubles the retrieval time' makes it sound as though
somewhere in your query to populate the grid control you are requiring a
combinatorial operation (that is, "comp
I forgot the punch line:
If you are requiring some sort of combinatorial operation, you might
consider restructuring your query or doing some of the query's work
programmatically.
David Boerwinkle
-Original Message-
From: [EMAIL PROTECTED] <[EMAIL PROTECTED]>
To: Robert Wagner <[EMAIL PR
On Wed, Jan 12, 2000 at 12:23:23PM -0400, The Hermit Hacker wrote:
> Have/do you perform reasonably regular vacuum's of the database?
on my databases, i have only been successful in doing a "VACUUM VERBOSE" on
my tables.
i suspect i've got the syntax wrong or something:
nagoss=> \h vacuum
Com
I have unfortunately deleted a message to pgsql-general today which
contained a query like:
SELECT tab1.b, tab2.c FROM tab1, tab2 WHERE tab1.a=tab2.a;
There was also a UNION following, but my memory fails me. My question is
though, can an index be used for the above query? When I try it with an
Bruce Momjian wrote:
>
> I have been thinking about how to simulate an outer join. It seems the
> best way is to do:
>
> SELECT tab1.col1, tab2.col3
> FROM tab1, tab2
> WHERE tab1.col1 = tab2.col2
> UNION ALL
> SELECT tab1.col1, NULL
> FROM tab1
>
On Wed, 12 Jan 2000, Karl DeBisschop wrote:
>
> > Anyone know if read performance on a postgres database decreases at
> > an increasing rate, as the number of stored records increase?
> >
> > It seems as if I'm missing something fundamental... maybe I am... is
> > some kind of database cle
On Wed, 12 Jan 2000, Jim Mercer wrote:
> On Wed, Jan 12, 2000 at 12:23:23PM -0400, The Hermit Hacker wrote:
> > Have/do you perform reasonably regular vacuum's of the database?
>
> on my databases, i have only been successful in doing a "VACUUM VERBOSE" on
> my tables.
>
> i suspect i've got
I am in the process of creating a large relational database. One of the key
things I need to include in this database is a system to maintain data
integrity across multiple tables.
here's an example:
Table: items
item_id description vendor_id model
cost
stuff1
Hi Jim,
> "J" == Jim Mercer <[EMAIL PROTECTED]> writes:
J> i suspect i've got the syntax wrong or something:
Good suspicion.
J> nagoss=> \h vacuum
J> Command: vacuum
J> Description: vacuum the database, i.e. cleans out deleted records, updates
statistics
J> Syntax:
J> VACUUM [VERBO
Can somebody comment on using EXISTS vs. IN in a subselect? I have
some statements with subselects, and I'd like to understand the
ramifications of choosing EXISTS or IN.
Sarah Officer
[EMAIL PROTECTED]
Mike Mascari wrote:
>
> Bruce Momjian wrote:
> >
> > I have been thinking about how to simu
On Wed, Jan 12, 2000 at 01:09:08PM -0500, [EMAIL PROTECTED] wrote:
> J> nagoss=> \h vacuum
> J> Command: vacuum
> J> Description: vacuum the database, i.e. cleans out deleted records, updates
>statistics
> J> Syntax:
> J> VACUUM [VERBOSE] [ANALYZE] [table]
> J> or
> J> VAC
It seems to me that in this case Bruce would be better off to use a default
value and NOT "simulate" an outer join.
I suggest the following:
Instead of using a character abbreviation for the relation, use a number.
Since the list of categories is most likely going to remain small, you can
use an
I was originally trying to avoid this, but I think you make a good point.
The default value is probably best for this case. Thanks for the solid
argument.
- Original Message -
From: "Culberson, Philip" <[EMAIL PROTECTED]>
> It seems to me that in this case Bruce would be better off to us
> Bruce Momjian wrote:
> >
> > I have been thinking about how to simulate an outer join. It seems the
> > best way is to do:
> >
> > SELECT tab1.col1, tab2.col3
> > FROM tab1, tab2
> > WHERE tab1.col1 = tab2.col2
> > UNION ALL
> > SELECT tab1.col1, NULL
>
> Can somebody comment on using EXISTS vs. IN in a subselect? I have
> some statements with subselects, and I'd like to understand the
> ramifications of choosing EXISTS or IN.
We have some brain-damaged code that is faster with EXISTS than IN.
With IN, the subquery is evaluated and the result
How can I update on select?
>From the User's Guide, it seems triggers can only be used on INSERT,
UPDATE and DELTE events. As for rules, the guide says they can be used on
SELECT. When I actually tried updating on select using rules, here's what
I got:
test=> CREATE RULE tab_rule AS ON select
te
I'd like to take a set of points and link them into a path. But I can't see a
single operator/function that creates a path from points! ;-(
It seems like a fairly fundamental operation, unlike some of Postgres's
delightfully rich set of geometric datatypes, operators and functions. It
doesn't lo
> Bruce Momjian wrote:
> >
> > I have been thinking about how to simulate an outer join. It seems the
> > best way is to do:
> >
> > SELECT tab1.col1, tab2.col3
> > FROM tab1, tab2
> > WHERE tab1.col1 = tab2.col2
> > UNION ALL
> > SELECT tab1.col1, NULL
> >
FAQ items 3.10 and 4.9 might give you a running start.
On 2000-01-12, Robert Wagner mentioned:
> Hello All,
>
> Anyone know if read performance on a postgres database decreases at an
> increasing rate, as the number of stored records increase?
>
> This is a TCL app, which makes entries into a
Hi,
I'm porting a database from Oracle, and I'm having difficulty
working out the syntax & logic for porting the triggers.
Here's an example of what I have in Oracle:
create table Images (
id varchar(100) PRIMARY KEY,
title varchar(25)NOT NULL,
filepath va
> > UNION ALL
> > SELECT table1.key, NULL
> > FROM table1 WHERE NOT EXISTS
> > (SELECT table2.key FROM table2 WHERE table1.key = table2.key);
>
> FWIW, that's exactly Joe Celko's SQL-89 workaround for OUTER JOINs in 'SQL for
> Smarties'. Well in fact he uses (SELECT * FROM table2 WHERE table1.key
Sarah Officer wrote:
> Can anyone set me straight here? An example of a trigger which
> calls a sql procedure would be much appreciated! I'd like the
> function to be able to access the rows which are being removed.
How about examples of a trigger that calls a *PL/pgSQL* procedure that
has acc
Oh, and one other thing...
The example has a typo. In the function, 'temp' and 'cust' should be the
same variable (doesn't matter what it's called).
Cheers,
Ed Loehr
Ed Loehr wrote:
> Sarah Officer wrote:
>
> > Can anyone set me straight here? An example of a trigger which
> > calls a sql pr
On Tue, 11 Jan 2000, Bruce Bantos wrote:
> In my current Oracle DB, I have a number of "lookup" tables
> that contain something like this:
You make a "lookup" function, and you call the
function in your select list.
It's been a few months since I've played with
PostgreSQL, so I don't remem
27 matches
Mail list logo
