[PERFORM] how to use explain analyze

2011-10-25 Thread alan
I'm new to postgres and was wondering how to use EXPLAIN ANALYZE Can I use the output from ANALYZE EXPLAIN to estimate or predict the actual time it would take for a given query to return? I ask because I'm writing a typical web app that allows the user to build and submit a query to my DB.

Re: [PERFORM] CTE vs Subquery

2011-10-25 Thread Merlin Moncure
On Tue, Oct 25, 2011 at 11:47 AM, Linos wrote: > El 25/10/11 18:43, Tom Lane escribió: >> Linos writes: >>>     i am having any problems with performance of queries that uses CTE, can >>> the >>> join on a CTE use the index of the original table? >> >> CTEs act as optimization fences.  This is a

Re: [PERFORM] CTE vs Subquery

2011-10-25 Thread Linos
El 25/10/11 18:43, Tom Lane escribió: > Linos writes: >> i am having any problems with performance of queries that uses CTE, can >> the >> join on a CTE use the index of the original table? > > CTEs act as optimization fences. This is a feature, not a bug. Use > them when you want to isola

Re: [PERFORM] CTE vs Subquery

2011-10-25 Thread Tom Lane
Linos writes: > i am having any problems with performance of queries that uses CTE, can > the > join on a CTE use the index of the original table? CTEs act as optimization fences. This is a feature, not a bug. Use them when you want to isolate the evaluation of a subquery.

[PERFORM] CTE vs Subquery

2011-10-25 Thread Linos
Hi all, i am having any problems with performance of queries that uses CTE, can the join on a CTE use the index of the original table?, suppose two simple tables: CREATE TABLE employee ( emp_id integer NOT NULL, name character varying NOT NULL, CONSTRAINT employee_pkey PRIMARY KEY (emp_i

Re: [PERFORM] Choosing between Intel 320, Intel 510 or OCZ Vertex 3 SSD for db server

2011-10-25 Thread David Boreham
On 10/25/2011 8:55 AM, Claudio Freire wrote: But what about unexpected failures. Faulty electronics, stuff like that? I really don't think a production server can work without at least raid-1. Same approach : a server either works or it does not. The transition between working and not workin

Re: [PERFORM] Choosing between Intel 320, Intel 510 or OCZ Vertex 3 SSD for db server

2011-10-25 Thread Claudio Freire
On Mon, Oct 24, 2011 at 11:37 PM, David Boreham wrote: >> What about redundancy? >> >> How do you swap an about-to-die SSD? >> >> Software RAID-1? > > The approach we take is that we use 710 series devices which have predicted > reliability similar to all the other components in the machine, there

[PERFORM] sub

2011-10-25 Thread Cezariusz Marek
-- Cezariusz Marek   mob: +48 608 646 494 http://www.comarch.com/   tel: +48 33 815 0734 -- Sent via pgsql-performance mailing lis

Re: [PERFORM] Bad plan by Planner (Already resolved?)

2011-10-25 Thread Robins Tharakan
Thanks Kevin, That's a pretty neat way to managing (at least) minor upgrades. Like I said, this place is new, and so although I'm quite positive about upgrading to the latest, I should probably take things one-at-a-time and bring in this idea of implementing regular updates sometime in the fut

Re: [PERFORM] Query running a lot faster with enable_nestloop=false

2011-10-25 Thread Robins Tharakan
Hi Mohanaraj, One thing you should certainly try is to increase the default_statistics_target value from 50 up to say about 1000 for the larger tables. Large tables tend to go off on estimates with smaller values here. I guess I am not helping here, but apart from your query, those estimate

[PERFORM] Query running a lot faster with enable_nestloop=false

2011-10-25 Thread Mohanaraj Gopala Krishnan
Hi guys, I have a query that runs a lot slower (~5 minutes) when I run it with the default enable_nestloop=true and enable_nestloop=false (~10 secs). The actual query is available here http://pastie.org/2754424 . It is a reporting query with many joins as the database is mainly used for transactio

Re: [PERFORM] Choosing between Intel 320, Intel 510 or OCZ Vertex 3 SSD for db server

2011-10-25 Thread Thilo Raufeisen
Am 24.10.2011 16:09, schrieb Amitabh Kant: while Intel 320 had a nasty bug which has been rectified Be careful with that Intel SSD. This one is still very buggy. Take a look at the Intel forums http://communities.intel.com/community/tech/solidstate?view=discussions#/ about users who are comp

Re: [PERFORM] delete/recreate indexes

2011-10-25 Thread alan
Thanks Jeff, On Oct 20, 4:51 am, pg...@j-davis.com (Jeff Davis) wrote: > Also, to take a step back, why do you try to keep the timestamps > changing like that? Why not store the information you need in the record > (e.g. insert time as well as the datum) and then compute the result you > need usin