Re: [PERFORM] Duplicate deletion optimizations

ge). - Use EXISTS statement in the delete (but not recommended by another reply) I'll try your ideas this week, and I'll give you results. Antoine. -- 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] Duplicate deletion optimizations

duration is lowered to ~5s. My questions: - Why the planner refuse to use my index? - Is there a better method for my problem? Thanks by advance for your help, Antoine Millet. [1] http://stackoverflow.com/questions/1109061/insert-on-duplicate-update-postgresql http://stackoverflow.com

[PERFORM] Duplicate deletion optimizations

able_seqscan = off", the index is used [3] and query duration is lowered to ~5s. My questions: - Why the planner refuse to use my index? - Is there a better method for my problem? Thanks by advance for your help, Antoine Millet. [1] http://stackoverflow.com/questions/11

Re: [PERFORM] multiple joins + Order by + LIMIT query performance issue

Ok, I've tried everything, and the planner keeps choosing index scans when it shouldnt. Is there a way to disable index scans? Antoine -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/

Re: [PERFORM] multiple joins + Order by + LIMIT query performance issue

If a misestimate of this kind is bugging you enough that you're willing to change the query, I think you can fix it like this: select ... from foo order by x limit n; => select ... from (select ... from foo order by x) ss limit n; The subselect will be planned without awarene

Re: [PERFORM] multiple joins + Order by + LIMIT query performance issue

is gone too, and the query runs in a few millisecs!! This is crazy, so simply by adding a LIMIT to a query, the planning is changed in a very bad way. Does the planner use the LIMIT as a sort of hint? Thank you for your explanations, Antoine Baudoux -- Sent via pgsql-performance mailing li

Re: [PERFORM] multiple joins + Order by + LIMIT query performance issue

id) -> Index Scan using t_system_pkey on t_system system (cost=0.00..6.27 rows=1 width=16) (never executed) Index Cond: (system.id = service.system_id) Total runtime: 0.362 ms On May 6, 2008, at 5:38 PM, Guillaume Smet wrote: Antoine, On Tue, May 6, 20

[PERFORM] multiple joins + Order by + LIMIT query performance issue

ws=1 width=16)" " Index Cond: (service_s2_.id = service_s3_.system_id)" [/code] My understanding is that in the first case the sort is done after all the table joins and filtering, but in the second case ALL the rows in t_event are scanne

Re: [PERFORM] OT - select + must have from - sql standard syntax?

You can get free "draft" versions that are close-enough-to-final to be perfectly usable. See our developers' FAQ for some links. I like the drafts partly because they're plain ASCII, and so far easier to search than PDFs ... Great to know - thanks! Cheers Antoine -- Th

Re: [PERFORM] OT - select + must have from - sql standard syntax?

FROM dual" is not > in the standard either (certainly the spec does not mention any such > table). Thanks for your answers guys. I was pretty sure DUAL wasn't in the standard (never seen it outside an oracle context) but wasn't at all sure about the FROM. Cheers Antoine ps. sh

[PERFORM] OT - select + must have from - sql standard syntax?

Hi, I don't have a copy of the standard on hand and a collegue is claiming that there must be a from clause in a select query (he is an oracle guy). This doesn't seem to be the case for postgres... does anyone know? Cheers Antoine ps. any one of them will do... -- This is where I shoul

Re: [PERFORM] vacuuming problems continued

aste for a copy/drop scenario... I will try and increase settings and keep you posted. Cheers Antoine -- This is where I should put some witty comment. ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster

[PERFORM] vacuuming problems continued

.. Help! 8-] Cheers Antoine -- This is where I should put some witty comment. ---(end of broadcast)--- TIP 6: explain analyze is your friend

Re: [PERFORM] [Solved] Slow performance on Windows .NET and OleDb

On 01/04/06, Magnus Hagander <[EMAIL PROTECTED]> wrote: > > This is a blatant thread steal... but here we go... > > Do people have any opinions on the pgsql driver? > > It's very nice. ... Thanks for the tips - i'll try a couple of test apps soon. Cheers Antoine

Re: [PERFORM] [Solved] Slow performance on Windows .NET and OleDb

defending postgres against mysql/postgres/sqlserver? Cheers Antoine ps. I try my best not to steal threads but sometimes... :-) On 30/03/06, Dave Dutcher <[EMAIL PROTECTED]> wrote: > I use Npgsql, and the connection string I use is real simple: > > Server=192.168.0.36;Database=mydb;U

[PERFORM] MVCC intro and benefits docs?

Hi, Does anyone know of any fairly entry-level documentation for the benefits-drawbacks of MVCC in the db? As it relates to performance? Postgres vs the others? Cheers Antoine -- This is where I should put some witty comment. ---(end of broadcast

Re: [PERFORM] n00b autovacuum question

On 19/03/06, Andreas Pflug <[EMAIL PROTECTED]> wrote: > Antoine wrote: > > On 18/03/06, Andreas Pflug <[EMAIL PROTECTED]> wrote: > > > >>Antoine wrote: > >> > >>>Hi, > >>>I have enabled the autovacuum daemon, but occasionally sti

[PERFORM] partitioning

Hi, Is there any work on the cards for implementing other partitioning strategies? I see mysql 5.1 will have support for hashes and stuff but didn't see anything in the todos for postgres. Cheers Antoine -- This is where I should put some witty comment. ---(e

Re: [PERFORM] n00b autovacuum question

On 18/03/06, Andreas Pflug <[EMAIL PROTECTED]> wrote: > Antoine wrote: > > Hi, > > I have enabled the autovacuum daemon, but occasionally still get a > > message telling me I need to run vacuum when I access a table in > > pgadmin. > > pgAdmin notices

[PERFORM] n00b autovacuum question

Hi, I have enabled the autovacuum daemon, but occasionally still get a message telling me I need to run vacuum when I access a table in pgadmin. Is this normal? Should I use scripts instead of the daemon? Would posting config options make this a much more sensible question? Cheers Antoine -- This

[PERFORM] query stopped working after tables > 50000 records

gives me nothing. I have come up with a solution that gets the text via another query (possibly even a better solution), but this seems very strange. Can anyone shed some light on the subject? I tried a full vacuum on the tables that needed it, and a postgres restart, all to no avail. Cheers An

Re: [PERFORM] new to postgres (and db management) and performance already a problem :-(

On 17/01/06, [EMAIL PROTECTED] <[EMAIL PROTECTED]> wrote: > Try a), b), and c) in order on the "offending" tables as they address> the problem at increasing cost...thanks alot for the detailed information! the entire concept of vacuum isn'tyet that clear to me, so your explanations and hints are ve

[PERFORM] new to postgres (and db management) and performance already a problem :-(

that might guide me in sorting out these sorts of problems? Some stuff with pratical examples would be good so I could compare with what we have. Thanks Antoine ps. I had a look with top and it didn't look like it was going much over 15% cpu, with memory usage negligeable. There are usuall

Re: [PERFORM] Nested Loop trouble : Execution time increases more

ng more keywords. Thanks to Tom Lane and Simon Riggs. Best regards, Antoine Bajolet Antoine Bajolet a écrit : Hello, Tom Lane a écrit : Antoine Bajolet <[EMAIL PROTECTED]> writes: We are using postgresql in a search engine on an intranet handling throusand of documents. But we a

Re: [PERFORM] Nested Loop trouble : Execution time increases more

Hello, Tom Lane a écrit : Antoine Bajolet <[EMAIL PROTECTED]> writes: We are using postgresql in a search engine on an intranet handling throusand of documents. But we ave a big problem when users use more than two search key. I think you need to increase the statistics targe

[PERFORM] Nested Loop trouble : Execution time increases more 1000 time (long)

-> Index Scan using fiches_pkey on fiches f (cost=0.00..3.36 rows=1 width=4) (actual time=0.041..0.043 rows=1 loops=2929) Index Cond: (f.f_id = "outer".f_id) Total runtime: 673048.405 ms -- More than 10 minutes ! Is there a specific reason the planner chooses this way ? Can whe do something on the postgresql configuration to avoid this ? Can whe force the planner to use a hash join as it does for the first joins ? Regards, Antoine Bajolet ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster