[SQL] How to get binary data from pgsql function

[MaksimRomanov]

Hi 
 
    I want to return miltiplie 
row data from stored procedure in binary format; I use "libpq++" 
DLL for access to PostgreSql and I want to take binary data from pointer 
returned by PgDatabase::GetData(int,int).
    I have found only one way to 
receive binary data from backend - to use  "DECLARE BINARY CURSOR" 
statement - but I don't know way to use this statement with plpgsql 
function;
   Is there any deccision?
 
Thanks.

[SQL] Group And Sort After Union

[cnliou]

Greetings!

I want to GROUP BY and ORDER BY on the result of UNION similar to the
following (wrong) SQL:

(SELECT c11 AS c1,SUM(c12) AS c2 FROM table1
UNION
SELECT c21 AS c1,SUM(c22) AS c2 FROM table2
)
GROUP BY c1
ORDER BY c2;

Please note that the following is NOT what I want because it generates
2 groups of data set:

SELECT c11 AS c1,SUM(c12) AS c2 FROM table1
GROUP BY c1
ORDER BY c2
UNION
SELECT c21 AS c1,SUM(c22) AS c2 FROM table2
GROUP BY c1
ORDER BY c2;

How do I do that? Thank you in advance!

CNLIOU

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

http://archives.postgresql.org

Re: [SQL] Returning PK of first insert for second insert use.

[Ken Corey]

On Mon, 2002-07-29 at 19:32, Peter Atkins wrote:
> I have two tables t_proj, t_task see below:
> 
> CREATE TABLE t_proj (
> proj_id SERIAL NOT NULL,
> PRIMARY KEY (proj_id),
> task_id integer(12),
> user_id integer(6),
> title varchar(35),
> description varchar(80)
> );
> 
> CREATE TABLE t_task (
> task_id SERIAL NOT NULL,
> PRIMARY KEY (task_id),
> title varchar(35),
> description varchar(80)
> );
> 
> When I insert into t_task I need to return the task_id (PK) for that insert
> to be used for the insert into the t_proj table.
> 
> I tried using RESULT_OID but I have no idea how to obtain the true PK using
> this opague id. Below is the procedure I tried to use.

Since the primary key of the first table is a SERIAL, it's really
defined as something like this:

create table t_task (
task_id int4 not null default nextval('t_task_task_id_seq'),
...

Which means that you can predict what the next value will be, store that
in a temporary var, and then insert it into both tables...

CREATE OR REPLACE FUNCTION insertTask (varchar, varchar)
RETURNS INTEGER AS '

DECLARE
-- local variables
oid1 INTEGER;
retval INTEGER;
tempvar int4;

BEGIN
select into tempvar nextval(''t_task_task_id_seq'');

INSERT INTO t_task (task_id, title, description) 
VALUES (tempvar,$1, $2);

-- Everything has passed, return id as pk
RETURN tempvar;
END;
' LANGUAGE 'plpgsql';

WARNING: this is not guaranteed to be the correct syntax, I didn't
create the tables and the function to test it, but I do this kind of
thing all the time in my functions.

-- 
Ken Corey  CTO  http://www.atomic-interactive.com  07720 440 731


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

http://archives.postgresql.org

[SQL] Random resultset retrieving -> performance bottleneck

[Cédric Dufour]

Hello to all of you,

I'm running into a performance problem when considering the following
scenario: I have a fairly large table (1mio rows) related to other smaller
tables (between 100 and 1 rows) and would like to retrieve the joined
data (through a view) in random order. In order to do so, the main table
contains a 'Random' field (which is updated on a regular basis, in order to
re-randomize the data set), on which an index is created:

*
* SCHEMA
*

CREATE TABLE tb_Small AS (
PK integer UNIQUE NOT NULL
PRIMARY KEY (PK)
);

CREATE TABLE tb_Large AS (
Random integer DEFAULT( CAST( 100*random() AS integer ) ) NOT NULL,
FK_Small integer NOT NULL,
PK integer UNIQUE NOT NULL,
FOREIGN KEY (FK_Small) REFERENCES tb_Small (PK),
PRIMARY KEY (PK)
);

CREATE INDEX ix_Large__Random ON tb_Large (Random);

CREATE TABLE tb_Foo AS (
FK_Large integer NOT NULL,
PK integer UNIQUE NOT NULL,
FOREIGN KEY (FK_Large) REFERENCES tb_Large (PK) DEFERRABLE,
PRIMARY KEY (PK)
);

CREATE VIEW vw_Large AS
SELECT
tb_Small.*,
tb_Large.*
FROM
tb_Small
INNER JOIN
tb_Large
ON ( tb_Small.PK = tb_Large.FK_Small );

NOTA BENE: My production view actually involves much more inner- or
left-joined tables that this simple example

Here are the various querying scenario and the related performance problem
(my production view actually involves much more inner- or left-joined tables
that the scenarios below, simplified for the sake of clarity)

*
* 1.
*
CREATE VIEW vw_Large AS
SELECT
*
FROM
tb_Small AS Small
INNER JOIN
tb_Large AS Large
ON ( Small.PK = Large.FK_Small );

SELECT * FROM vw_Large ORDER BY Random LIMIT 50;
-> The slowest way (~60 time units), since the entire view is evaluated
before it is sorted properly (the index on the 'Random' being ignored)

SELECT * FROM vw_Large WHERE Small.PK =  ORDER BY Random LIMIT 50;
-> Quicker (~5 time units), since the evaluated view is smaller (cf. the
WHERE clause) before it is sorted properly

*
* 2.
*
CREATE VIEW vw_Large AS
SELECT
*
FROM
tb_Small AS Small
INNER JOIN
( SELECT * FROM tb_Large ORDER BY Random ) AS Large
ON ( Small.PK = Large.FK_Small );

SELECT * FROM vw_Large LIMIT 50;
-> Much quicker (~15 time units), since the ordering is achieved on the
table itself, using the index, before the view is evaluated

SELECT * FROM vw_Large WHERE Small.PK =  LIMIT 50;
-> Slow (~15 time units), since the ordering is achieved on the entire
table, despite the WHERE clause


*
* POSSIBLE SOLUTIONS AND PROBLEMS
*
Since the second approach seems to give better results, the idea was to
reorder (cluster) the 'Large' table regurlarly - like once a day -, so as to
have a randomized data set and avoid the ORDER BY clause (this is the way I
achieved VERY GOOD performance on MS SQL Server [~1 time unit], for exactly
the same scenario). In order to do so, one might:

*
* 1.
*
CLUSTER ix_Large__Random TABLE tb_Large;

-> Would be ideal... but OIDS, CONTRAINTS AND INDEXES ARE LOST AND ALL
RELATED VIEWS/FUNCTIONS WON'T WORK ANYLONGER...

*
* 2.
*
BEGIN;
SET CONSTRAINTS ALL DEFERRED;
CREATE TEMP TABLE tmp_Large AS SELECT * FROM tb_Table;
DELETE FROM tb_Large; -- won't work; RI violation on foreign key
'tb_Foo(FK_Large)'
INSERT INTO tb_Large SELECT * FROM tb_Table ORDER BY Random;
DROP TABLE tmp_Large;
COMMIT;

-> Would preserve oids, constraints and indexes... BUT DELETE IS IMPOSSIBLE,
BECAUSE REFERENTIAL INTEGRITY IS VIOLATED ON FOREIGN KEY 'FK_Large' IN TABLE
'tb_Foo', despite the SET CONSTRAINTS ALL DEFERRED clause

*
* HELP !!!
*
Would anyone have a solution to this (general) randomization problem ?
Is there a way to turn RI off during the transaction ?
Is there another way to reorder (cluster) the table without having
oids/constraints/indexes or RI problems ?

Any clues would be very much appreciated ;-)

Cédric Dufour



---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]

[SQL] Transitive Closure

[Suhas Joshi]

Question from a Postgresql newbie. 
Does Postgresql have transitive closure feature in psql? Where can I
find some documentation and examples on it?
Thanks in advance

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

Re: [SQL] Change size of a field

[ROUWEZ Stephane]

Hi,

Thank you, it works but I loose my foreign keys and the privileges for
groups and users. 

Is there a solution ?

Stef


-Message d'origine-
De : Achilleus Mantzios [mailto:[EMAIL PROTECTED]]
Envoye : vendredi 26 juillet 2002 14:50
A : ROUWEZ Stephane
Cc : '[EMAIL PROTECTED]'; LESNE Philippe
Objet : Re: [SQL] Change size of a field


On Fri, 26 Jul 2002, ROUWEZ Stephane wrote:

> Hi,
> 
> Is it possible to change the size of a field ?
> Actually, I have a field called 'nom' in varchar(50) and I want this field
> in varchar(80). 
> I can't do it with PgAdmin. Is it normal ?

Pretty normal!
just 
1) % pg_dump -t   > -DUMP.sql
2) % psql
   # drop sequence _id_seq;
3) vi -DUMP.sql
   change your varchar(50) to varchar(80)
4) psql
   #\i -DUMP.sql

There you go

> 
> Tanks
> 
> 
> -- 
> http://www.ecolo.be 
> 
> 
> ---(end of broadcast)---
> TIP 6: Have you searched our list archives?
> 
> http://archives.postgresql.org
> 

-- 
Achilleus Mantzios
S/W Engineer
IT dept
Dynacom Tankers Mngmt
Nikis 4, Glyfada
Athens 16610
Greece
tel:+30-10-8981112
fax:+30-10-8981877
email:  [EMAIL PROTECTED]
[EMAIL PROTECTED]


-- 
http://www.ecolo.be 


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

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

Re: [SQL] Please Help me

[Jochem van Dieten]

Michelle Murrain wrote:
> 
> Yeah, except I do have experience with ColdFusion, and ColdFusion
> runs into some very problematic issues with Postgres, sadly. Although
> I use Postgres almost exclusively, I had to switch to MySQL for use
> with ColdFusion.

I wonder what your issues are, because in my experience it is very 
version related. With PostgreSQL versions before 7.1 there were various 
problems. As of 7.1, the main problem has been drivers. ColdFusion came 
with old (obsolete) drivers that didn't support many of the new features 
in postgreSQL. A custom compilation of new drivers was required, but was 
a tricky process.

As of CF MX, IMHO PostgreSQL is the clear winner. For the very simple 
reason of full Unicode support, which means internationalization is a 
breeze. Simply save templates as UTF-8 and create a database with 
Unicode encoding and you are ready. And I am not aware of any problems 
with the JDBC drivers.

Jochem

PS Whichever you choose, use cfqueryparam exclusively in your queries.


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

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

Re: [SQL] Returning PK of first insert for second insert use.

[Ken Corey]

On Mon, 2002-07-29 at 20:52, Peter Atkins wrote:
> Is there a possibility of another application accessing the DB and using the
> id before my function has completed the transaction? I'm concerned with the
> possibility of cross-over of ID's if the insert hangs.
> 
> There's no way to return the id of that insert inherently, and then use it
> for the second insert? I think SQL uses something like ADD_ID, not sure.

That's the beauty of the nextval statement. The database internally
sequences requests to it so that you're kept out of harm's way.

Say process A called the function,and nextval returns 16.  The function
now continues on its way, but is not finished when process B then calls
the function (before A is done), and nextval returns 17.

So, then function called by process A returns 16, and the function
called by process B returns 17.

That means that unless the results of process B depend in some way upon
the results of process A, there's no problem.

-Ken

-- 
Ken Corey  CTO  http://www.atomic-interactive.com  07720 440 731


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

http://archives.postgresql.org

[SQL] Returning PK of first insert for second insert use.

[Peter Atkins]

All,

I have two tables t_proj, t_task see below:

CREATE TABLE t_proj (
proj_id SERIAL NOT NULL,
PRIMARY KEY (proj_id),
task_id integer(12),
user_id integer(6),
title varchar(35),
description varchar(80)
);

CREATE TABLE t_task (
task_id SERIAL NOT NULL,
PRIMARY KEY (task_id),
title varchar(35),
description varchar(80)
);

When I insert into t_task I need to return the task_id (PK) for that insert
to be used for the insert into the t_proj table.

I tried using RESULT_OID but I have no idea how to obtain the true PK using
this opague id. Below is the procedure I tried to use.

CREATE OR REPLACE FUNCTION insertTask (varchar, varchar)
RETURNS INTEGER AS '

DECLARE
-- local variables
oid1 INTEGER;
retval INTEGER;

BEGIN
INSERT INTO t_task (title, description) VALUES ($1, $2);

-- Get the oid of the row just inserted.
GET DIAGNOSTICS oid1 = RESULT_OID;

retval := oid1;

-- Everything has passed, return id as pk
RETURN retval;
END;
' LANGUAGE 'plpgsql';


Any help would be great! 

Thanks Again,
-p

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

[SQL] How to optimize SQL query ?

[Milosz Krajewski]

How to optimize query or just force postgre to do it my way ?

Example:
  table continets (
id numeric, ..., active numeric
  );

  table countries (
id numeric, id_continent numeric, ..., active numeric
  );

  table cities (
id numeric, id_country numeric, ..., active numeric
  );

relations:
  cities.id_county are in countries.id
  countries.id_continent are on continents.id

Query:
  table temp_cities (
id_city numeric;
  );


temp_cities is temp table which holds few (~20) id of cities, to
show them.

so:
  select * from cities 
where cities.id in (select id_city from temp_cities);
or:
  select * from cities, temp_cities tmp
where cities.id = tmp.id_city;
works fine.

But the problem starts here:

  select * from cities, coutries, continets
where 
  (cities.id in (select id_city from temp_cities)) and
  (cities.id_county = countries.id) and
  (countries.id_continent = continents.id) and
  (cities.active = 1) and (coutries.active = 1) and
  (continents.active = 1)

(active means is row active or archive, many of them are active,
but I have to check it)

Posgre is planning it like this:
  joins cities with coutries
  joins countries with continents
  selects active
  filtering with cities.id (with temp_cities)

If I could force it to filter cities.id first 
(I can do this with Oracle by changing 
"select id_city from temp_cities" to 
"select id_city from temp_cities group by id_city")
it will work much (1000x) faster.

Can I force postgre do it my way ?
-- 
[ Milosz "Krashan" Krajewski ][ mail: [EMAIL PROTECTED], UIN: 1319535 ]
[ inet: Vilge, Vilgefortz][ www: http://www.ds2.pg.gda.pl/~krash ]

---(end of broadcast)---
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])

Re: [SQL] Returning PK of first insert for second insert use.

[Cédric Dufour (Cogito Ergo Soft)]

You can retrieve the last inserted sequence value using:
currval('t_task_task_id_seq')

This is connection safe, so you get the the last ID inserted by YOUR
connection.

> -Original Message-
> From: [EMAIL PROTECTED]
> [mailto:[EMAIL PROTECTED]]On Behalf Of Ken Corey
> Sent: Monday, July 29, 2002 20:59
> To: Peter Atkins
> Cc: '[EMAIL PROTECTED]'
> Subject: Re: [SQL] Returning PK of first insert for second insert use.
>
>
> On Mon, 2002-07-29 at 19:32, Peter Atkins wrote:
> > I have two tables t_proj, t_task see below:
> >
> > CREATE TABLE t_proj (
> > proj_id SERIAL NOT NULL,
> > PRIMARY KEY (proj_id),
> > task_id integer(12),
> > user_id integer(6),
> > title varchar(35),
> > description varchar(80)
> > );
> >
> > CREATE TABLE t_task (
> > task_id SERIAL NOT NULL,
> > PRIMARY KEY (task_id),
> > title varchar(35),
> > description varchar(80)
> > );
> >
> > When I insert into t_task I need to return the task_id (PK) for
> that insert
> > to be used for the insert into the t_proj table.
> >
> > I tried using RESULT_OID but I have no idea how to obtain the
> true PK using
> > this opague id. Below is the procedure I tried to use.
>
> Since the primary key of the first table is a SERIAL, it's really
> defined as something like this:
>
> create table t_task (
> task_id int4 not null default nextval('t_task_task_id_seq'),
> ...
>
> Which means that you can predict what the next value will be, store that
> in a temporary var, and then insert it into both tables...
>
> CREATE OR REPLACE FUNCTION insertTask (varchar, varchar)
> RETURNS INTEGER AS '
>
> DECLARE
> -- local variables
> oid1 INTEGER;
> retval INTEGER;
>   tempvar int4;
>
> BEGIN
>   select into tempvar nextval(''t_task_task_id_seq'');
>
> INSERT INTO t_task (task_id, title, description)
>   VALUES (tempvar,$1, $2);
>
> -- Everything has passed, return id as pk
> RETURN tempvar;
> END;
> ' LANGUAGE 'plpgsql';
>
> WARNING: this is not guaranteed to be the correct syntax, I didn't
> create the tables and the function to test it, but I do this kind of
> thing all the time in my functions.
>
> --
> Ken Corey  CTO  http://www.atomic-interactive.com  07720 440 731
>
>
> ---(end of broadcast)---
> TIP 6: Have you searched our list archives?
>
> http://archives.postgresql.org
>



---(end of broadcast)---
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to [EMAIL PROTECTED] so that your
message can get through to the mailing list cleanly

Re: [SQL] How to optimize SQL query ?

[Cédric Dufour (Cogito Ergo Soft)]

> -Original Message-
> From: [EMAIL PROTECTED]
> [mailto:[EMAIL PROTECTED]]On Behalf Of Milosz Krajewski
> Sent: Monday, July 29, 2002 16:50
> To: [EMAIL PROTECTED]
> Subject: [SQL] How to optimize SQL query ?
>
>
> How to optimize query or just force postgre to do it my way ?
>
> Example:
>   table continets (
> id numeric, ..., active numeric
>   );
>
>   table countries (
> id numeric, id_continent numeric, ..., active numeric
>   );
>
>   table cities (
> id numeric, id_country numeric, ..., active numeric
>   );
>
> relations:
>   cities.id_county are in countries.id
>   countries.id_continent are on continents.id
>
> Query:
>   table temp_cities (
> id_city numeric;
>   );
>
>
> temp_cities is temp table which holds few (~20) id of cities, to
> show them.
>
> so:
>   select * from cities
> where cities.id in (select id_city from temp_cities);

AVOID: indexes (which you should have defined on primary keys [implicitely
defined by PostgreSQL] and foreign keys [must be defined explicitely]) are
not used

> or:
>   select * from cities, temp_cities tmp
> where cities.id = tmp.id_city;

BETTER ;-)

> works fine.
>
> But the problem starts here:
>
>   select * from cities, coutries, continets
> where
>   (cities.id in (select id_city from temp_cities)) and
>   (cities.id_county = countries.id) and
>   (countries.id_continent = continents.id) and
>   (cities.active = 1) and (coutries.active = 1) and
>   (continents.active = 1)
>
> (active means is row active or archive, many of them are active,
> but I have to check it)
>
> Posgre is planning it like this:
>   joins cities with coutries
>   joins countries with continents
>   selects active
>   filtering with cities.id (with temp_cities)
>
> If I could force it to filter cities.id first
> (I can do this with Oracle by changing
> "select id_city from temp_cities" to
> "select id_city from temp_cities group by id_city")
> it will work much (1000x) faster.
>
> Can I force postgre do it my way ?

Use the explicit JOIN syntax and join each table one after another in the
order you feel is the more adequate for your query. PostgreSQL will respect
this order.

>From one I understand, you should write it this way:
SELECT
*
FROM
continents
INNER JOIN
countries
ON ( continents.id = country.id_continent )
INNER JOIN
cities
ON ( countries.id = cities.id_country )
INNER JOIN
temp_cities
ON ( cities.id = temp_cities.id )
WHERE
( continents.active = 1 )
AND ( countries.active = 1 )
AND ( cities.active = 1 )

The reason to do so are:
1. Joining first on the tables that contain the less rows contributes to
keep the cartesian product between the joins as low as possible
2. Thus if a continent - respectively country - is not active, it will be
casted out from the join immediately and thus reduce the cartesian product
for the next join(s)
3. Joining on 'temp-cities' allows the usage of the hopefully defined index

I achieved ratio from 1 to 1 respecting this strategy on a scenario
fairly closed to yours ;-)

Ce.D

> --
> [ Milosz "Krashan" Krajewski ][ mail: [EMAIL PROTECTED], UIN: 1319535 ]
> [ inet: Vilge, Vilgefortz][ www: http://www.ds2.pg.gda.pl/~krash ]
>
> ---(end of broadcast)---
> TIP 2: you can get off all lists at once with the unregister command
> (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
>



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

http://archives.postgresql.org

Re: [SQL] Group And Sort After Union

[Tom Lane]

[EMAIL PROTECTED] (cnliou) writes:
> I want to GROUP BY and ORDER BY on the result of UNION similar to the
> following (wrong) SQL:

> (SELECT c11 AS c1,SUM(c12) AS c2 FROM table1
> UNION
> SELECT c21 AS c1,SUM(c22) AS c2 FROM table2
> )
> GROUP BY c1
> ORDER BY c2;

Correct is

SELECT * FROM
(SELECT c11 AS c1,SUM(c12) AS c2 FROM table1
UNION
SELECT c21 AS c1,SUM(c22) AS c2 FROM table2
) ss
GROUP BY c1
ORDER BY c2;

regards, tom lane

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

http://archives.postgresql.org

Re: [SQL] How to optimize SQL query ?

[Tom Lane]

Milosz Krajewski <[EMAIL PROTECTED]> writes:
> Can I force postgre do it my way ?

Possibly.  See
http://www.ca.postgresql.org/users-lounge/docs/7.2/postgres/explicit-joins.html

regards, tom lane

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

Re: [SQL] How to optimize SQL query ?

[Cédric Dufour (Cogito Ergo Soft)]

You're right ! Its always a question of keeping the cartesian products of
joints as low as possible, depending on what you know of your data structure
and on the WHERE clause(s) that you know might be most used on your query...

Note that if you do not explicitely give the order of joints to PostgreSQL
(using, the 'table1, table2, ... tableN WHERE ...' syntax), PostgreSQL
should try to achieve the joints the best way as possible, based on the
tables' statistics. I had no opportunity so far to check how well it
behaves.

On ther other hand, is the optimizing algorithm clever enough to know that
even though a table might be fairly large, the quantity of rows important to
your query might be very low (depending on a WHERE clause) and that the
given table shall thus appear in the first joints rather than in the last
ones ? Anyone has experience with this ?

Happy optimizing !

> -Original Message-
> From: Milosz Krajewski [mailto:[EMAIL PROTECTED]]
> Sent: Friday, August 02, 2002 15:42
> To: Cédric Dufour (Cogito Ergo Soft)
> Subject: Re: [SQL] How to optimize SQL query ?
>
>
> Cédric Dufour (Cogito Ergo Soft) wrote:
> >
> >
> > Use the explicit JOIN syntax and join each table one after another in
> > the order you feel is the more adequate for your query. PostgreSQL
> > will respect this order.
> > From one I understand, you should write it this way:
> >
> > SELECT * FROM
> >   continents
> >   INNER JOIN countries ON ( continents.id = country.id_continent )
> >   INNER JOIN cities ON ( countries.id = cities.id_country )
> >   INNER JOIN temp_cities ON ( cities.id = temp_cities.id )
> > WHERE
> >  ( continents.active = 1 )
> >  AND ( countries.active = 1 )
> >  AND ( cities.active = 1 )
> >
> > The reason to do so are:
> > 1. Joining first on the tables that contain the less rows
> contributes > to keep the cartesian product between the joins as
> low as possible
> > 2. Thus if a continent - respectively country - is not active, it
> > will be casted out from the join immediately and thus reduce the
> > cartesian product for the next join(s)
> > 3. Joining on 'temp-cities' allows the usage of the hopefully defined
> > index
> >
> > I achieved ratio from 1 to 1 respecting this strategy on a
> > scenario fairly closed to yours ;-)
> >
> > Ce.D
>
> Thaks for your answer.
>
> I this schema temp_cities is the smallest table (ie 5 records) so
> shouldnt't it look like this ?
>
> select *
> from
>   temp_cities
>   inner join cities using (id)
>   inner join countries on (...)
>   inner join continents on (...)
> where
>   ...
> ?
>
>
> --
> [ Milosz "Krashan" Krajewski ][ mail: [EMAIL PROTECTED], UIN: 1319535 ]
> [ inet: Vilge, Vilgefortz][ www: http://www.ds2.pg.gda.pl/~krash ]
> [  2 nie jest rowne 3, nawet dla bardzo duzych wartosci 2]
>



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

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

Re: [SQL] Random resultset retrieving -> performance bottleneck

[Stephan Szabo]

On Thu, 1 Aug 2002, [iso-8859-1] Cédric Dufour wrote:

> *
> * 2.
> *
> BEGIN;
> SET CONSTRAINTS ALL DEFERRED;
> CREATE TEMP TABLE tmp_Large AS SELECT * FROM tb_Table;
> DELETE FROM tb_Large; -- won't work; RI violation on foreign key
> 'tb_Foo(FK_Large)'
> INSERT INTO tb_Large SELECT * FROM tb_Table ORDER BY Random;
> DROP TABLE tmp_Large;
> COMMIT;
>
> -> Would preserve oids, constraints and indexes... BUT DELETE IS IMPOSSIBLE,
> BECAUSE REFERENTIAL INTEGRITY IS VIOLATED ON FOREIGN KEY 'FK_Large' IN TABLE
> 'tb_Foo', despite the SET CONSTRAINTS ALL DEFERRED clause

Yeah, there's been a bug that should now be patched for upcoming 7.3
that caused this to fail.  I believe you should be able to find the
patch if you search -patches since it was pretty recent.  It might take a
little work to patch to a previous version, but it shouldn't be too hard.

Failing that, you can turn off all triggers (look at the output of
a data only pg_dump for queries to turn off/on trigger).


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

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

[SQL] possible bug in \df+

[Rajesh Kumar Mallah.]

Hi there ,

" SELECT prosrc from  pg_proc where proname=''; "

and 

\df+   are reporting two different versions
for FUNCTION body.


eg:

\df+ category_path shows:


DECLARE v_category_id ALIAS FOR $1;
DECLARE tmp_record RECORD;
DECLARE tmp_id VARCHAR;
DECLARE tmp_code VARCHAR;

BEGIN
tmp_code := '' ;
SELECT INTO tmp_record b.name , a.category_id , a.parent_category_id  from 
category_tree_new a natural inner
 join categories_new b where category_id=v_category_id and 
a.link is not true ;
IF NOT FOUND THEN
RETURN  'Home';
END IF;

IF tmp_record.category_id = 0 THEN
RETURN tmp_record.name;
END IF;

tmp_id := category_path_text(tmp_record.parent_category_id) ;

IF tmp_record.category_id <> 0 THEN
tmp_code := tmp_id || ' > ' || tmp_record.name ;
END IF;

RETURN tmp_code;
END;


and select from pg_proc gives

DECLARE v_category_id ALIAS FOR $1;
DECLARE tmp_record RECORD;
DECLARE tmp_id VARCHAR;
DECLARE tmp_code VARCHAR;

BEGIN
tmp_code := '' ;
SELECT INTO tmp_record b.name , a.category_id , a.parent_category_id  from 
category_tree_new a natural inner join categories_new b where 
category_id=v_category_id and a.link is not true ;
IF NOT FOUND THEN
RETURN '0' || ':' ||  'ROOT';
END IF;

IF tmp_record.category_id = 0 THEN
RETURN tmp_record.category_id || ':' ||  tmp_record.name ;
END IF;

tmp_id := category_path(tmp_record.parent_category_id) ;

IF tmp_record.category_id <> 0 THEN
tmp_code := tmp_id   || '#' || tmp_record.category_id || ':' || 
tmp_record.name ;
END IF;

RETURN tmp_code;
END;



regds
mallah.




-- 
Rajesh Kumar Mallah,
Project Manager (Development)
Infocom Network Limited, New Delhi
phone: +91(11)6152172 (221) (L) ,9811255597 (M)

Visit http://www.trade-india.com ,
India's Leading B2B eMarketplace.



---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]

[SQL] Seeking advice regarding a design problem

[Wei Weng]

I am running postgresql as database backend, and I have some scripts
dealing with constant incoming data and then insert these data into the
database, in a quite complex way, involving a couple of procedures.

But the performance of the database is worse than I had thought. After
about 100 times of the script being run, the speed of the insertion
slowed down dramatically. But it went back to the regular fast speed
after I did a vacuum analyze.

how can I redesign the system to avoid the bottleneck? And why is it
that postgresql can slow down so much after doing some complex
operations?


Thanks


-- 
Wei Weng
Network Software Engineer
KenCast Inc.



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

http://archives.postgresql.org

Re: [SQL] Seeking advice regarding a design problem

[Wei Weng]

Forgot to mention, the version of postgresql I am running is 7.1.3.

On Fri, 2002-08-02 at 12:16, Wei Weng wrote:
> I am running postgresql as database backend, and I have some scripts
> dealing with constant incoming data and then insert these data into the
> database, in a quite complex way, involving a couple of procedures.
> 
> But the performance of the database is worse than I had thought. After
> about 100 times of the script being run, the speed of the insertion
> slowed down dramatically. But it went back to the regular fast speed
> after I did a vacuum analyze.
> 
> how can I redesign the system to avoid the bottleneck? And why is it
> that postgresql can slow down so much after doing some complex
> operations?
> 
> 
> Thanks
> 
> 
> -- 
> Wei Weng
> Network Software Engineer
> KenCast Inc.
> 
> 
> 
> ---(end of broadcast)---
> TIP 6: Have you searched our list archives?
> 
> http://archives.postgresql.org
> 
-- 
Wei Weng
Network Software Engineer
KenCast Inc.



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

[SQL] Ltree usage..

[Rajesh Kumar Mallah.]

Hi Oleg,

I am trying to use contrib/ltree for one of my applications.

the query below works fine for me.

Qry1: SELECT   path   from  unified_data where path ~ '*.180.*'  and path ~ '*.1.*';

is there any way of compacting it for example

Qry2: SELECT   path   from  unified_data where path ~ '*.180.*'  or path ~ '*.1.*'; is 
better
written as 
Qry3: SELECT   path   from  unified_data where path ~ '*.180|1.*' ;

also is qry3 better to Qry2 in terms of performance?

regds
mallah.



-- 
Rajesh Kumar Mallah,
Project Manager (Development)
Infocom Network Limited, New Delhi
phone: +91(11)6152172 (221) (L) ,9811255597 (M)

Visit http://www.trade-india.com ,
India's Leading B2B eMarketplace.



---(end of broadcast)---
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to [EMAIL PROTECTED] so that your
message can get through to the mailing list cleanly

Re: [SQL] Ltree usage..

[Oleg Bartunov]

On Fri, 2 Aug 2002, Rajesh Kumar Mallah. wrote:

>
> Hi Oleg,
>
> I am trying to use contrib/ltree for one of my applications.
>
> the query below works fine for me.
>
> Qry1: SELECT   path   from  unified_data where path ~ '*.180.*'  and path ~ '*.1.*';
>
> is there any way of compacting it for example
>
> Qry2: SELECT   path   from  unified_data where path ~ '*.180.*'  or path ~ '*.1.*'; 
>is better
> written as
> Qry3: SELECT   path   from  unified_data where path ~ '*.180|1.*' ;

Qry2 and Qry3 are equvalent and Qry3 is faster but not much.
But Qry1 is not the same as Qry2 !!!

Qry1 could be rewritten as:

SELECT   path   from  unified_data where path @ '180 & 1';

>
> also is qry3 better to Qry2 in terms of performance?
>
> regds
> mallah.
>
>
>
>

Regards,
Oleg
_
Oleg Bartunov, sci.researcher, hostmaster of AstroNet,
Sternberg Astronomical Institute, Moscow University (Russia)
Internet: [EMAIL PROTECTED], http://www.sai.msu.su/~megera/
phone: +007(095)939-16-83, +007(095)939-23-83


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

Re: [SQL] Seeking advice regarding a design problem

[Stephan Szabo]

On 2 Aug 2002, Wei Weng wrote:

> I am running postgresql as database backend, and I have some scripts
> dealing with constant incoming data and then insert these data into the
> database, in a quite complex way, involving a couple of procedures.
>
> But the performance of the database is worse than I had thought. After
> about 100 times of the script being run, the speed of the insertion
> slowed down dramatically. But it went back to the regular fast speed
> after I did a vacuum analyze.
>
> how can I redesign the system to avoid the bottleneck? And why is it

Upgrade to 7.2 so that you can vacuum while other things are going on
and vacuum analyze after modifying a large portion of the database (note
that if the database is particularly large you'll probably need to change
the free space map configuration as well).

It's hard to tell what particularly you're running into, is it just a
case that you're accessing the dead tuples and that's slowing it down,
is it that you're changing the data in a fashion that changes how the
optimizer should be choosing to do queries, etc...



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

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

Re: [SQL] Seeking advice regarding a design problem

[Wei Weng]

On Fri, 2002-08-02 at 14:24, Stephan Szabo wrote:
> On 2 Aug 2002, Wei Weng wrote:
> 
> > I am running postgresql as database backend, and I have some scripts
> > dealing with constant incoming data and then insert these data into the
> > database, in a quite complex way, involving a couple of procedures.
> >
> > But the performance of the database is worse than I had thought. After
> > about 100 times of the script being run, the speed of the insertion
> > slowed down dramatically. But it went back to the regular fast speed
> > after I did a vacuum analyze.
> >
> > how can I redesign the system to avoid the bottleneck? And why is it
> 
> Upgrade to 7.2 so that you can vacuum while other things are going on
> and vacuum analyze after modifying a large portion of the database (note
> that if the database is particularly large you'll probably need to change
> the free space map configuration as well).
I found this in my postgresql.conf

#shared_buffers = 64# 2*max_connections, min 16
#max_fsm_relations = 100# min 10, fsm is free space map
#max_fsm_pages = 1  # min 1000, fsm is free space map
#max_locks_per_transaction = 64 # min 10
#wal_buffers = 8# min 4

Which ones are critical to the insertion performance? I looked for them
in the interactive dev doc, but the descriptions were not clear enough.

> 
> It's hard to tell what particularly you're running into, is it just a
> case that you're accessing the dead tuples and that's slowing it down,
What do you mean by "dead tuples"?


> is it that you're changing the data in a fashion that changes how the
> optimizer should be choosing to do queries, etc...
> 

Thanks!
 
-- 
Wei Weng
Network Software Engineer
KenCast Inc.



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

http://archives.postgresql.org

Re: [SQL] Seeking advice regarding a design problem

[Stephan Szabo]

On 2 Aug 2002, Wei Weng wrote:

> On Fri, 2002-08-02 at 14:24, Stephan Szabo wrote:
> > On 2 Aug 2002, Wei Weng wrote:
> >
> > > I am running postgresql as database backend, and I have some scripts
> > > dealing with constant incoming data and then insert these data into the
> > > database, in a quite complex way, involving a couple of procedures.
> > >
> > > But the performance of the database is worse than I had thought. After
> > > about 100 times of the script being run, the speed of the insertion
> > > slowed down dramatically. But it went back to the regular fast speed
> > > after I did a vacuum analyze.
> > >
> > > how can I redesign the system to avoid the bottleneck? And why is it
> >
> > Upgrade to 7.2 so that you can vacuum while other things are going on
> > and vacuum analyze after modifying a large portion of the database (note
> > that if the database is particularly large you'll probably need to change
> > the free space map configuration as well).
> I found this in my postgresql.conf
>
> #shared_buffers = 64# 2*max_connections, min 16
> #max_fsm_relations = 100# min 10, fsm is free space map
> #max_fsm_pages = 1  # min 1000, fsm is free space map
> #max_locks_per_transaction = 64 # min 10
> #wal_buffers = 8# min 4
>
> Which ones are critical to the insertion performance? I looked for them
> in the interactive dev doc, but the descriptions were not clear enough.

In general shared_buffers should be higher than the default.  I'd suggest
incrementing it while testing to get an idea of what works for your
system.

In 7.2, you may want to raise max_fsm_pages if you're noticing that
non-full vacuums are not reclaiming all of your space.

> > It's hard to tell what particularly you're running into, is it just a
> > case that you're accessing the dead tuples and that's slowing it down,
> What do you mean by "dead tuples"?

Tuples that are not visible to the transaction.  Postgres uses a non
overwriting storage manager, so any updates or deletes leave the old
row in place.  Vacuum removes rows that no transaction can see.
If you vacuum analyze you'll get some stats about how many rows were
removed and such.

Another important question is whether you've got any foreign keys or
triggers on the tables since those may be making a difference as well.


---(end of broadcast)---
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])

Re: [SQL] [NOVICE] Aggregates and Indexes

[Josh Berkus]

Adam,

> I do a lot of reporting based on such SQL statements.  Counting rows from
> large datasets.  Since the PG gurus don't seem to think this is such a big
> deal can someone enlighten me as to why?

I am not a core developer, but I will take a crack at your question anyway 
based on my personal knowledge.  I am sure that Tom, Bruce, or Stephan will 
correct my mistaken assumptions.

Actually, the inability to index MAX, MIN, and COUNT is an annoyance I think 
everyone would like to fix; it's just that the technical challenge is much 
greater than the benefit for the core team.   If you know a programmer who 
wants to tackle it, go ahead.

Presumably you've already read the many previous e-mails on why it is a 
techincal challenge.

Now, even if that challenge were solved, indexing for aggregates would still 
be of limited usefulness because:

Few (if any) RDBMSs can index for SUM, STDEV, or other "calcuation" 
aggregates.  This is because the value of every single record must be incuded 
and estimates are not possible, so the performance gain from using an index 
is infinitessimal except for those RDBMSs with very slow file access times.

For Postgres custom aggregates, using a standard index is impossible, for 
reasons I think are obvious.

That leaves MAX, MIN, and COUNT.All of these aggregates should, in an 
ideal world, be index-responsive for large data sets.  Once again, for small 
data sets or subsets, indexes are not useful.   And there is a workaround for 
Min and Max.

So what we'd be looking at is either developing a special parser routine for 
MIN, MAX, and COUNT (and them only) just to index for those aggregates, or 
coming up with a new type of index just for aggregates.   The first approach 
is a bit of kludge that would require a lot of debugging; the second is 
probably the best long-term solution, but would require a great deal of 
innovation.


-- 
-Josh Berkus
 Aglio Database Solutions
 San Francisco


---(end of broadcast)---
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])

Re: [SQL] [NOVICE] Aggregates and Indexes

[Tom Lane]

Josh Berkus <[EMAIL PROTECTED]> writes:
> For Postgres custom aggregates, using a standard index is impossible, for 
> reasons I think are obvious.

> That leaves MAX, MIN, and COUNT.All of these aggregates should, in an 
> ideal world, be index-responsive for large data sets.

While it's fairly clear how one might use an index for MAX/MIN
(basically, make the optimizer transform it into a SELECT ... ORDER BY
... LIMIT 1 operation, which can then be done with an indexscan),
I really don't see how an index can help for COUNT.

The real problem with COUNT is that any attempt to maintain such a value
on-the-fly creates a single-point bottleneck for all insertions and
deletions on the table.  The perspective of most of the developers is
that that cost outweighs any possible savings from having an
instantaneous COUNT operation.

When you add in the issue that under MVCC there isn't a unique COUNT
that's the same for all transactions, it's just not worth thinking
about.  (And do I need to point out that with WHERE conditions,
GROUP BY, or a variable COUNT argument, all hope of such optimization
disappears anyway?  A global rowcount doesn't help in those cases.)

The MAX/MIN issue will probably be addressed someday, but since there
is a good workaround it's not very high on anyone's TODO queue.  We have
many more-pressing problems.

regards, tom lane

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

[SQL] What about this?

[Wei Weng]

Why can't postmaster run VACUUM ANALYZE automatically every once in a
while? Since it is a very useful feature...


-- 
Wei Weng
Network Software Engineer
KenCast Inc.



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

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

Re: [SQL] What about this?

[Josh Berkus]

Wei,

> Why can't postmaster run VACUUM ANALYZE automatically every once in a
> while? Since it is a very useful feature...

Because such a practice is not appropriate for everyone's database 
installation, that's why.

-- 
-Josh Berkus
 Aglio Database Solutions
 San Francisco


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

http://archives.postgresql.org

[SQL] Add Primary Key to a Table

[Tom Haddon]

Hi Folks,

I'm trying to use the ALTER TABLE command to add a primary key to a table.

The command I am using is:

ALTER TABLE "agency_contact_info" ADD CONSTRAINT "agency_contact_info_pkey"
PRIMARY KEY ("id");

I get a message saying "ALTER TABLE / ADD CONSTRAINT not implemented for
that constraint type". Is this a bug, or am I doing something wrong?


The reason I am trying to do this is that I want to split a table with a
large number of fields up into a few tables with a sub-set of fields from
the original table, linked by FOREIGN KEY on "id". I had thought the best
way to do this was to use SELECT INTO and then try to alter the tables to
include PRIMARY and FOREIGN keys where applicable. Is there a better way to
do this?

Thanks, Tom

___
Tom Haddon
IT Director
The Better Health Foundation
414 Thirteenth Street, Suite 450
Oakland, CA 94612
(510) 444-5096
www.betterhealthfoundation.org
___


---(end of broadcast)---
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to [EMAIL PROTECTED] so that your
message can get through to the mailing list cleanly

Re: [SQL] Add Primary Key to a Table

[Josh Berkus]

Tom,

> ALTER TABLE "agency_contact_info" ADD CONSTRAINT "agency_contact_info_pkey"
> PRIMARY KEY ("id");
> 
> I get a message saying "ALTER TABLE / ADD CONSTRAINT not implemented for
> that constraint type". Is this a bug, or am I doing something wrong?

What version are you using?  That command is supported with Postgres 7.2, but 
not with earlier versions.

-- 
-Josh Berkus
 Aglio Database Solutions
 San Francisco


---(end of broadcast)---
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to [EMAIL PROTECTED] so that your
message can get through to the mailing list cleanly

Re: [SQL] Add Primary Key to a Table

[Tom Haddon]

Hi Josh,

I think that solves it for me - I'm using 7.1.3 - will upgrade
appropriately.

Thanks, Tom

-Original Message-
From: Josh Berkus [mailto:[EMAIL PROTECTED]]
Sent: Friday, August 02, 2002 4:20 PM
To: Tom Haddon; [EMAIL PROTECTED]
Subject: Re: [SQL] Add Primary Key to a Table



Tom,

> ALTER TABLE "agency_contact_info" ADD CONSTRAINT
"agency_contact_info_pkey"
> PRIMARY KEY ("id");
>
> I get a message saying "ALTER TABLE / ADD CONSTRAINT not implemented for
> that constraint type". Is this a bug, or am I doing something wrong?

What version are you using?  That command is supported with Postgres 7.2,
but
not with earlier versions.

--
-Josh Berkus
 Aglio Database Solutions
 San Francisco



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

http://archives.postgresql.org

Re: [SQL] What about this?

[Tom Lane]

Josh Berkus <[EMAIL PROTECTED]> writes:
>> Why can't postmaster run VACUUM ANALYZE automatically every once in a
>> while? Since it is a very useful feature...

> Because such a practice is not appropriate for everyone's database 
> installation, that's why.

No, the correct answer is "read the TODO list"...

regards, tom lane

---(end of broadcast)---
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])

Re: [SQL] [NOVICE] Aggregates and Indexes

[Bruce Momjian]

Josh Berkus wrote:
> 
> Adam,
> 
> > I do a lot of reporting based on such SQL statements.  Counting rows from
> > large datasets.  Since the PG gurus don't seem to think this is such a big
> > deal can someone enlighten me as to why?
> 
> I am not a core developer, but I will take a crack at your question anyway 
> based on my personal knowledge.  I am sure that Tom, Bruce, or Stephan will 
> correct my mistaken assumptions.

I have seen a few mentions in the past weeks about core vs. non-core
developers.  I should reiterate that the core group feels there is no
distinction between the opinions of the core people and the other major
developers.  Everyone gets just one vote.

The only reason for core is to deal with certain "sensitive" issues that
can't be dealt with in public forums, and making _big_ decisions like
should we release on Friday or Monday.  ;-)

-- 
  Bruce Momjian|  http://candle.pha.pa.us
  [EMAIL PROTECTED]   |  (610) 853-3000
  +  If your life is a hard drive, |  830 Blythe Avenue
  +  Christ can be your backup.|  Drexel Hill, Pennsylvania 19026

---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]

Re: [SQL] [ADMIN] 3-tier

[Chad R. Larson]

At 04:28 AM 7/31/02 , Elielson Fontanezi wrote:
>I'm interested in programming a first prototype to demonstrate 3-tier 
>programming using PostGRE database.

We're using FreeBSD/Apache for web servers, Macromedia JRun for the servlet 
container and PostgreSQL for the back-end database.

We've just started looking at using Tomcat as a replacement for JRun, as it 
is bundled with Solaris 9.


 -crl
--
Chad R. Larson (CRL22)[EMAIL PROTECTED]
   Eldorado Computing, Inc.   602-604-3100
  5353 North 16th Street, Suite 400
Phoenix, Arizona   85016-3228


---(end of broadcast)---
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to [EMAIL PROTECTED] so that your
message can get through to the mailing list cleanly

Re: [SQL] Ltree usage..

[Rajesh Kumar Mallah.]

Hi Oleg,

It does not yeild the correct result for me.
I am providing more details this time.

path is ltree [] for me not ltree,

 Column   |  Type  |Modifiers
++-
 profile_id | integer| not null default 
nextval('"unified_data_profile_id_seq"'::text)
 co_name| character varying(255) |
 city   | character varying(100) |
 path   | ltree[]|
Indexes: unified_data_path
Unique keys: unified_data_co_name_key,
 unified_data_profile_id_key



eg if my sample data set is.

profile_id |   path
+--
  25477 | {0.180.830,0.180.848}
  26130 | {0.180.848}
   2928 | {0.64.65,0.64.67,0.180.830,0.180.848,0.180.849}
  26129 | {0.180.848}
  26126 | {0.180.848}
  26127 | {0.180.848}
  26128 | {0.180.848}
  24963 | {0.180.830,0.180.848}
  26125 | {0.180.848}
   7239 | {0.246.256,0.246.282,0.180.848,0.246.857,0.76.1255.1161}
(10 rows)

what query shud i use to extract profiles where path contains *.64.* and *.180.*

eg this query
SELECT   profile_id,pathfrom  unified_data where path ~ '*.180.*'  and path ~ 
'*.64.*' limit 10;
 profile_id |  path
+-
   2928 | {0.64.65,0.64.67,0.180.830,0.180.848,0.180.849}
   3238 | {0.64.68,0.180.830,0.395.904}
   6255 | {0.180.227,0.64.814}
   6153 | {0.180.227,0.505.518,0.64.814}
   6268 | {0.180.227,0.64.814}
   6267 | {0.180.227,0.64.814}
   6120 | {0.180.227,0.64.814}
   6121 | {0.180.227,0.64.814}
   6084 | {0.180.227,0.64.814}
   6066 | {0.180.227,0.64.810}
(10 rows)
gives me the correct result but i am not sure if its the most efficient.

I will be using it for medium sized dataset  approx 100,000 that there will be such
search on upto four such indexed columns.

regds
mallah.





On Friday 02 August 2002 22:30, Oleg Bartunov wrote:
> On Fri, 2 Aug 2002, Rajesh Kumar Mallah. wrote:
> > Hi Oleg,
> >
> > I am trying to use contrib/ltree for one of my applications.
> >
> > the query below works fine for me.
> >
> > Qry1: SELECT   path   from  unified_data where path ~ '*.180.*'  and path
> > ~ '*.1.*';
> >
> > is there any way of compacting it for example
> >
> > Qry2: SELECT   path   from  unified_data where path ~ '*.180.*'  or path
> > ~ '*.1.*'; is better written as
> > Qry3: SELECT   path   from  unified_data where path ~ '*.180|1.*' ;
>
> Qry2 and Qry3 are equvalent and Qry3 is faster but not much.
> But Qry1 is not the same as Qry2 !!!
>
> Qry1 could be rewritten as:
>
> SELECT   path   from  unified_data where path @ '180 & 1';
>
> > also is qry3 better to Qry2 in terms of performance?
> >
> > regds
> > mallah.
>
>   Regards,
>   Oleg
> _
> Oleg Bartunov, sci.researcher, hostmaster of AstroNet,
> Sternberg Astronomical Institute, Moscow University (Russia)
> Internet: [EMAIL PROTECTED], http://www.sai.msu.su/~megera/
> phone: +007(095)939-16-83, +007(095)939-23-83

-- 
Rajesh Kumar Mallah,
Project Manager (Development)
Infocom Network Limited, New Delhi
phone: +91(11)6152172 (221) (L) ,9811255597 (M)

Visit http://www.trade-india.com ,
India's Leading B2B eMarketplace.



---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]