Re: Optimizing

That should be virtually instant.

First, the usual suspects: have you run "vacuum analyze" on your table? If 
not, do it and try again.

If so, please show us your table structure, index, and query - just fire up 
psql and run "\d yourtablename" and "explain <your query>" and send it to us.

Cheers,
Steve

On Friday 28 February 2003 7:26 am, Montrone, Marc -- 7183 wrote:
> Please advise how I can improve performance of query searching for
> telephone numbers. I have a table with over a million records of phone
> numbers in a single indexed field. To find one phone number takes nearly 20
> seconds. Aside from hardware upgrades, how can I improve the performance of
> this query? Thank you in advance.
>
> ---------------------------(end of broadcast)---------------------------
> TIP 1: subscribe and unsubscribe commands go to majordomo <at> postgresql.org

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

http://archives.postgresql.org

Re: Optimizing

On Fri, 28 Feb 2003, Montrone, Marc -- 7183 wrote:

> Please advise how I can improve performance of query searching for telephone
> numbers. I have a table with over a million records of phone numbers in a
> single indexed field. To find one phone number takes nearly 20 seconds.
> Aside from hardware upgrades, how can I improve the performance of this
> query? Thank you in advance.

Please provide the following which will help us to help you:

The output of this commend : select version();

Please prefix your query with "explain analyze" and provide us with both
the query and the output of this command.

---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo <at> postgresql.org so that your
message can get through to the mailing list cleanly

Epoch extraction

Re: Executing SQL commands via triggers without the use of procedures

--- Susan Hoddinott <susan <at> perth.dialix.com.au> wrote:
> Hello,
> 
> Having scoured the relevant documentation I cannot
> find anything which indicates how I simply create a
> database trigger to insert into a second table after
> insert on a first table, without the use of a
> procedure.  As I do not want return values etc. this
> seems like overkill.  Is it possible to create a
> trigger which executes SQL directly without the need
> to create a procedure?  If so, what is the syntax?
> 

You don't need to worry about those return values;
nothing will be returned to your application from a
trigger function.  AFAICT the only difference between
PostgreSQL and other DBMSs is that (at least some)
others put the procedural logic inside the "create
trigger" statement, whereas PostgreSQL puts it in a
separate function.  You need to think about this only
at statement creation time; otherwise everything works
just the same.  One tip: always use the "create or
replace function" syntax, to avoid having your trigger
become confused if you change your function definition.

__________________________________________________
Do you Yahoo!?
Yahoo! Tax Center - forms, calculators, tips, more
http://taxes.yahoo.com/

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

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

Re: Denormalizing during select

On Tue, 2003-02-25 at 13:48, Edmund Lian wrote:
> On Tue, 25 Feb 2003 03:44:02 GMT, Edmund Lian wrote:
> 
> >I found this example in "Practical PostgreSQL"... will it do the job?
> 
> Answering my own question: kind of. The problem with custom aggregates
> is that they need to be used with a "group by" clause, and this means
> that the select cannot return columns that are not aggregates of some
> kind. What I'm trying to return are rows that are a combination of
> columns and aggregates.

I've been trying to figure out how to give a running total (similar
issue I think).

key    	value
1	5
1	5
1	5
2	1
2	2
2	1

Query output:
key	value	sum to point
1	5	5
1	5	10
1	5	15
2	1	1
2	2	3
2	1	4

I think I should be able to do it with an aggregate -- but the best I've
been able to come up with is a Set Returning Function.

--

-- 
Rod Taylor <rbt <at> rbt.ca>

PGP Key: http://www.rbt.ca/rbtpub.asc

Re: Denormalizing during select

On Tue, 2003-02-25 at 13:48, Edmund Lian wrote:
> On Tue, 25 Feb 2003 03:44:02 GMT, Edmund Lian wrote:
> 
> >I found this example in "Practical PostgreSQL"... will it do the job?
> 
> Answering my own question: kind of. The problem with custom aggregates
> is that they need to be used with a "group by" clause, and this means
> that the select cannot return columns that are not aggregates of some
> kind. What I'm trying to return are rows that are a combination of
> columns and aggregates.

I've been trying to figure out how to give a running total (similar
issue I think).

key    	value
1	5
1	5
1	5
2	1
2	2
2	1

Query output:
key	value	sum to point
1	5	5
1	5	10
1	5	15
2	1	1
2	2	3
2	1	4

I think I should be able to do it with an aggregate -- but the best I've
been able to come up with is a Set Returning Function.

--

-- 
Rod Taylor <rbt <at> rbt.ca>

PGP Key: http://www.rbt.ca/rbtpub.asc

Re: Denormalizing during select

Edmund Lian <no.spam <at> address.com> writes:
> Answering my own question: kind of. The problem with custom aggregates
> is that they need to be used with a "group by" clause, and this means
> that the select cannot return columns that are not aggregates of some
> kind. What I'm trying to return are rows that are a combination of
> columns and aggregates.

This strikes me as evidence of fuzzy thinking.  What sets of rows are
the aggregates taken over?  Which column values within those sets of
rows do you expect the non-aggregated column references to return?
If the columns aren't the ones grouped by, seems like you have an
inherently undefined result.

If you know for some reason that there will be only one unique value
of a column in a grouped row set, or you don't actually much care which
one you get, then you could use MIN() or MAX() on the column reference
to make it look like a kosher query.

			regards, tom lane

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

http://archives.postgresql.org

OUTER JOIN with filter

Hello,

Can anyone help me with the problem bellow ?

I want to have all the row in table "table_main" with the value in the table
"table_slave" (value or null if not exist)

It is possible to have the same result without sub-select in OUTER JOIN
(speed problem on big table) ?

Actualy this is what I make...

CREATE TABLE "table_main" (
   "id" int4,
   "some_field" varchar(100)
);

CREATE TABLE "table_slave" (
   "id" int4,
   "name" varchar(100),
   "c1" varchar(30)
);

SELECT TM.some_field, TS.name
FROM table_main TM
LEFT OUTER JOIN (SELECT name FROM table_slave WHERE c1 = 'myvalue') TS
USING(id)

the request bellow is not correct:

SELECT TM.some_filed, TS.name
FROM table_main TM
LEFT OUTER JOIN table_slave TS USING(id)
WHERE (TS.c1 = 'myc1' OR TS.c1 IS NULL)

Many thanks,

Nicolas

---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to majordomo <at> postgresql.org

Re: Are scalar type's in/out functions implicitly STRICT?

On Thu, Feb 27, 2003 at 09:44:37PM -0500, Tom Lane wrote:
> Denis Zaitsev <zzz <at> cd-club.ru> writes:
> > So, I create some new scalar type and I don't declare its in/out
> > functions as STRICT.  But PostgreSQL copes with them such as they
> > would be STRICT - they never get the NULL value.
> 
> This is likely true in many places --- for example, COPY has a shortcut
> because it wants to substitute \N for nulls.  It would not be a bright
> idea to assume that it's true in every place and forevermore.  In
> general, if you have a C function and don't want to be bothered with
> explicit PG_ISNULL testing, you'd better mark it STRICT.
> 

Ok, thanks.

---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
    (send "unregister YourEmailAddressHere" to majordomo <at> postgresql.org)

Re: Are scalar type's in/out functions implicitly STRICT?

On Thu, Feb 27, 2003 at 09:24:44PM -0600, Bruno Wolff III wrote:
> On Fri, Feb 28, 2003 at 05:45:44 +0500,
>   Denis Zaitsev <zzz <at> cd-club.ru> wrote:
> > So, I create some new scalar type and I don't declare its in/out
> > functions as STRICT.  But PostgreSQL copes with them such as they
> > would be STRICT - they never get the NULL value.  And this is not
> > documented, AFAIK.  Have I missed something or is it a some hard
> > well-known etc. fact?  Thanks in advance.
> 
> Are you using the version one calling sequence?

Yes, I am - v1.

> The version zero method doesn't have a way handle nulls other than by
> declaring the function strict.
> 

Hmm, examples/docs show such a way for v0...

---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster