Re: [PERFORM] Index row requires 9324 bytes maximum size is 8191
not only that's slow, but limited as you can see. Use something like: http://gjsql.wordpress.com/2009/04/19/how-to-speed-up-index-on-bytea-text-etc/ instead. -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] Index row requires 9324 bytes maximum size is 8191
* solAris: Also, average time to search for a query in a table is taking about 15 seconds. I have done indexing but the time is not reducing. Is there any way to reduce the time to less than 1 sec ??? How are your queries structured? Do you just compare values? Do you perform range queries? Or something like WHERE col LIKE '%string%')? -- Florian Weimerfwei...@bfk.de BFK edv-consulting GmbH http://www.bfk.de/ Kriegsstraße 100 tel: +49-721-96201-1 D-76133 Karlsruhe fax: +49-721-96201-99 -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
[PERFORM] query memory consumption
Hey folks, We are looking to optimize the query I was talking about last week which is killing our system. We have explain and analyze which tell us about the cost of a query time-wise, but what does one use to determine (and trace / predict?) memory consumption? thanks, -Alan -- “Don't eat anything you've ever seen advertised on TV” - Michael Pollan, author of In Defense of Food -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] slow query : very simple delete, 100% cpu, nearly no disk activity
On Mon, Sep 21, 2009 at 10:50 AM, Vincent de Phily
vincent.deph...@mobile-devices.fr wrote:
On Friday 11 September 2009 23:55:09 Merlin Moncure wrote:
On Mon, Sep 7, 2009 at 5:05 AM, Vincent de Phily
vincent.deph...@mobile-devices.fr wrote:
Table public.message
Column | Type | Modifiers
---+-+---
--- id | integer | not null
default
nextval('message_id_seq'::regclass)
unitid | integer | not null
userid | integer |
refid | integer |
Indexes:
message_pkey PRIMARY KEY, btree (id)
message_unitid_fromto_status_idx btree (unitid, fromto, status)
message_userid_idx btree (userid)
Foreign-key constraints:
message_refid_fkey FOREIGN KEY (refid) REFERENCES message(id) ON
UPDATE CASCADE ON DELETE CASCADE
message_unitid_fkey FOREIGN KEY (unitid) REFERENCES units(id) ON
UPDATE CASCADE ON DELETE CASCADE
message_userid_fkey FOREIGN KEY (userid) REFERENCES users(id) ON
UPDATE CASCADE ON DELETE CASCADE
where is the index on refid?
It's
message_pkey PRIMARY KEY, btree (id)
because
(refid) REFERENCES message(id)
You are thinking about this backwards. Every time you delete a
message, the table has to be scanned for any messages that reference
the message being deleted because of the refid constraint (in order to
see if any deletions must be cascaded). PostgreSQL creates a backing
index for primary keys automatically but not foreign keys...so you
likely need to create an index on refid.
merlin
--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] How to post Performance Questions
Kevin Grittner wrote: Michael Glaesemann g...@seespotcode.net wrote: On Sep 14, 2009, at 16:55 , Josh Berkus wrote: Please read the following two documents before posting your performance query here: http://wiki.postgresql.org/wiki/Guide_to_reporting_problems http://wiki.postgresql.org/wiki/SlowQueryQuestions This will help other users to troubleshoot your problems far more rapidly. Can something similar be added to the footer of (at least) the performance list? Perhaps on this page?: http://www.postgresql.org/community/lists/ Done this part. (It'll take some time to propagate.) -- Alvaro Herrerahttp://www.CommandPrompt.com/ The PostgreSQL Company - Command Prompt, Inc. -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] query memory consumption
On Mon, Sep 21, 2009 at 10:47 AM, Alan McKay alan.mc...@gmail.com wrote: We are looking to optimize the query I was talking about last week which is killing our system. We have explain and analyze which tell us about the cost of a query time-wise, but what does one use to determine (and trace / predict?) memory consumption? I'm not sure what to suggest, other than the available operating system tools, but if you post EXPLAIN ANALYZE output we might be able to speculate better. Setting work_mem too high is a frequent cause of problems of this sort, I think. ...Robert -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] statement stats extra load?
Alan McKay wrote: Is there a rule of thumb for the extra load that will be put on a system when statement stats are turned on? And if so, where does that extra load go?Disk? CPU? RAM? As of 8.4.X the load isn't measurable. -- Bruce Momjian br...@momjian.ushttp://momjian.us EnterpriseDB http://enterprisedb.com + If your life is a hard drive, Christ can be your backup. + -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] Planner question - wrong row count estimation
On 9/19/09 5:08 PM, Michael Korbakov wrote: - Hash Join (cost=8.50..25.11 rows=1 width=28) (actual time=0.092..1.864 rows=560 loops=1) Hash Cond: (((partners_shares.year)::double precision = (shares.year)::double precision) AND ((partners_shares.month)::double precision = (shares.month)::double precision)) This appears to be where the estimates go wrong; Postgres may be assuming random correlation which isn't correct. My suggestion would be to try and create matching indexes on date_trunc(daily_domain_reports.date) and month year of monthly_shares_with_parents_materialized. -- Josh Berkus PostgreSQL Experts Inc. www.pgexperts.com -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] statement stats extra load?
And if so, where does that extra load go? Disk? CPU? RAM? As of 8.4.X the load isn't measurable. Thanks Bruce. What about 8.3 since that is our current production DB? -- “Don't eat anything you've ever seen advertised on TV” - Michael Pollan, author of In Defense of Food -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] statement stats extra load?
Alan McKay wrote: And if so, where does that extra load go? ? ?Disk? ?CPU? ?RAM? As of 8.4.X the load isn't measurable. Thanks Bruce. What about 8.3 since that is our current production DB? Same. All statsistics settings that are enabled by default have near-zero overhead. Is there a specific setting you are thinking of? -- Bruce Momjian br...@momjian.ushttp://momjian.us EnterpriseDB http://enterprisedb.com + If your life is a hard drive, Christ can be your backup. + -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
[PERFORM] session servers in ram
I'm looking at running session servers in ram. All the data is throw-away data, so my plan is to have a copy of the empty db on the hard drive ready to go, and have a script that just copies it into ram and starts the db there. We're currently IO write bound with fsync=off using a 15k5 seagate SAS drive, so I'm hoping that moving the db into /dev/shm will help quite a bit here. Does anybody any real world experience here or any words of sage advice before I go off and start testing this? -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] slow query : very simple delete, 100% cpu, nearly no disk activity
On Friday 11 September 2009 23:30:37 Robert Haas wrote: On Mon, Sep 7, 2009 at 5:05 AM, Vincent de Phily vincent.deph...@mobile-devices.fr wrote: On Monday 07 September 2009 03:25:23 Tom Lane wrote: 99% of the time, the reason a delete takes way longer than it seems like it should is trigger firing time. In particular, foreign key triggers where you don't have an index on the referencing column. Are there any foreign keys linking to this table? Yes, but they look fine to me (?). Only one FK references the table; it's an internal reference : (...) I would try EXPLAIN ANALYZE DELETE ... with a query that is modified so as to delete only a handful of rows. That will report the amount of time spent in triggers vs. the main query, which will help you assess whether your conclusion that the foreign keys are OK is correct. Good idea. I'll try that in a little while and report the result. -- Vincent de Phily Mobile Devices +33 (0) 666 301 306 +33 (0) 142 119 325 Warning This message (and any associated files) is intended only for the use of its intended recipient and may contain information that is confidential, subject to copyright or constitutes a trade secret. If you are not the intended recipient you are hereby notified that any dissemination, copying or distribution of this message, or files associated with this message, is strictly prohibited. If you have received this message in error, please notify us immediately by replying to the message and deleting it from your computer. Any views or opinions presented are solely those of the author vincent.deph...@mobile-devices.fr and do not necessarily represent those of the company. Although the company has taken reasonable precautions to ensure no viruses are present in this email, the company cannot accept responsibility for any loss or damage arising from the use of this email or attachments. -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] slow query : very simple delete, 100% cpu, nearly no disk activity
On Friday 11 September 2009 23:55:09 Merlin Moncure wrote:
On Mon, Sep 7, 2009 at 5:05 AM, Vincent de Phily
vincent.deph...@mobile-devices.fr wrote:
Table public.message
Column | Type | Modifiers
---+-+---
--- id | integer | not null
default
nextval('message_id_seq'::regclass)
unitid | integer | not null
userid | integer |
refid | integer |
Indexes:
message_pkey PRIMARY KEY, btree (id)
message_unitid_fromto_status_idx btree (unitid, fromto, status)
message_userid_idx btree (userid)
Foreign-key constraints:
message_refid_fkey FOREIGN KEY (refid) REFERENCES message(id) ON
UPDATE CASCADE ON DELETE CASCADE
message_unitid_fkey FOREIGN KEY (unitid) REFERENCES units(id) ON
UPDATE CASCADE ON DELETE CASCADE
message_userid_fkey FOREIGN KEY (userid) REFERENCES users(id) ON
UPDATE CASCADE ON DELETE CASCADE
where is the index on refid?
It's
message_pkey PRIMARY KEY, btree (id)
because
(refid) REFERENCES message(id)
--
Vincent de Phily
Mobile Devices
+33 (0) 666 301 306
+33 (0) 142 119 325
Warning
This message (and any associated files) is intended only for the use of its
intended recipient and may contain information that is confidential, subject
to copyright or constitutes a trade secret. If you are not the intended
recipient you are hereby notified that any dissemination, copying or
distribution of this message, or files associated with this message, is
strictly prohibited. If you have received this message in error, please
notify us immediately by replying to the message and deleting it from your
computer. Any views or opinions presented are solely those of the author
vincent.deph...@mobile-devices.fr and do not necessarily represent those of
the
company. Although the company has taken reasonable precautions to ensure no
viruses are present in this email, the company cannot accept responsibility
for any loss or damage arising from the use of this email or attachments.
--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] slow query : very simple delete, 100% cpu, nearly no disk activity
On Monday 21 September 2009 17:00:36 Merlin Moncure wrote:
On Mon, Sep 21, 2009 at 10:50 AM, Vincent de Phily
vincent.deph...@mobile-devices.fr wrote:
On Friday 11 September 2009 23:55:09 Merlin Moncure wrote:
On Mon, Sep 7, 2009 at 5:05 AM, Vincent de Phily
vincent.deph...@mobile-devices.fr wrote:
Table public.message
Column | Type |
Modifiers
---+-+
--- --- id | integer |
not null default
nextval('message_id_seq'::regclass)
unitid | integer | not null
userid | integer |
refid | integer |
Indexes:
message_pkey PRIMARY KEY, btree (id)
message_unitid_fromto_status_idx btree (unitid, fromto, status)
message_userid_idx btree (userid)
Foreign-key constraints:
message_refid_fkey FOREIGN KEY (refid) REFERENCES message(id) ON
UPDATE CASCADE ON DELETE CASCADE
message_unitid_fkey FOREIGN KEY (unitid) REFERENCES units(id) ON
UPDATE CASCADE ON DELETE CASCADE
message_userid_fkey FOREIGN KEY (userid) REFERENCES users(id) ON
UPDATE CASCADE ON DELETE CASCADE
where is the index on refid?
It's
message_pkey PRIMARY KEY, btree (id)
because
(refid) REFERENCES message(id)
You are thinking about this backwards. Every time you delete a
message, the table has to be scanned for any messages that reference
the message being deleted because of the refid constraint (in order to
see if any deletions must be cascaded). PostgreSQL creates a backing
index for primary keys automatically but not foreign keys...so you
likely need to create an index on refid.
D'Oh ! Sounds obvious now that you mention it, and it's a very good
explanation of the delete's slowness.
I'll test this tonight or tomorrow.
--
Vincent de Phily
Mobile Devices
+33 (0) 666 301 306
+33 (0) 142 119 325
Warning
This message (and any associated files) is intended only for the use of its
intended recipient and may contain information that is confidential, subject
to copyright or constitutes a trade secret. If you are not the intended
recipient you are hereby notified that any dissemination, copying or
distribution of this message, or files associated with this message, is
strictly prohibited. If you have received this message in error, please
notify us immediately by replying to the message and deleting it from your
computer. Any views or opinions presented are solely those of the author
vincent.deph...@mobile-devices.fr and do not necessarily represent those of
the
company. Although the company has taken reasonable precautions to ensure no
viruses are present in this email, the company cannot accept responsibility
for any loss or damage arising from the use of this email or attachments.
--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] Slow select times on select with xpath
CREATE INDEX CONCURRENTLY idx_serializedxml ON object (serialized_object ASC NULLS LAST); yields the error: ERROR: data type xml has no default operator class for access method btree The same error occurs when I try to use the other access methods as well. On Thu, Sep 3, 2009 at 4:06 PM, astro77astro_co...@yahoo.com wrote: I was receiving an error that an XML field does not support the various indexes available in postgresql. Please post what happens when you try. -- View this message in context: http://www.nabble.com/Slow-select-times-on-select-with-xpath-tp25259351p25530433.html Sent from the PostgreSQL - performance mailing list archive at Nabble.com. -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] Slow select times on select with xpath
Thanks Kevin. I thought about using tsearch2 but I need to be able to select exact values on other numerical queries and cannot use contains queries. It's got to be fast so I cannot have lots of records returned and have to do secondary processing on the xml for the records which contain the exact value I'm looking for. This is one of the reasons I moved from using Lucene for searching. I hope this makes sense. Kevin Grittner wrote: wrote: I would try to minimize how many XML values it had to read, parse, and search. The best approach that comes to mind would be to use tsearch2 techniques (with a GIN or GiST index on the tsvector) to identify which rows contain 'fdc3da1f-060f-4c34-9c30-d9334d9272ae', and use AND to combine that with your xpath search. -Kevin -- View this message in context: http://www.nabble.com/Slow-select-times-on-select-with-xpath-tp25259351p25530439.html Sent from the PostgreSQL - performance mailing list archive at Nabble.com. -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] Slow select times on select with xpath
As a follow-up, when I try to create the index like this...
CREATE INDEX concurrently
idx_object_nodeid2
ON
object
USING
btree(
xpath('/a:root/a:Identification/b:ObjectId/text()', serialized_object,
ARRAY
[
ARRAY['a', 'http://schemas.datacontract.org/2004/07/Objects'],
ARRAY['b', 'http://schemas.datacontract.org/2004/07/Security']
])
) ;
The index begins to build but fails after about 90 seconds with this error:
ERROR: could not identify a comparison function for type xml
SQL state: 42883
Robert Haas wrote:
On Thu, Sep 3, 2009 at 4:06 PM, astro77astro_co...@yahoo.com wrote:
I was receiving an error that an XML field does not support the various
indexes available in postgresql.
Please post what happens when you try.
Is there an example of how to do this
properly?
Not sure.
...Robert
--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
--
View this message in context:
http://www.nabble.com/Slow-select-times-on-select-with-xpath-tp25259351p25530455.html
Sent from the PostgreSQL - performance mailing list archive at Nabble.com.
--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] Slow select times on select with xpath
astro77 wrote: Thanks Kevin. I thought about using tsearch2 but I need to be able to select exact values on other numerical queries and cannot use contains queries. You might be able to make use of a custom parser for tsearch2 that creates something like a single word for xml fragments like whatever1/whatever which would let you quickly find exact matches for those words/phrases. It's got to be fast so I cannot have lots of records returned and have to do secondary processing on the xml for the records which contain the exact value I'm looking for. This is one of the reasons I moved from using Lucene for searching. I hope this makes sense. Kevin Grittner wrote: wrote: I would try to minimize how many XML values it had to read, parse, and search. The best approach that comes to mind would be to use tsearch2 techniques (with a GIN or GiST index on the tsvector) to identify which rows contain 'fdc3da1f-060f-4c34-9c30-d9334d9272ae', and use AND to combine that with your xpath search. -Kevin -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
