[SQL] length of array

[Chris Faulkner]

Hello

Is there a function in postgres to return the length of an array field ? I
have seen array_dims(array) but this returns a character value. Ideally, I'd
like something numeric returned.

Thanks

Chris



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

   http://archives.postgresql.org

Re: [SQL] length of array

[Chris Faulkner]

Hello

Thanks for that solution, Joe - nice use of nested functions !

Related to this problem, I want to constrain a selection using elements of
this variable length array. I want to constrain where all elements of the
array are 0.

I would like to do it like this in Oracle

select field from table N where [conditions]
and NVL(N.level[1],0) = 0
and NVL(N.level[2],0) = 0
and NVL(N.level[3],0) = 0
and NVL(N.level[4],0) = 0

So if a row only has two elements in the array, but the first two both had
values "0", then the row would return.  At the moment, I have this :

and N.level[1] = 0
and N.level[2] = 0
and N.level[3] = 0
and N.level[4] = 0

but my row with 2 elements in the array won't be returned with this
condition.

Chris

-Original Message-
From: Joe Conway [mailto:[EMAIL PROTECTED]
Sent: 28 August 2003 01:40
To: Chris Faulkner
Cc: [EMAIL PROTECTED]
Subject: Re: [SQL] length of array


Chris Faulkner wrote:
> Is there a function in postgres to return the length of an array field ? I
> have seen array_dims(array) but this returns a character value. Ideally,
I'd
> like something numeric returned.
>

Afraid not in 7.3.x or earlier. In 7.3.x (but not earlier) you *can* do
this (for a one-dimensional array at least):

SELECT
   replace(split_part(array_dims(array_fld),':',1),'[','')::int
   as low
FROM tbl;

SELECT
   replace(split_part(array_dims(array_fld),':',2),']','')::int
   as high
FROM tbl;


In 7.4 (now in beta) there are two new functions, array_lower() and
array_upper() that do what you're looking for:

regression=# select array_lower(array_fld, 1) from tbl;
  array_lower
-
1
(1 row)

regression=# select array_upper(array_fld, 1) from tbl;
  array_upper
-
2
(1 row)

See the following links for more on 7.4's array support:
http://developer.postgresql.org/docs/postgres/arrays.html
http://developer.postgresql.org/docs/postgres/functions-array.html
http://developer.postgresql.org/docs/postgres/sql-expressions.html#SQL-SYNTA
X-ARRAY-CONSTRUCTORS

HTH,

Joe






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

   http://www.postgresql.org/docs/faqs/FAQ.html

[SQL] create new field

[Chris Faulkner]

Hello

I would like to change the type of a column. At the moment, it is varchar(4)
but I would like it to be int. All values in the field at the moment are
actually integer.

I tried a way I had seen in the archives - it foes along the lines of adding
a column, using update, drop the old column and rename the new one.

alter table tab add column new_col int4;
update tab set new_col = "OLD_COL";
ERROR:  column "new_col" is of type integer but expression is of type
characte
r
You will need to rewrite or cast the expression

OK - so I tried casting.

template1=# update tab set new_col = "OLD_COL"::int4;
ERROR:  Cannot cast type character to integer

I understand this - some tables might have characters in the varchar but how
to get around it in my case ? I know that my character field has only
integers in it ?

Thanks

Chris



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

   http://www.postgresql.org/docs/faqs/FAQ.html

[SQL] monitor sessions

[Chris Faulkner]

Hello

Can anyone tell me - is there a system table or view that I can query to
show all current sessions ?

Thanks


Chris



---(end of broadcast)---
TIP 7: don't forget to increase your free space map settings

[SQL] sql performance and cache

[Chris Faulkner]

Hello all

I have two very similar queries which I need to execute. They both have
exactly the same from / where conditions. When I execute the first, it takes
about 16 seconds. The second is executed almost immediately after, it takes
13 seconds. In short, I'd like to know why the query result isn't being
cached and any ideas on how to improve the execution.

The first query attempts to find the maximum size of an array in the result
set- the field is called "level". IT contains anything between 1 and 10
integers. I just need to know what the largest size is. I do this to find
out the maximum size of the "level" array.

"max(replace(split_part(array_dims(level),':',2),']','')::int)"

I know this is big and ugly but is there any better way of doing it ?

The second query just returns the result set - it has exactly the same
FROM/Where clause.

OK - so I could execute the query once, and get the maximum size of the
array and the result set in one. I know what I am doing is less than optimal
but I had expected the query results to be cached. So the second execution
would be very quick. So why aren't they ? I have increased my cache size -
shared_buffers is 2000 and I have doubled the default max_fsm... settings
(although I am not sure what they do). sort_mem is 8192.

The from / where is

FROM oscar_node N, oscar_point P
where N."GEOM_ID_OF_POINT" = P."POINT_ID"
and N."TILE_REF" = P."TILE_REF"
and N."TILE_REF" in ('TQ27NE','TQ28SE','TQ37NW','TQ38SW')
and P."TILE_REF" in ('TQ27NE','TQ28SE','TQ37NW','TQ38SW')
and P."FEAT_CODE" = 3500
and P.wkb_geometry && GeometryFromText('BOX3D(529540.0 179658.88,530540.0
180307.12)'::box3d,-1)

oscar_node and oscar_point both have about 3m rows. PK on oscar_node is
composite of "TILE_REF" and "NODE_ID". PK on oscar_point is "TILE_REF" and
"POINT_ID". The tables are indexed on feat_code and I have an index on
wkb_geometry. (This is a GIST index). I have increased the statistics size
and done the analyze command.

Here is my explain plan

 Nested Loop  (cost=0.00..147.11 rows=1 width=148)
   Join Filter: ("inner"."GEOM_ID_OF_POINT" = "outer"."POINT_ID")
   ->  Index Scan using gidx_oscar_point on oscar_point p  (cost=0.00..61.34
rows=1 width=57)
 Index Cond: (wkb_geometry && 'SRID=-1;BOX3D(529540 179658.88
0,530540 180307.12 0)'::geometry)
 Filter: ((("TILE_REF" = 'TQ27NE'::bpchar) OR ("TILE_REF" =
'TQ28SE'::bpchar) OR ("TILE_REF" = 'TQ37NW'::bpchar) OR ("TILE_REF" =
'TQ38SW'::bpchar)) AND ("FEAT_CODE" = 3500))
   ->  Index Scan using idx_on_tile_ref on oscar_node n  (cost=0.00..85.74
rows=2 width=91)
 Index Cond: (n."TILE_REF" = "outer"."TILE_REF")
 Filter: (("TILE_REF" = 'TQ27NE'::bpchar) OR ("TILE_REF" =
'TQ28SE'::bpchar) OR ("TILE_REF" = 'TQ37NW'::bpchar) OR ("TILE_REF" =
'TQ38SW'::bpchar))


I am seeing this message in my logs.

"bt_fixroot: not valid old root page"

Maybe this is relevant to my performance problems.

I know this has been a long message but I would really appreciate any
performance tips.

Thanks


Chris



---(end of broadcast)---
TIP 9: the planner will ignore your desire to choose an index scan if your
  joining column's datatypes do not match

Re: [SQL] sql performance and cache

[Chris Faulkner]

Hello

Thanks for the reply.

> The short answer is that PG doesn't cache query results. The only
> way it could
> do so safely is to lock all tables you access to make sure that no other
> process changes them. That would effectively turn PG into a
> single-user DB in
> short notice.

I am not sure I agree with you. I have done similar things with Oracle and
found that the second query will execute much more quickly than the first.
It could be made to work in at least two scenarios

- as a user/application perspective - you accept that the result might not
be up-to-date and take what comes back. This would be acceptable in my case
because I know that the tables will not change.
OR
- the database could cache the result set. If some of the data is changed by
another query or session, then the database flushes the result set out of
the cache.

> I assume these two queries are linked? If you rely on the max size being
> unchanged and have more than one process using the database, you
> should make
> sure you lock the rows in question.

I can rely on the max size remaining the same. As I mentioned above, the
tables are entirely read only. The data will not be updated or deleted by
anyone - I don't need to worry about that. The data will be updated en masse
once every 3 months.

> There is a discussion of the postgresql.conf file and how to tune it at:
>   http://www.varlena.com/varlena/GeneralBits/Tidbits/index.php

Thanks for that.

> Given the explain attached, 16 secs seems slow. Could you post an EXPLAIN
> ANALYSE of either/both queries to the performance list. I'd drop
> the sql list
> when we're just talking about performance.

To be honest, my main concern was about the cache. If the second one could
use a cache amd execute in 2 seconds, that would be better that reducing the
execution of each individual query by 30% or so.

Thanks for the offer of help on this one. explain analyze gives me the same
as the last message - did you want verbose ?

 Nested Loop  (cost=0.00..147.11 rows=1 width=148) (actual
time=84.00..12323.00 rows=67 loops=1)
   Join Filter: ("inner"."GEOM_ID_OF_POINT" = "outer"."POINT_ID")
   ->  Index Scan using gidx_oscar_point on oscar_point p  (cost=0.00..61.34
rows=1 width=57) (actual time=0.00..9.00 rows=67 loops=1)
 Index Cond: (wkb_geometry && 'SRID=-1;BOX3D(529540 179658.88
0,530540 1
80307.12 0)'::geometry)
 Filter: ((("TILE_REF" = 'TQ27NE'::bpchar) OR ("TILE_REF" =
'TQ28SE'::bp
char) OR ("TILE_REF" = 'TQ37NW'::bpchar) OR ("TILE_REF" = 'TQ38SW'::bpchar))
AND
 ("FEAT_CODE" = 3500))
   ->  Index Scan using idx_on_tile_ref on oscar_node n  (cost=0.00..85.74
rows=2 width=91) (actual time=0.06..150.07 rows=4797 loops=67)
 Index Cond: (n."TILE_REF" = "outer"."TILE_REF")
 Filter: (("TILE_REF" = 'TQ27NE'::bpchar) OR ("TILE_REF" =
'TQ28SE'::bpchar) OR ("TILE_REF" = 'TQ37NW'::bpchar) OR ("TILE_REF" =
'TQ38SW'::bpchar))
 Total runtime: 12325.00 msec
(9 rows)

Thanks


Chris



---(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