headers
Jenny | 6 Dec 2005 09:38
Picon
Favicon

need help

I'm running PostgreSQL 8.0.3 on i686-pc-linux-gnu (Fedora Core 2). I've been
dealing with Psql for over than 2 years now, but I've never had this case
before.

I have a table that has about 20 rows in it.

       Table "public.s_apotik"
    Column         |          Type                |     Modifiers
-------------------+------------------------------+------------------
obat_id            | character varying(10)        | not null
stock              | numeric                      | not null
s_min              | numeric                      | not null
s_jual             | numeric                      | 
s_r_jual           | numeric                      | 
s_order            | numeric                      | 
s_r_order          | numeric                      | 
s_bs               | numeric                      | 
last_receive       | timestamp without time zone  |
Indexes:
   "s_apotik_pkey" PRIMARY KEY, btree(obat_id)

When I try to UPDATE one of the row, nothing happens for a very long time.
First, I run it on PgAdminIII, I can see the miliseconds are growing as I
waited. Then I stop the query, because the time needed for it is unbelievably
wrong.

Then I try to run the query from the psql shell. For example, the table has
obat_id : A, B, C, D.
db=# UPDATE s_apotik SET stock = 100 WHERE obat_id='A';
(.... nothing happens.. I press the Ctrl-C to stop it. This is what comes out
(Continue reading)

Permalink | Reply | 1 comment |
headers
Tino Wildenhain | 6 Dec 2005 09:54
Picon

Re: need help

Jenny schrieb:
> I'm running PostgreSQL 8.0.3 on i686-pc-linux-gnu (Fedora Core 2). I've been
> dealing with Psql for over than 2 years now, but I've never had this case
> before.
> 
> I have a table that has about 20 rows in it.
> 
>        Table "public.s_apotik"
>     Column         |          Type                |     Modifiers
> -------------------+------------------------------+------------------
> obat_id            | character varying(10)        | not null
> stock              | numeric                      | not null
> s_min              | numeric                      | not null
> s_jual             | numeric                      | 
> s_r_jual           | numeric                      | 
> s_order            | numeric                      | 
> s_r_order          | numeric                      | 
> s_bs               | numeric                      | 
> last_receive       | timestamp without time zone  |
> Indexes:
>    "s_apotik_pkey" PRIMARY KEY, btree(obat_id)
>    
> When I try to UPDATE one of the row, nothing happens for a very long time.
> First, I run it on PgAdminIII, I can see the miliseconds are growing as I
> waited. Then I stop the query, because the time needed for it is unbelievably
> wrong.
> 
> Then I try to run the query from the psql shell. For example, the table has
> obat_id : A, B, C, D.
> db=# UPDATE s_apotik SET stock = 100 WHERE obat_id='A';
(Continue reading)

Permalink | Reply | 1 comment |
headers
Jenny | 6 Dec 2005 10:41
Picon
Favicon

need help (not anymore)

I run the VACUUM as you suggested, but still no response from the server. So, I
decided to DROP the database. I got a message that the database is being used.
I closed every application that accessing it. But, the message remains.

I checked the server processes (ps -ax). There were lots of  'UPDATE is waiting
...' on the list. I killed them all. I backuped current database and DROP the
database, restore to the backup file I just made. 

Don't really know why this happened, but thankfully now, everything's normal.
Thank you, guys.

Regards,
Jenny Tania

		
__________________________________________ 
Yahoo! DSL – Something to write home about. 
Just $16.99/mo. or less. 
dsl.yahoo.com 

---------------------------(end of broadcast)---------------------------
TIP 2: Don't 'kill -9' the postmaster
Permalink | Reply | 0 comments |
headers
Gregory S. Williamson | 15 Dec 2005 01:23

RE: Is my query planner failing me, or vice versa?


Forgive the cross-posting, but I found myself wondering if might not be some way future way of telling the
planner that a given table (column ?) has a high likelyhood of being TOASTed. Similar to the
random_page_cost in spirit. We've got a lot of indexed data that is spatial and have some table where no
data is toasted (road segments) and others where evrything is.

An idle suggestion from one who knows that he is meddling with ;-}

Greg Williamson
DBA
GlobeXplorer LLC

> -----Original Message-----
> From: postgis-users-bounces <at> postgis.refractions.net
> [mailto:postgis-users-bounces <at> postgis.refractions.net]On Behalf Of
> Jessica M Salmon
> Sent: Wednesday, December 14, 2005 9:09 AM
> To: PostGIS Users Discussion
> Subject: Re: [postgis-users] Is my query planner failing me, or vice versa?
> 
> Thanks, Marcus, for explaining.
> 
> And thanks, Robert, for asking that question about adjusting page size.
> 
> My tuples are definitely toasted (some of my geometries are 30X too big for
> a single page!), so I'm glad I'm aware of the TOAST tables now. I suppose
> there's not much to be done about it, but it's good to know.
> 
> Thanks everyone for such an array of insightful help.
>
(Continue reading)

Permalink | Reply | 752 comments |
headers
Markus Schaber | 15 Dec 2005 12:03
Favicon

Re: Is my query planner failing me, or vice versa?

Hi, Gregory,

Gregory S. Williamson wrote:
> Forgive the cross-posting, but I found myself wondering if might not
> be some way future way of telling the planner that a given table
> (column ?) has a high likelyhood of being TOASTed. Similar to the
> random_page_cost in spirit. We've got a lot of indexed data that is
> spatial and have some table where no data is toasted (road segments)
> and others where evrything is.

I'd personally put this into ANALYZE, it already collects statistics, so
it could also calculate TOASTing likelyhood and average TOASTed size.

Maybe that 8.X PostgreSQL already does this, I'm a bit lagging :-)

Markus
Permalink | Reply | 751 comments |
headers
Gregory S. Williamson | 15 Dec 2005 13:03

RE: [PERFORM] Is my query planner failing me, or vice versa?

Well, what does the random_page_cost do internally ?

I don't think I'd expect postgres to be able to *do* anything in particular, any more than I would expect it to
"do" something about slow disk I/O or having limited cache. But it might be useful to the EXPLAIN ANALYZE in
estimating costs of retrieving such data. 

Admittedly, this is not as clear as wanting a sequential scan in preference to indexed reads when there are
either very few rows or a huge number, but it strikes me as useful to me the DBA to have this factoid thrust in
front of me when considering why a given query is slower than I might like. Perhaps an added time based on
this factor and the random_page_cost value, since lots of TOAST data and a high access time would indicate
to my (ignorant!) mind that retrieval would be slower, especially over large data sets.

Forgive my ignorance ... obviously I am but a humble user. grin.

G

-----Original Message-----
From:	Tom Lane [mailto:tgl <at> sss.pgh.pa.us]
Sent:	Wed 12/14/2005 9:36 PM
To:	Gregory S. Williamson
Cc:	pgsql-performance <at> postgresql.org; PostGIS Users Discussion
Subject:	Re: [PERFORM] [postgis-users] Is my query planner failing me, or vice versa? 
"Gregory S. Williamson" <gsw <at> globexplorer.com> writes:
> Forgive the cross-posting, but I found myself wondering if might not
> be some way future way of telling the planner that a given table
> (column ?) has a high likelyhood of being TOASTed.

What would you expect the planner to do with the information, exactly?

We could certainly cause ANALYZE to record some estimate of this, but
(Continue reading)

Permalink | Reply | 752 comments |
headers
Mark Cave-Ayland | 15 Dec 2005 15:20
Picon

RE: [PERFORM] Is my query planner failing me, or vice versa?

> -----Original Message-----
> From:	Tom Lane [mailto:tgl <at> sss.pgh.pa.us]
> Sent:	Wed 12/14/2005 9:36 PM
> To:	Gregory S. Williamson
> Cc:	pgsql-performance <at> postgresql.org; PostGIS Users Discussion
> Subject:	Re: [PERFORM] [postgis-users] Is my query planner failing
me,
> or vice versa?
> "Gregory S. Williamson" <gsw <at> globexplorer.com> writes:
> > Forgive the cross-posting, but I found myself wondering if might not
> > be some way future way of telling the planner that a given table
> > (column ?) has a high likelyhood of being TOASTed.
> 
> What would you expect the planner to do with the information, exactly?
> 
> We could certainly cause ANALYZE to record some estimate of this, but
> I'm not too clear on what happens after that...
> 
> 			regards, tom lane
>
>
> -----Original Message-----
> From: postgis-users-bounces <at> postgis.refractions.net [mailto:postgis-users-
> bounces <at> postgis.refractions.net] On Behalf Of Gregory S. Williamson
> Sent: 15 December 2005 12:03
> To: Tom Lane
> Cc: pgsql-performance <at> postgresql.org; PostGIS Users Discussion
> Subject: RE: [PERFORM] [postgis-users] Is my query planner failing me,or
> vice versa?
>
(Continue reading)

Permalink | Reply | 751 comments |
headers
Alban Medici (NetCentrex | 21 Dec 2005 12:10

Re: [PERFORM] need help


Try to execute your query (in psql) with prefixing by EXPLAIN ANALYZE and
send us the result
 db=# EXPLAIN ANALYZE UPDATE s_apotik SET stock = 100 WHERE obat_id='A';

regards

-----Original Message-----
From: pgsql-performance-owner <at> postgresql.org
[mailto:pgsql-performance-owner <at> postgresql.org] On Behalf Of Tino Wildenhain
Sent: mardi 6 décembre 2005 09:55
To: Jenny
Cc: pgsql-general <at> postgresql.org; pgsql-sql <at> postgresql.org;
pgsql-performance <at> postgresql.org
Subject: Re: [PERFORM] [GENERAL] need help

Jenny schrieb:
> I'm running PostgreSQL 8.0.3 on i686-pc-linux-gnu (Fedora Core 2). 
> I've been dealing with Psql for over than 2 years now, but I've never 
> had this case before.
> 
> I have a table that has about 20 rows in it.
> 
>        Table "public.s_apotik"
>     Column         |          Type                |     Modifiers
> -------------------+------------------------------+------------------
> obat_id            | character varying(10)        | not null
> stock              | numeric                      | not null
> s_min              | numeric                      | not null
> s_jual             | numeric                      |
(Continue reading)

Permalink | Reply | 0 comments |

Gmane