Re: eliminating records not in (select id ... so SLOW?

On Thu, 31 Jul 2008 14:59:29 -0700 (PDT)
Lennin Caro <lennin.caro <at> yahoo.com> wrote:

> > The box is a 2x dual core Xeon (below 2GHz) with 4Gb ram.
> > Default debian etch setup.

> you recently run vacuum ? 

The tables are pretty stable. I think no more than 20 records were
modified (update/insert/delete) during the whole history of the 2
tables.

autovacuum is running regularly.

The actual query running is:

begin;
 create index catalog_categoryitem_ItemsID_index on
   catalog_categoryitem using btree (ItemID);
 delete from catalog_categoryitem
   where ItemID not in (select ItemID from catalog_items);
commit;

That's what came back
Timing is on.
BEGIN
Time: 0.198 ms
CREATE INDEX
Time: 3987.991 ms

Re: hibernate nativequery and uuid

Re: hibernate nativequery and uuid

I have given it consideration, but haven't tried it as I have concluded 
that I would still have the same issue.  The problem occurs with the 
client attempting to apply a UUID value to filter the result set.  So I 
would still have a datatype mismatch if I were to use a view.

I am confident that I can get it to work if I were to remove Hibernate 
and make a direct JDBC call, as I have tested that approach previously.  
That is a fall back position.  But I would like if I can to keep to a 
single approach with my DB access for this application if I can.

The suggestion was appreciated though.

A.M. wrote:
>
> On Jul 31, 2008, at 5:57 PM, Andrew wrote:
>
>> I'm currently using JPA with Hibernate as my ORM and have been able 
>> to convince hibernate to play nicely with the Postgresql UUID.  Most 
>> of my queries have been in EJBQL using the JPA entity manager's 
>> createQuery.  However when I try to do a UNION, JPA only returned the 
>> results of the first query, and ignored the other UNION queries, 
>> which is in line with what I have read online, in that JPA does not 
>> support UNIONS.
>> So I'm currently attempting to go via a createNativeQuery call.  The 
>> keys on the target tables are PostgreSQL UUID data types.  When doing 
>> so I get the following error:
>>
>> org.postgresql.util.PSQLException: ERROR: operator does not exist: 
>> uuid = character varying
>>
>> The only relevant thing I have been able to find relating to it is 
>> http://archives.postgresql.org/pgsql-bugs/2007-12/msg00061.php which 
>> suggests adding a ::uuid cast to the parameter.
>>
>> However, when doing that, hibernate thinks that it is a named 
>> parameter and complains.
>> org.hibernate.QueryException: Not all named parameters have been set
>>
>> Hibernate's functionality to declare an escape character for a LIKE 
>> clause does not apply to this.  But in attempting to escape the 
>> colons, getting various other hibernate parsing errors, so my 
>> attempts down this path has not been of help.
>>
>> Has anyone else run into this issue and been able to resolve it?
>>
>> I'm using Postgresql 8.3.3 on Windows XP, Hibernate 3.2.6.GA and the 
>> Postgresql 8.3-603 JDBC4 driver.
>
> Could you use a view to hide the UNION?
>
> Cheers,
> M
>
> No virus found in this incoming message.
> Checked by AVG - http://www.avg.comVersion: 8.0.138 / Virus Database: 
> 270.5.8/1582 - Release Date: 7/30/2008 6:37 PM
>
>
>

--

-- 
Sent via pgsql-general mailing list (pgsql-general <at> postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: hibernate nativequery and uuid

Oh, I see what you mean.  Use EJBQL on a view.  That would probably 
work.  Have to get going, so will try that when I get back in several 
hours.  I'll let you know how I go.

Andrew wrote:
> I have given it consideration, but haven't tried it as I have 
> concluded that I would still have the same issue.  The problem occurs 
> with the client attempting to apply a UUID value to filter the result 
> set.  So I would still have a datatype mismatch if I were to use a view.
>
> I am confident that I can get it to work if I were to remove Hibernate 
> and make a direct JDBC call, as I have tested that approach 
> previously.  That is a fall back position.  But I would like if I can 
> to keep to a single approach with my DB access for this application if 
> I can.
>
> The suggestion was appreciated though.
>
>
>
> A.M. wrote:
>>
>> On Jul 31, 2008, at 5:57 PM, Andrew wrote:
>>
>>> I'm currently using JPA with Hibernate as my ORM and have been able 
>>> to convince hibernate to play nicely with the Postgresql UUID.  Most 
>>> of my queries have been in EJBQL using the JPA entity manager's 
>>> createQuery.  However when I try to do a UNION, JPA only returned 
>>> the results of the first query, and ignored the other UNION queries, 
>>> which is in line with what I have read online, in that JPA does not 
>>> support UNIONS.
>>> So I'm currently attempting to go via a createNativeQuery call.  The 
>>> keys on the target tables are PostgreSQL UUID data types.  When 
>>> doing so I get the following error:
>>>
>>> org.postgresql.util.PSQLException: ERROR: operator does not exist: 
>>> uuid = character varying
>>>
>>> The only relevant thing I have been able to find relating to it is 
>>> http://archives.postgresql.org/pgsql-bugs/2007-12/msg00061.php which 
>>> suggests adding a ::uuid cast to the parameter.
>>>
>>> However, when doing that, hibernate thinks that it is a named 
>>> parameter and complains.
>>> org.hibernate.QueryException: Not all named parameters have been set
>>>
>>> Hibernate's functionality to declare an escape character for a LIKE 
>>> clause does not apply to this.  But in attempting to escape the 
>>> colons, getting various other hibernate parsing errors, so my 
>>> attempts down this path has not been of help.
>>>
>>> Has anyone else run into this issue and been able to resolve it?
>>>
>>> I'm using Postgresql 8.3.3 on Windows XP, Hibernate 3.2.6.GA and the 
>>> Postgresql 8.3-603 JDBC4 driver.
>>
>> Could you use a view to hide the UNION?
>>
>> Cheers,
>> M
>>
>> No virus found in this incoming message.
>> Checked by AVG - http://www.avg.comVersion: 8.0.138 / Virus Database: 
>> 270.5.8/1582 - Release Date: 7/30/2008 6:37 PM
>>
>>
>>
>
>

--

-- 
Sent via pgsql-general mailing list (pgsql-general <at> postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: eliminating records not in (select id ... so SLOW?

Ivan Sergio Borgonovo <mail <at> webthatworks.it> writes:
> I'm doing something like:
> delete from table1 where id not in (select id from table2).
> table1 contains ~1M record table2 contains ~ 600K record and id is
> unique.

That's going to pretty much suck unless you've got work_mem set high
enough to allow a "hashed subplan" plan --- which is likely to require
tens of MB for this case, I don't recall exactly what the per-row
overhead is.  Experiment until EXPLAIN tells you it'll use a hashed
subplan.

BTW, don't bother with creating the index, it doesn't help for this.

			regards, tom lane

--

-- 
Sent via pgsql-general mailing list (pgsql-general <at> postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: Declaring constants in SQL

On Jul 30, 10:53 pm, richard.broer... <at> gmail.com ("Richard Broersma")
wrote:
> On Wed, Jul 30, 2008 at 1:16 PM, EXT-Rothermel, Peter M
> <Peter.M.Rother... <at> boeing.com> wrote:
> > Is there any means like (#define or DECLARE ) where I can write SQL like

(...)

> CREATE VIEW primary_colors_foos AS
>   SELECT * FROM foo
>    WHERE color = ANY( SELECT colorid
>                         FROM Colors
>                        WHERE colorname = ANY( 'red', 'blue', 'yellow' ));

Or even:
CREATE VIEW primary_color_foos AS
  SELECT foo.* FROM foo JOIN color c USING (color_id)
   WHERE c.colorname IN ('red', 'blue', 'yellow' );

If you have some constant values you need all over the place, you can
also resort to functions, which you can use much like CONSTANTs:

CREATE FUNCTION my_val()
  RETURNS integer AS
$BODY$
BEGIN

RETURN 21;

END;
$BODY$
  LANGUAGE 'plpgsql' IMMUTABLE;

Or, for the case at hand, an example in sql:
SELECT * FROM foo WHERE foo_id > myval();

CREATE FUNCTION my_colors()
  RETURNS text[] AS
$$ SELECT ARRAY['red','green','blue'] $$
  LANGUAGE 'sql' IMMUTABLE;

Use it like this:
SELECT * FROM foo WHERE color = ANY(myval());

Regards
Erwin

--

-- 
Sent via pgsql-general mailing list (pgsql-general <at> postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: Declaring constants in SQL

The last part got scrambled, should read like this:

(...)
Use it like this:
SELECT * FROM foo WHERE foo_id > myval();

Or, for the case at hand, an example in sql:

CREATE FUNCTION my_colors()
  RETURNS text[] AS
$$ SELECT ARRAY['red','green','blue'] $$
  LANGUAGE 'sql' IMMUTABLE;

Use it like this:
SELECT * FROM foo WHERE color = ANY(my_colors());

Regards
Erwin

--

-- 
Sent via pgsql-general mailing list (pgsql-general <at> postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: eliminating records not in (select id ... so SLOW?

On Thu, 31 Jul 2008 21:37:39 -0400
Tom Lane <tgl <at> sss.pgh.pa.us> wrote:

> Ivan Sergio Borgonovo <mail <at> webthatworks.it> writes:
> > I'm doing something like:
> > delete from table1 where id not in (select id from table2).
> > table1 contains ~1M record table2 contains ~ 600K record and id
> > is unique.

> That's going to pretty much suck unless you've got work_mem set
> high enough to allow a "hashed subplan" plan --- which is likely
> to require tens of MB for this case, I don't recall exactly what

Thanks.

> the per-row overhead is.  Experiment until EXPLAIN tells you it'll
> use a hashed subplan.

explain delete from catalog_categoryitem where ItemID not in (select
ItemID from catalog_items);

Well I reached 3Gb of work_mem and still I got:

"Seq Scan on catalog_categoryitem  (cost=31747.84..4019284477.13
rows=475532 width=6)"
"  Filter: (NOT (subplan))"
"  SubPlan"
"    ->  Materialize  (cost=31747.84..38509.51 rows=676167 width=8)"
"          ->  Seq Scan on catalog_items  (cost=0.00..31071.67
rows=676167 width=8)"

I've this too:
alter table catalog_items cluster on catalog_items_pkey;
should I drop it?

This is just a dev box. I loaded the 2 tables with 2 not coherent
set of data just to play with, before adding all the pk/fk I need.
I could just truncate the tables and reload them from coherent
sources.

But what if I *really* had to execute that query?
Any other magic I could play to speed it up?

thanks

-- 
Ivan Sergio Borgonovo
http://www.webthatworks.it

--

-- 
Sent via pgsql-general mailing list (pgsql-general <at> postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: Copy fails

Abraham, Danny wrote:
> I am loading a huge file using C, STDIN

Using C?

Have you written a C program using libpq to load some data, which it
reads from its stdin?

Or do you mean COPY FROM STDIN ?

Something else?

Perhaps if you provided a clearer and more complete explanation of your
problem you might get a more useful answer.

> The program fails immediately on "canceling statement due to statement
> timeout"

Do you have a statement timeout set in postgresql.conf ?

--
Craig Ringer

--

-- 
Sent via pgsql-general mailing list (pgsql-general <at> postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: Cursor Error

Bob Pawley wrote:
> Is it allowed to declare a cursor in this manner??
> 
> Declare
> procgraphic cursor for select p_id.p_id.process_id
> from p_id.p_id, processes_count
> where p_id.p_id.p_id_id = processes_count.p_id_id;

Using DECLARE instead of OPEN? Yes, but that won't somehow make a cursor
involving a join updatable. See:

http://www.postgresql.org/docs/8.3/interactive/plpgsql-cursors.html

DECLARE and OPEN do not have exactly the same meaning, as explained by
the above documentation. To use a cursor defined with DECLARE you must
use OPEN - see section 38.7.2.3 ("Opening a Bound Cursor") of the
documentation.

--
Craig Ringer

--

-- 
Sent via pgsql-general mailing list (pgsql-general <at> postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general