Hi Ken, I used to have the same problem. In my computer, using date_part in WHERE clause will always slow down the system when the table has plenty of rows. My suggestion is try to change the query to: ... WHERE dxdate >= '2001-01-01'; I hope this helps. Regards, bungsu ----- Original Message ----- From: Ken Hill To: Postgres Help Sent: Tuesday, February 14, 2006 8:48 AM Subject: [SQL] Slow update SQL I'm experiencing a very slow query. The table contains 611,564 rows of data. I vaccumed the table: VACUUM ANALYZE ncccr10; SELECT count(*) FROM ncccr10; count -------- 611564 (1 row) When I try to analyze the query plan with: EXPLAIN ANALYZE UPDATE ncccr10 SET key = facilityno||'-'|| lastname||'-'|| sex||'-'|| ssno||'-'|| birthdate||'-'|| primarysit||'-'|| dxdate||'-'|| morphology3 WHERE date_part('year',dxdate) > '2000'; The query just never finishes (even 1 hour later). The colum key100 is indexed, and I'm setting the value of this column from other columns. Why is this so slow?

hi all....
this is akhilesh from india.i just want to ask one thing regarding grant operation??
i am using pgsql 8.0 on ubuntu 5.10 linux.
just tell me the procedure that how can i grant all permissions for a database to any of the other user???
i am using :-
:->> grant all privileges on database <db_name> to <user_name>;
GRANT
:->> grant all ON DATABASE <db_name> to <user_name>;
GRANT

and the query executes succesfully.
but at the other user end, she is not able to access the database given in the above query.
here i have to grant permissions to that user individually for each and every table by using:
:->> grant ALL ON <tab_name> to <user_name>;
GRANT

and all the permissions are granted to that user for that particular table.

i want same results for the entire database.................
plz help me it's urgent

--
Thanks & Regards,
Akhilesh
DAV Institute of Management
Faridabad(Haryana)
GSM+919891606064)
          (+911744293789)

"FAILURES CAN BE FORGIVEN BUT AIMING LOW IS A CRIME"

You can try this too:

...
WHERE EXTRACT(YEAR FROM dxdate::Date) > 2000

Mauricio Fernández A.
Ingeniero de Sistemas
Universidad Autónoma de Manizales (Colombia)

-----Mensaje original-----
De: pgsql-sql-owner <at> postgresql.org
[mailto:pgsql-sql-owner <at> postgresql.org]En nombre de Bungsuputra Linan
Enviado el: lunes, 27 febrero, 2006 3:42
Para: ken <at> scottshill.com; Postgres Help
Asunto: Re: [SQL] Slow update SQL


Hi Ken,

I used to have the same problem. In my computer, using date_part in WHERE
clause will always slow down the system when the table has plenty of rows.

My suggestion is try to change the query to:
... WHERE dxdate >= '2001-01-01';

I hope this helps.

Regards,
bungsu

----- Original Message -----
From: Ken Hill
To: Postgres Help
Sent: Tuesday, February 14, 2006 8:48 AM
Subject: [SQL] Slow update SQL


I'm experiencing a very slow query. The table contains 611,564 rows of data.
I vaccumed the table:

VACUUM ANALYZE ncccr10;

SELECT count(*) FROM ncccr10;
count
--------
611564
(1 row)

When I try to analyze the query plan with:

EXPLAIN ANALYZE
UPDATE ncccr10
SET key = facilityno||'-'||
lastname||'-'||
sex||'-'||
ssno||'-'||
birthdate||'-'||
primarysit||'-'||
dxdate||'-'||
morphology3
WHERE date_part('year',dxdate) > '2000';

The query just never finishes (even 1 hour later). The colum key100 is
indexed, and I'm setting the value of this
column from other columns. Why is this so slow?


---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend

-----Mensaje original-----
De: pgsql-sql-owner <at> postgresql.org [mailto:pgsql-sql-owner <at> postgresql.org]En nombre de Daniel Joo
Enviado el: viernes, 24 febrero, 2006 2:45
Para: pgsql-sql <at> postgresql.org
Asunto: [SQL] how to add primary key to existing table with multiple primary keys

Hi there,

 

I am trying to add another primary key to an existing table with two other primary keys.  I got the following error when I tried this command:

 

alter table extprobe2tissue ADD primary key (expid);

ERROR:  multiple primary keys for table "extprobe2tissue" are not allowed

 

The details of the extprobe2tissue table is:

 

\d extprobe2tissue;

          Table "public.extprobe2tissue"

   Column    |          Type          | Modifiers

-------------+------------------------+-----------

 probeid     | integer                | not null

 tissueid    | integer                | not null

 value       | double precision       | not null

 entrydate   | date                   | not null

 description | character varying(200) |

 qval        | double precision       |

 expid       | integer                | not null

Indexes:

    "extprobe2tissue_pkey" PRIMARY KEY, btree (probeid, tissueid)

    "extprobe2tissue_probeid" btree (probeid)

    "extprobe2tissue_tissueid" btree (tissueid)

Foreign-key constraints:

    "extprobe2tissue_tissueid_fkey" FOREIGN KEY (tissueid) REFERENCES tissue(tissueid)

    "extprobe2tissue_probeid_fkey" FOREIGN KEY (probeid) REFERENCES extprobe(probeid)

 

Any suggestion would be much appreciated.

 

Thanks!