Re: [SQL] Simple delete takes hours
On 05.03.2005 00:24 PFC wrote: > Every time a row is removed from pwd_name, the ON DELETE CASCADE > trigger will look in pwd_name_rev if there is a row to delete... Does > it have an index on pwd_name_rev( rev_of ) ? If not you'll get a full > table scan for every row deleted in pwd_name... Yes that's it, thanks a lot! pwdcheck=# explain analyze delete from pwd_name where description=1; QUERY PLAN - Seq Scan on pwd_name (cost=0.00..116571.15 rows=1774250 width=6) (actual time=9526.671..21957.920 rows=543348 loops=1) Filter: (description = 1) Total runtime: 3.749 ms (3 rows) Is it possible to get fired triggers/called stored procedures and things like that in an 'explain' ? To find out why the delete is that slow I did: pwdcheck=# explain analyze delete from pwd_name where id in pwdcheck-# (select id from pwd_name where description=1 limit 10); There was no hint that every deleted row leads to a full table scan. Thomas ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [SQL] Postgresql FK to MS SQL triggers
Igor, > For interested people I wrote a PHP script which: > 1) Extracts all underlying triggers from pg_trigger table in Postgres used > to support FK (3 triggers for each FK) > 2) Generates a MSSQL script file which recreates all triggers in MSSQL How about a script which goes the other way? -- --Josh Josh Berkus Aglio Database Solutions San Francisco ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] Postgres performance
No, I haven't foreign keys in the older version, in that new I've it... however I manage relations from app code (PHP)... Really ? In my experience this is a sure way to get inconsistencies slowly creeping into your database, and you also get a load of funky concurrency issues. doesn't MYSQL allow to use 'foreign keys' in sure and fast way then? It does, IF you use the InnoDB engine... which is slower than postgres... and there are a lot of gotchas.> Not for every query, for every CONNECTION. You are using persistant connections are you. Are you ? I'm using PHP and every user (can be from 1 user to 100 users) must connect to the database... do you know how I can use persistant connection? I think it's impossible... I'm wrong? Well, first, I get a connection establishment time of about 20 ms in mysql and 60 ms in postgres. This information is useless as I use persistent connections, obviously, because it is crazy to spend 20 ms connecting just to make a 0.5 ms query. Now, in PHP, you can use mysql_pconnect instead of mysql_connect to get a persistent connection. mod_php keeps a pool of connections. The same thing probably applies for postgres, but as I don't use it with PHP (only with Python) I can't tell. Look in the docs for "persistent connections". This way, each Apache server process keeps a persistent connection open, and re-uses it for every page. You save the connection establishment time and load. > - why connection time is slower? (compared to mySQL)? Because MySQL forks a thread whereas Postgres forks a process. This is of no importance as everyone uses persistent connections anyway. See last answer... I hope my explanations are useful. And MySQL requires analyze too (read the docs), optimize table which looks like vacuum to me, and sometimes repair table... Ok... they are conceptually implemented in the same mode... Well, not really. For instance when you make joins, postgres will look the ANALYZE stats and say "Hm, this value seems rare, I'll use an index scan to get these few values" or "This column has few distinct values, I'll better load them all into a hash before joining to this big table instead of making a lot of index scans"... it can get a lot more complicated. MySQL thinks "I see indexed column, I don't know what a hash join is, thus I use index." Both try to estimate the size of result sets to choose plans, postgres generally does it well, mysql sometimes can do something which happens to work, most of the time it makes no diference. But using the MySQL analyze seems to speed up some of my queries, though. I don't think it has such detailed stats as postgres, though. Point is, if the query gets complex, forget MySQL... ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
[SQL] Links between rows in a table
Hi. We are currently designing a web-based application in which users can add other users as "friends". These links are bi-directional, meaning that when A adds B to his friends, he is automatically one of B's friends. Eventually we will have to add a feature that shows how A is is related to some other user E (via B->C->D->...) - similar to the way Friendster, Orkut and others work, but on a much smaller scale (some 5000 users). Probably the most annoying part is that it has to work with different database vendors, including MySQL4 (default install, MyISAM tables, no foreign keys, no stored procedures, no triggers, no views etc). Most of the logic will have to live in the application, and I won't be able to use anything beyond plain SQL. I can see several ways how such links could be modeled in a relational database, but I was wondering if there was some tried-and-true recipe that would spare me from reinventing the wheel. Putting aside for the moment everything but the links, the simplest way of connecting users would be a "friends" table (user_id int, friend_id int). We could get a user's friends with a simple query like this: SELECT friend_id FROM friends WHERE user_id = X UNION SELECT user_id FROM friends WHERE friend_id = X; Is there a better way, or any reason why we should not go that way, especially considering other likely queries such as "friends of friends" or the connection chain mentioned above? We are also thinking of precalculating possible connection chains, or trees, at night (to a certain depth) in order to avoid performance problems in the peak hours. Any ideas on how such precalculated results could be stored and queried efficiently? Thanks in advance, Stefan Weiss ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
