[SQL] Efficient deletions?
Hello,
This is my first message to this list. I'm curently trying to
improve my postgres skillsm I've assigned myself a project involving a
database whose dataset is quite larger than those I had to use until
now.
I'm curently facing a trouble with the DELETE query: I need to
delete from a table a set of rows choosen by a sub-select. The query
curently looks like this:
DELETE FROM foo WHERE (col1, col2) IN (SELECT...)
It seems from the output of ANALYSE that the sub-query is executed for
each foo rows. That's really not efficient as the set returned by the
sub-select is constant.
I had a similar trouble with a SELECT query but it was eavily
solved by placing one of the sub-select in the FROM part of the query.
Unfortunately it look that can't be done this way with DELETE.
Is there a way to instruct the planner that this sub-query is
constant over the time of the DELETE execution? Or is there another
more efficent way to express this?
I conducted tests with a sub-select returning about 20,000
rows and a foo table of 370,000 rows, out of patience I had to
interrupt the query after five hours. That worries me as this query is
part of an update process that could be fired several times a day.
--
%!PS
297.6 420.9 translate 90 rotate 0 setgray gsave 0 1 1{pop 0 180 moveto 100
180 170 100 170 -10 curveto 180 -9 180 -9 190 -10 curveto 190 100 100 180
0 180 curveto fill 180 rotate}for grestore/Bookman-LightItalic findfont
240 scalefont setfont -151.536392 -63.7998886 moveto (bp)show showpage
---(end of broadcast)---
TIP 6: Have you searched our list archives?
http://archives.postgresql.org
[SQL] Comparing arrays
Hello,
I advance in my postgres exploration and found something that
looks quite strange, or at least unexpected regarding the present
7.3.3 documentation.
In two table I store "objects" and their attributes. The
attributes, which are not meant to be searched, are stored as unbound
arrays of varchars. I have a query that needs to use those attributes
on both sides of an EXCEPT statement:
SELECT left.id, left.attribs FROM left
EXCEPT SELECT right.id, right.attribs FROM right;
That query can't be planed because of the following error: "Unable to
identify an ordering operator '<' for type 'character varying[]'".
I thought that I could build such an operator using PL/pgSQL,
unfortunately this language can't receive arguments of type ANYARRAY.
So this led me to the creation of a new ATTRIBUTES data type, the
should be acceptable as an argument to a PL/pgSQL procedure.
When I tried to create such a datatype, using a query modelled
after the documentation examples:
CREATE TYPE attributes (INPUT=array_in, OUTPUT=array_out,
INTERNALLENGTH=VARIABLE, ELEMENT=VARCHAR);
I'm signaled that the array_out procedure is not defined: "ERROR:
TypeCreate: function array_out(attributes) does not exist". That error
sounds strange as the CREATE TYPE manual describes uniform array type
creation as illustrated above and that array_out() seems to exist as
shown bellow.
SELECT proname, oidvectortypes(proargtypes) FROM pg_proc
WHERE proname LIKE 'array_%';
proname | oidvectortypes
-+
array_dims | anyarray
array_eq| anyarray, anyarray
array_in| cstring, oid, integer
array_length_coerce | anyarray, integer, boolean
array_out | anyarray
(5 rows)
All of this leads to the unavoidable questions:
1/ What went wrong with the ATTRIBUTES datatype creation?
How to correctly create it using 7.3.x backends?
2/ There may be better paths than creating a new datatype and
the associated operators that would permit using unbound
uniform arrays on both sides of an EXCEPT statement.
What would be such paths?
Regards.
--
%!PS
297.6 420.9 translate 90 rotate 0 setgray gsave 0 1 1{pop 0 180 moveto 100
180 170 100 170 -10 curveto 180 -9 180 -9 190 -10 curveto 190 100 100 180
0 180 curveto fill 180 rotate}for grestore/Bookman-LightItalic findfont
240 scalefont setfont -151.536392 -63.7998886 moveto (bp)show showpage
---(end of broadcast)---
TIP 6: Have you searched our list archives?
http://archives.postgresql.org
Re: [SQL] Comparing arrays
On Sat, Aug 09, 2003 at 09:01:13AM -0700, Joe Conway wrote:
>
> This should work on 7.4 beta.
And now it works too in 7.3. I've implemented the <, <=, =,
<>, =>, and > operators in PL/pgSQL for the VARCHAR[] type and
assembled them into an operator class. This is just a bit slow but
that's bearable considering that the queries using them are just
exceptional administrativia tasks.
> In any case, 7.3 and before have many issues with effective use of
> arrays. For instance, even if you got all this working, you'd find
> working with arrays in PL/pgSQL painful if not impossible.
The only annoyance trouble I had with array manipulation is
the array_dims() function. I had to wrap it inside
split_part(split_part(array_dims($1), ']', 1), ':', 2)::INT
> If you really depend on arrays, I'd strongly suggest testing on 7.4 beta
> and moving to 7.4 as soon as it is released.
Right now I'll try to stay away from betas or .1 releases: I'm
not yet confident enough with my skills and may not be able to decide
if a failure is mine or postgresql's.
Thanks for your kind answer.
--
%!PS
297.6 420.9 translate 90 rotate 0 setgray gsave 0 1 1{pop 0 180 moveto 100
180 170 100 170 -10 curveto 180 -9 180 -9 190 -10 curveto 190 100 100 180
0 180 curveto fill 180 rotate}for grestore/Bookman-LightItalic findfont
240 scalefont setfont -151.536392 -63.7998886 moveto (bp)show showpage
---(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] sequence
On Fri, Aug 15, 2003 at 12:32:36PM +0300, cristi wrote:
> What is wrong here?
>
> insert into table_name (field_name) values (select
> setval('sequence_name')-1) as currval);
This should be better:
INSERT INTO table_name (field_name) VALUES (nextval('sequence_name'));
--
%!PS
297.6 420.9 translate 90 rotate 0 setgray gsave 0 1 1{pop 0 180 moveto 100
180 170 100 170 -10 curveto 180 -9 180 -9 190 -10 curveto 190 100 100 180
0 180 curveto fill 180 rotate}for grestore/Bookman-LightItalic findfont
240 scalefont setfont -151.536392 -63.7998886 moveto (bp)show showpage
---(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] "SELECT IN" Still Broken in 7.4b
On Wed, Aug 20, 2003 at 04:32:19PM -0400, Tom Lane wrote:
>
> against actual elapsed time (cf psql's \timing option) would tell.
What is measured by the \timing option? The figures reported
are slightly larger than those loged when the log_duration parameter
is true.
=> select count(*) from foo;
count
362921
(1 row)
Time: 5500.88 ms
while in syslog, the timing was "LOG: duration: 5.499783 sec". There
is a difference of 1.097 ms.
In both cases what is this duration? Real time or CPU time?
--
%!PS
297.6 420.9 translate 90 rotate 0 setgray gsave 0 1 1{pop 0 180 moveto 100
180 170 100 170 -10 curveto 180 -9 180 -9 190 -10 curveto 190 100 100 180
0 180 curveto fill 180 rotate}for grestore/Bookman-LightItalic findfont
240 scalefont setfont -151.536392 -63.7998886 moveto (bp)show showpage
---(end of broadcast)---
TIP 7: don't forget to increase your free space map settings
Re: [SQL] "SELECT IN" Still Broken in 7.4b
On Fri, Aug 22, 2003 at 08:50:15AM -0400, Tom Lane wrote:
> Bertrand Petit <[EMAIL PROTECTED]> writes:
> > On Wed, Aug 20, 2003 at 04:32:19PM -0400, Tom Lane wrote:
> >> against actual elapsed time (cf psql's \timing option) would tell.
>
> > What is measured by the \timing option?
>
> Elapsed time ... as seen by the client, of course.
Is there a way to obtain the CPU time consumed by a query,
including a breakdown between user- and kernel-land?
--
%!PS
297.6 420.9 translate 90 rotate 0 setgray gsave 0 1 1{pop 0 180 moveto 100
180 170 100 170 -10 curveto 180 -9 180 -9 190 -10 curveto 190 100 100 180
0 180 curveto fill 180 rotate}for grestore/Bookman-LightItalic findfont
240 scalefont setfont -151.536392 -63.7998886 moveto (bp)show showpage
---(end of broadcast)---
TIP 7: don't forget to increase your free space map settings
[SQL] Equality operators on NULL values
(in)Equality operators evaluates to NULL when any of their
arguments are NULL as summarized bellow.
NULL=NULL ==> NULL
1=NULL==> NULL
1=1 ==> t
1=2 ==> f
I have a case where I need to delete rows from table A for records
that are designated in table B. One of the joined colum can have NULL
values. Therefore I need an operator behaving as follows:
NULL=NULL ==> t
1=NULL==> NULL (or anything else as long as it is not t)
1=1 ==> t
1=2 ==> f
I replaced the use of the = operator with this expression:
nullif(tabA.col_with_nulls, tabB.col_with_nulls) IS NULL
It works correctly but the intent is not that clear.
Is there a stock replacement operator that would behave like
the second truth table?
Regards,
Bertrand.
--
%!PS
297.6 420.9 translate 90 rotate 0 setgray gsave 0 1 1{pop 0 180 moveto 100
180 170 100 170 -10 curveto 180 -9 180 -9 190 -10 curveto 190 100 100 180
0 180 curveto fill 180 rotate}for grestore/Bookman-LightItalic findfont
240 scalefont setfont -151.536392 -63.7998886 moveto (bp)show showpage
---(end of broadcast)---
TIP 7: don't forget to increase your free space map settings
Re: [SQL] Backup of multiple tables
On Tue, Sep 23, 2003 at 12:34:00AM +0200, Andreas Joseph Krogh wrote:
>
> I've never looked at the code-base of pgsql before, but I'll give it a try.
> Don't expect anything real soon tho.
If you acheive to add the selection of tables to dump, you may
at the same time try to add a similar option but working schemas. And,
yes, there's a beer waiting for you here too!
--
%!PS
297.6 420.9 translate 90 rotate 0 setgray gsave 0 1 1{pop 0 180 moveto 100
180 170 100 170 -10 curveto 180 -9 180 -9 190 -10 curveto 190 100 100 180
0 180 curveto fill 180 rotate}for grestore/Bookman-LightItalic findfont
240 scalefont setfont -151.536392 -63.7998886 moveto (bp)show showpage
---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster
[SQL] pg_class.relpages
Does the figures stored in pg_class.relpages include the pages
consumed by the toast tables linked to a normal table?
--
%!PS
297.6 420.9 translate 90 rotate 0 setgray gsave 0 1 1{pop 0 180 moveto 100
180 170 100 170 -10 curveto 180 -9 180 -9 190 -10 curveto 190 100 100 180
0 180 curveto fill 180 rotate}for grestore/Bookman-LightItalic findfont
240 scalefont setfont -151.536392 -63.7998886 moveto (bp)show showpage
---(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] pg_class.relpages
On Sat, Sep 27, 2003 at 08:26:16PM -0400, Bruce Momjian wrote:
> Bertrand Petit wrote:
> >
> > Does the figures stored in pg_class.relpages include the pages
> > consumed by the toast tables linked to a normal table?
>
> No. See the chapter on monitoring disk space for more information.
Okay. I eventually wrote the following query that lists tables
and indices sizes. A view similar to this query might my useful for
the general public if defined in the information schema.
SELECT pg_namespace.nspname AS schema,
COALESCE((SELECT class3.relname
FROM pg_class AS class3, pg_index
WHERE class1.oid=pg_index.indexrelid AND
class3.oid=pg_index.indrelid),
class1.relname) AS table,
CASE
WHEN class1.relkind='r' THEN NULL
ELSE class1.relname
END AS index,
(SELECT COALESCE(class1.relpages+SUM(class2.relpages),
class1.relpages) * 8
FROM pg_class AS class2
WHERE class2.oid IN (class1.reltoastrelid,
class1.reltoastidxid)) AS size
FROM pg_class AS class1, pg_namespace
WHERE class1.relnamespace = pg_namespace.oid AND
pg_namespace.nspname NOT IN ('pg_catalog',
'information_schema',
'pg_toast') AND
class1.relkind IN ('r', 'i')
ORDER BY class1.relnamespace ASC, class1.relname ASC,
class1.relkind DESC
--
%!PS
297.6 420.9 translate 90 rotate 0 setgray gsave 0 1 1{pop 0 180 moveto 100
180 170 100 170 -10 curveto 180 -9 180 -9 190 -10 curveto 190 100 100 180
0 180 curveto fill 180 rotate}for grestore/Bookman-LightItalic findfont
240 scalefont setfont -151.536392 -63.7998886 moveto (bp)show showpage
---(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] Ambiguous error message
On Fri, Jan 02, 2004 at 05:49:46PM +0100, Samuel Tardieu wrote:
>
> select texten, total
> from (select protocolid, count(*) as total) from ips where catid=1
^
+--- There
> group by protocolid order by protocolid) as c
> inner join protocols using (protocolid);
>
> gives the error message:
>
> ERROR: subquery in FROM must have an alias
> HINT: For example, FROM (SELECT ...) [AS] foo.
>
> Why isn't the "as c" considered as an alias?
That's maybe due to the highlighted spurious parenthesis.
--
%!PS
297.6 420.9 translate 90 rotate 0 setgray gsave 0 1 1{pop 0 180 moveto 100
180 170 100 170 -10 curveto 180 -9 180 -9 190 -10 curveto 190 100 100 180
0 180 curveto fill 180 rotate}for grestore/Bookman-LightItalic findfont
240 scalefont setfont -151.536392 -63.7998886 moveto (bp)show showpage
---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faqs/FAQ.html
