Re: [PERFORM] count * performance issue
A Dimarts 11 Març 2008 04:11, Scott Marlowe va escriure: On Mon, Mar 10, 2008 at 7:57 PM, Robins Tharakan [EMAIL PROTECTED] wrote: Hi, I have been reading this conversation for a few days now and I just wanted to ask this. From the release notes, one of the new additions in 8.3 is (Allow col IS NULL to use an index (Teodor)). Sorry, if I am missing something here, but shouldn't something like this allow us to get a (fast) accurate count ? SELECT COUNT(*) from table WHERE indexed_field IS NULL + SELECT COUNT(*) from table WHERE indexed_field IS NOT NULL It really depends on the distribution of the null / not nulls in the table. If it's 50/50 there's no advantage to using the index, as you still have to check visibility info in the table itself. OTOH, if NULL (or converserly not null) are rare, then yes, the index can help. I.e. if 1% of the tuples are null, the select count(*) from table where field is null can use the index efficiently. But you'll get a sequential scan with the NOT NULL case which will end up taking more time. (Seq Scan + Index Scan Seq Scan) -- Albert Cervera Areny Dept. Informàtica Sedifa, S.L. Av. Can Bordoll, 149 08202 - Sabadell (Barcelona) Tel. 93 715 51 11 Fax. 93 715 51 12 AVISO LEGAL La presente comunicación y sus anexos tiene como destinatario la persona a la que va dirigida, por lo que si usted lo recibe por error debe notificarlo al remitente y eliminarlo de su sistema, no pudiendo utilizarlo, total o parcialmente, para ningún fin. Su contenido puede tener información confidencial o protegida legalmente y únicamente expresa la opinión del remitente. El uso del correo electrónico vía Internet no permite asegurarni la confidencialidad de los mensajes nisucorrecta recepción. Enel caso de que el destinatario no consintiera la utilización del correo electrónico, deberá ponerlo en nuestro conocimiento inmediatamente. ... DISCLAIMER . This message and its attachments are intended exclusively for the named addressee. If you receive this message in error, please immediately delete it from your system and notify the sender. You may not use this message or any part of it for any purpose. The message may contain information that is confidential or protected by law, and any opinions expressed are those of the individualsender. Internet e-mail guarantees neither the confidentiality nor the proper receipt of the message sent. If the addressee of this message does not consent to the use of internete-mail,pleaseinform usinmmediately. -- 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] Very slow (2 tuples/second) sequential scan afterbulk insert; speed returns to ~500 tuples/second after commit
Simon Riggs wrote: On Mon, 2008-03-10 at 11:01 +, Heikki Linnakangas wrote: According to oprofile, all the time is spent in TransactionIdIsInProgress. I recently submitted a patch to optimise this. Your comments would be welcome on the patch. You mean this one: http://archives.postgresql.org/pgsql-patches/2008-02/msg8.php ? Unfortunately that patch won't help in this case. -- Heikki Linnakangas EnterpriseDB http://www.enterprisedb.com -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
[PERFORM] how many index can have????
I have one table with 30 fields, i have more then 60 million records, if i use more no of indexes will it affect the insertion performance? and select performance? Shall i have more then one partial index for same field, -- With Best Regards, Petchimuthulingam S
Re: [PERFORM] count * performance issue
In response to Robins Tharakan [EMAIL PROTECTED]: Hi, I have been reading this conversation for a few days now and I just wanted to ask this. From the release notes, one of the new additions in 8.3 is (Allow col IS NULL to use an index (Teodor)). Sorry, if I am missing something here, but shouldn't something like this allow us to get a (fast) accurate count ? SELECT COUNT(*) from table WHERE indexed_field IS NULL + SELECT COUNT(*) from table WHERE indexed_field IS NOT NULL For certain, qualified definitions of fast, sure. -- Bill Moran Collaborative Fusion Inc. http://people.collaborativefusion.com/~wmoran/ [EMAIL PROTECTED] Phone: 412-422-3463x4023 IMPORTANT: This message contains confidential information and is intended only for the individual named. If the reader of this message is not an intended recipient (or the individual responsible for the delivery of this message to an intended recipient), please be advised that any re-use, dissemination, distribution or copying of this message is prohibited. Please notify the sender immediately by e-mail if you have received this e-mail by mistake and delete this e-mail from your system. E-mail transmission cannot be guaranteed to be secure or error-free as information could be intercepted, corrupted, lost, destroyed, arrive late or incomplete, or contain viruses. The sender therefore does not accept liability for any errors or omissions in the contents of this message, which arise as a result of e-mail transmission. -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
[PERFORM] list user created triggers
how to find trigger names in my database ? using psql 7.4 the following query shows system triggers, i want only to list the triggers created by me select relname, tgname, tgtype, proname, prosrc, tgisconstraint, tgconstrname, tgconstrrelid, tgdeferrable, tginitdeferred, tgnargs, tgattr, tgargs from (pg_trigger join pg_class on tgrelid=pg_class.oid) join pg_proc on (tgfoid=pg_proc.oid); -- 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 many index can have????
On 11-Mar-08, at 8:28 AM, petchimuthu lingam wrote: I have one table with 30 fields, i have more then 60 million records, if i use more no of indexes will it affect the insertion performance? and select performance? Yes, and yes, but without more information about what you are trying to do, those answers are useless. Shall i have more then one partial index for same field, -- With Best Regards, Petchimuthulingam S -- 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] count * performance issue
On Tue, 11 Mar 2008, Bill Moran wrote: In response to Robins Tharakan [EMAIL PROTECTED]: Sorry, if I am missing something here, but shouldn't something like this allow us to get a (fast) accurate count ? SELECT COUNT(*) from table WHERE indexed_field IS NULL + SELECT COUNT(*) from table WHERE indexed_field IS NOT NULL For certain, qualified definitions of fast, sure. And certain, qualified definitions of accurate as well. Race condition? Matthew -- Television is a medium because it is neither rare nor well done. -- Fred Friendly -- 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] count * performance issue
Hi, Matthew wrote: On Tue, 11 Mar 2008, Bill Moran wrote: In response to Robins Tharakan [EMAIL PROTECTED]: Sorry, if I am missing something here, but shouldn't something like this allow us to get a (fast) accurate count ? SELECT COUNT(*) from table WHERE indexed_field IS NULL + SELECT COUNT(*) from table WHERE indexed_field IS NOT NULL For certain, qualified definitions of fast, sure. And certain, qualified definitions of accurate as well. Race condition? You mean in a three-state-logic? null, not null and something different? ;-) Tino -- 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] count * performance issue
Matthew wrote: No, actually I was referring to a race condition. So, you find the count of rows with IS NULL, then someone changes a row, then you find the count of rows with IS NOT NULL. Add the two together, and there may be rows that were counted twice, or not at all. Not a problem if you use a serializable transaction, or if you do SELECT COUNT(*) from table WHERE indexed_field IS NULL UNION ALL SELECT COUNT(*) from table WHERE indexed_field IS NOT NULL as one statement. However, this makes no sense whatsoever. As both index scans (assuming the planner even chooses an index scan for them, which seems highly unlikely) still have to visit each tuple in the heap. It's always going to be slower than a single SELECT COUNT(*) FROM table with a seq scan. -- Heikki Linnakangas EnterpriseDB http://www.enterprisedb.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] count * performance issue
On Tue, Mar 11, 2008 at 02:19:09PM +, Matthew wrote: of rows with IS NULL, then someone changes a row, then you find the count of rows with IS NOT NULL. Add the two together, and there may be rows that were counted twice, or not at all. Only if you count in READ COMMITTED. A -- 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] Joins and DELETE FROM
Thank you for your post. I finally spent some quality time with the query planner section in the docs' server config chapter. Very instructive, even considering that most of it went over my head! On Sat, Mar 8, 2008 at 4:08 PM, Tom Lane [EMAIL PROTECTED] wrote: ...have you got effective_cache_size set to something that's realistic for your machine? I guess not. It was the default value (128MB) on a machine with 4GB of RAM. It's not a dedicated server, though, so I'll set it to 1G for now. But before doing so I need a clarification. The docs state that this parameter is used only for cost estimation, and has no effect on actual memory allocations. I imagine that if other memory-related settings are not somehow in line with it, it could lead to estimates that are out of touch with reality. If this is correct what other memory-related parameters do I need to adjust to ensure that both the planner's estimates and the actual execution agree and fit well with the available memory? One problem with this test is that your smaller tables probably fit in memory whereas the big ones may not, so it's not a given that your test accurately reflects how the real query will go down. That's a very helpful reminder. Thanks. Kynn
Re: [PERFORM] how many index can have????
petchimuthu lingam wrote: I have one table with 30 fields, i have more then 60 million records, if i use more no of indexes will it affect the insertion performance? and select performance? Maintaining an index has a cost. That means that every time a record covered by an index is added, deleted, or modified the index must be updated, slowing down the insert/delete*/update operation. On the other hand, an index can dramatically speed up a query if: - The query only requires a small proportion of the rows in the table; and - The index matches the conditions in the query An index won't help with most operations that affect most of the table anyway; in fact, it'll generally be slower than a sequential scan. Maybe you should tell the people here a little more about your problem. What queries are you running? What's your schema? What indexes do you have? What is the EXPLAIN or EXPLAIN ANALYZE output from running your queries? There's plenty of advice on the net for using indexes in postgresql effectively, so I suggest you do some reading. I'm no expert, but from my reading and the recent work I've been doing I've found some crucial points to be: - Adding an index is a trade-off between the cost of maintaining the index and the benefits the index provides for queries. Don't blindly add indexes; use EXPLAIN ANALYZE on your queries and find out what they actually need and what they do. - Test queries with and without indexes ( you can set options to the query planner to control which methods it uses ) and see what difference they make. Don't blindly add indexes. - Prefer to make indexes on highly differentiated data. If most of the data is the same or split between just a couple of values an index might not help much. Test and find out. - If the data is mostly one value, and you want to make searching for other values faster, consider a partial index. For example, if you have a boolean column is_archived that's set to 't' in 99% of rows, but you regularly run queries that are restricted to the 1% of rows with is_archived = 'f' you will benefit from a partial index on is_archived that's limited to WHERE (NOT is_archived) . See the postgresql documentation on partial indexes. - Partial indexes don't have to use the indexed value(s) for the restriction clause, they can use other columns. For example: CREATE INDEX some_partial_index ON tablename(customer_created_date) WHERE (NOT customer_is_retired); ... will make queries like: SELECT customer_created_date DATE '2007-01-01' wWHERE NOT customer_is_retired; ... much faster then they would've been without the partial index if the majority of customers have customer_is_retired set to 't'. - A WHERE clause needs to exactly match the restrictions on a partial index to use that index. I'm pretty sure the data types must be exactly the same and so must the order of the checks. - You can use multi-column indexes for single-column filters under some circumstances, but it's slow. Shall i have more then one partial index for same field, Maybe, it depends on your query and what the distribution of the data in the field is like. * OK, so PostgreSQL can cheat with MVCC on deletes, but that has its own costs. -- Craig Ringer -- 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] list user created triggers
thanks... after this query also it is showing default triggers ( am very much worried that how the system triggers are created using my username ok i posted this in the group you specified.. On Tue, Mar 11, 2008 at 8:03 PM, Sergey Benner [EMAIL PROTECTED] wrote: Again :) Try this query. select usename,relname, tgname, tgtype, proname, prosrc, tgisconstraint, tgconstrname, tgconstrrelid, tgdeferrable, tginitdeferred, tgnargs, tgattr, tgargs from (pg_trigger join pg_class c on tgrelid=c.oid ) join pg_proc on (tgfoid=pg_proc.oid) join pg_user pu on c.relowner=pu.usesysid where pu.usename='YOURUSERNAME'; Please post such questions to the [EMAIL PROTECTED] list. Cheers, Sergey
Re: [PERFORM] list user created triggers
select usename,relname, tgname, tgtype, proname, prosrc, tgisconstraint, tgconstrname, tgconstrrelid, tgdeferrable, tginitdeferred, tgnargs, tgattr, tgargs from (pg_trigger join pg_class c on tgrelid=c.oid ) join pg_proc on (tgfoid=pg_proc.oid) join pg_user pu on c.relowner=pu.usesysid where pu.usename='YOURUSERNAME'; yes i got the answer by this query.. select relname, tgname, tgtype, proname, prosrc, tgisconstraint, tgconstrname, tgconstrrelid, tgdeferrable, tginitdeferred, tgnargs, tgattr, tgargs from (pg_trigger join pg_class on tgrelid=pg_class.oid) join pg_proc on (tgfoid=pg_proc.oid) where tgname not ilike '%constraint%' and tgname not ilike 'pg%'; the query which you had given shown the implicit triggers which is created for maintaining the constraints.. -- so the query which i had given will show the user created triggers... THANKS
Re: [PERFORM] migration of 7.4 to 8.1
sathiya psql wrote: Is there any article describing the migration database from postgresql 7.4to 8.1 This might be a silly question, but ... why 8.1 ? If you're doing a major upgrade, why not go straight to 8.3? It's been out long enough that there aren't any obvious nasty bugs, and there have been a fair few fixes and improvements since prior versions. -- Craig Ringer -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
