[PERFORM] Query Optimization

Hi, Can anyone suggest why this query so slow. SELECT version(); version - PostgreSQL 8.4.2 on i386-portbld-freebsd7.2, compiled by GCC cc (GCC)

Re: [PERFORM] Query Optimization

Sorry for the noise. I make vacuum analyze and the problem is solved. Kaloyan Iliev Kaloyan Iliev Iliev wrote: Hi, Can anyone suggest why this query so slow. SELECT version(); version

[PERFORM] Question about LEFT JOIN and query plan

Hello, I have I query which behave strangely (according to me). According to the first plan PG makes absolutely unnecessary seq scan on tables "invoices" and "domeini" and etc. I thing they should be access only if there are rows from the where. Why the left join executes first? Then I rewrite t

Re: [PERFORM] Question about LEFT JOIN and query plan

es i  (cost=0.00..5.13 rows=1 width=8) (never executed) Index Cond: (ac.ino = i.ino)  Total runtime: 92.612 ms (34 rows) Kevin Grittner wrote: Kaloyan Iliev Iliev wrote: I thing they should be access only if there are rows from the where. Why the left join executes fi

Re: [PERFORM] Question about LEFT JOIN and query plan

Hello again, I have another query which performance drops drastically after PG upgrade. I can not improve the plan no matter how hard I try. I try creating new indexes and rewrite the query with JOIN .. ON instead of commas but nothing happens. I will appreciate any suggestions. Best regards, K

Re: [PERFORM] Question about LEFT JOIN and query plan

184 loops=1) Filter: (is_removed = 0) -> Hash (cost=1026.01..1026.01 rows=59101 width=4) (actual time=277.161..277.161 rows=59027 loops=1) -> Seq Scan on request r (cost=0.00..1026.01 rows=59101 width=4) (actual time=0.075..131.951 rows=5902

[PERFORM] Performance problem from migrating between versions!

Hi, I have the following problem. A week ago we've migrated from PGv7.2.3 to 7.4.6. There were a lot of things in the apps to chenge but we made them. But one query doesn't want to run. In the old PGv7.2.3 it passes for 10 min. In the new one it gaves: DBD::Pg::st execute failed: ERROR: out of

Re: [PERFORM] Performance problem from migrating between versions!

on. This is used for debugging the query planner. How it is used to debug the query planner? And why it lower the mem usage? Thank you in advance. Kaloyan Iliev Tom Lane wrote: Kaloyan Iliev Iliev <[EMAIL PROTECTED]> writes: I have the following problem. A week ago we've m

Re: [PERFORM] Performance problem from migrating between versions!

Hi, I am asking the prev. question because there is no change in the query plan (as far as I see) but the mem usage decreases from 258M to 16M. Kaloyan Iliev Tom Lane wrote: Kaloyan Iliev Iliev <[EMAIL PROTECTED]> writes: I have the following problem. A week ago we'

Re: [PERFORM] Performance problem from migrating between versions!

Tom Lane wrote: I wouldn't recommend turning off hashagg as a permanent solution, it was just a quickie to verify my suspicion of where the memory was going. Hi, How to understant the upper sentence? I shouldn't turn "hashagg" off permanently for this query or for the entire database. For now I

Re: [PERFORM] Performance problem from migrating between versions!

Hi, I try it and it doesn't resolve the problem:( So, now what? To leave it that way for this query or There must be permanent solution because if other queries behave like that? Kaloyan Iliev Tom Lane wrote: Kaloyan Iliev Iliev <[EMAIL PROTECTED]> writes: W

Re: [PERFORM] 200 times slower then MSSQL??

Hi, First it will be good if you supply some EXPLAIN ANALYZE results from your query. Second, do you created the indexes which can be used with WHERE conditions. And Third AFAK MAX doesn't use index. If you only need max then you can try: ORDER BY DESC and LIMIT 1. But you can't use this i

Re: [PERFORM] How to read query plan

Hi, I have an idea about your problem. Will it be difficult not to change the entire code but only the queries? You can change type in the Postgres to bool. Then, when select data you can use a CASE..WHEN to return 'Y' or 'N' or even write a little function which accepts bool and returns 'Y' o

[PERFORM] Change query join order

Hi , I have a simple query with two tables. ms_data ~ 450 rows ms_commands_history ~ 50 rows I have done analyze and there are indexes. My question is why the planner didn't do the index scan first on ms_data to reduce the rows to ~ 11000 and the use the PK index on ms_commands_history

Re: [PERFORM] Change query join order

Thanks You,  I changed the random_page_cost to 2 and the query plan has changed and speeds up.  I will check the other queries but I think I will leave it at this value. Thank you again.   Kaloyan Iliev Robert Haas wrote: On Fri, Jan 8, 2010 at 2:23 PM, Tom Lane wrote: I

[PERFORM] Slow query after upgrade from 8.2 to 8.4

Hello All. We recently upgrade our server from PG8.2 to 8.4. Our current version is: database=> SELECT version(); version

Re: [PERFORM] Slow query after upgrade from 8.2 to 8.4

= CF.confid LIMIT 1; http://explain.depesz.com/s/4zb I want this plan and this query but with the additional condition 'CF.office = 18'. How could I force the planner to use this plan and just filter the result. Best regards,  Kaloyan Iliev Tom Lane wrote: Kaloyan Iliev Iliev

Re: [PERFORM] Slow query after upgrade from 8.2 to 8.4

Hi, Thanks for Replay. Actually I finally find a solution. If I rewrite the query in this way: explain analyze select 1 from acc_clients AC, acc_debts AD, debts_desc DD,