[PERFORM] Index not used on group by

2005-09-27 Thread Andrey Repko
Hello all, I have table ma_data, that contain above 30 rows. This table has primary key id, and field alias_id. I create index (btree)on this field. Set statistic: ALTER TABLE public.ma_data ALTER COLUMN alias_id SET STATISTICS 998; So, when I do something like

Re: [PERFORM] Query seem to slow if table have more than 200 million rows

2005-09-27 Thread Ahmad Fajar
Hi Qingqing, I don't know whether the statistic got is bad or good, this is the statistic: scooby=# select a.relid, a.relname, b.indexrelid, b.indexrelname, c.idx_scan, c.idx_tup_read, c.idx_tup_fetch, scooby-# a.heap_blks_read, a.heap_blks_hit, a.idx_blks_read, a.idx_blks_hit, scooby-#

Re: [PERFORM] Index not used on group by

2005-09-27 Thread Richard Huxton
Andrey Repko wrote: I have table ma_data, that contain above 30 rows. This table has primary key id, and field alias_id. I create index (btree)on this field. Set statistic: ALTER TABLE public.ma_data ALTER COLUMN alias_id SET STATISTICS 998; So, when I do something

Re: [PERFORM] Index not used on group by

2005-09-27 Thread Андрей Репко
Здравствуйте Richard, Tuesday, September 27, 2005, 1:48:15 PM, Вы писали: RH Andrey Repko wrote: I have table ma_data, that contain above 30 rows. This table has primary key id, and field alias_id. I create index (btree)on this field. Set statistic: ALTER TABLE

Re: [PERFORM] Index not used on group by

2005-09-27 Thread Андрей Репко
Здравствуйте Richard, Tuesday, September 27, 2005, 2:08:31 PM, Вы писали: sart_ma=# EXPLAIN ANALYZE SELECT alias_id FROM ma_data GROUP BY alias_id; QUERY PLAN

Re: [PERFORM] PostgreSQL overall design

2005-09-27 Thread Gnanavel S
On 9/27/05, Abhijit Menon-Sen [EMAIL PROTECTED] wrote: At 2005-09-27 15:20:05 +0530, [EMAIL PROTECTED] wrote: Can anyone please tell/point me where I can get the postgresql system layout (I've an interest to contribute). http://www.postgresql.org/developer/codingAnd, in

Re: [PERFORM] Index not used on group by

2005-09-27 Thread Richard Huxton
Андрей Репко wrote: RH What happens if you use something like RHSELECT DISTINCT alias_id FROM ma_data; sart_ma=# EXPLAIN ANALYZE SELECT DISTINCT alias_id FROM ma_data; QUERY PLAN

Re: [PERFORM] Index not used on group by

2005-09-27 Thread Richard Huxton
Андрей Репко wrote: Здравствуйте Richard, Tuesday, September 27, 2005, 2:08:31 PM, Вы писали: sart_ma=# EXPLAIN ANALYZE SELECT alias_id FROM ma_data GROUP BY alias_id; QUERY PLAN

Re: [PERFORM] slow database, queries accumulating

2005-09-27 Thread Dario
I have read that 600 connections are a LOT (somebody correct me please if I'm wrong), since each connections requires a process and your server must serve this. Besides the overhead involved, you will end up with 1200 megabytes of sort_mem allocated (probably idle most of time)... pgpool allows

Re: [HACKERS] [PERFORM] A Better External Sort?

2005-09-27 Thread Kevin Grittner
I can't help wondering how a couple thousand context switches per second would affect the attempt to load disk info into the L1 and L2 caches. That's pretty much the low end of what I see when the server is under any significant load. ---(end of

Re: [HACKERS] [PERFORM] A Better External Sort?

2005-09-27 Thread Josh Berkus
Ron, I've somehow missed part of this thread, which is a shame since this is an area of primary concern for me. Your suggested algorithm seems to be designed to relieve I/O load by making more use of the CPU. (if I followed it correctly). However, that's not PostgreSQL's problem;

Re: [PERFORM] VACUUM FULL vs CLUSTER

2005-09-27 Thread Dario
you can see that at the end of vacuum log (sorry for my english) ... INFO: free space map: 930 relations, 48827 pages stored; 60240 total pages needed -- NEEDED! -- I have already configured in postgresql.conf, you can see it below DETAIL: Allocated FSM size: 1000 relations + 7 pages =

[PERFORM] Delphi connection ADO is slow

2005-09-27 Thread Everton
Hello, My connection ADO is very, very, very slow My Delphi connection saw ADO is very slow. All SQL that I execute delaybig, I tested in pgadmin and the reply is instantaned, the problem this in the Delphi? Tanks! No virus found in this outgoing message. Checked by AVG Anti-Virus.

Re: [PERFORM] PostgreSQL overall design

2005-09-27 Thread Bruce Momjian
Have you read the developers FAQ? --- Gnanavel S wrote: Hi All, Can anyone please tell/point me where I can get the postgresql system layout (I've an interest to contribute). I would also like to know the files

Re: [PERFORM] PostgreSQL overall design

2005-09-27 Thread Gnanavel S
On 9/27/05, Bruce Momjian pgman@candle.pha.pa.us wrote: Have you read the developers FAQ? Thanks Bruce. I'm going through that. --- Gnanavel S wrote: Hi All, Can anyone please tell/point me where I can get the postgresql

Re: [HACKERS] [PERFORM] A Better External Sort?

2005-09-27 Thread Ron Peacetree
From: Josh Berkus josh@agliodbs.com ent: Sep 27, 2005 12:15 PM To: Ron Peacetree [EMAIL PROTECTED] Subject: Re: [HACKERS] [PERFORM] A Better External Sort? I've somehow missed part of this thread, which is a shame since this is an area of primary concern for me. Your suggested algorithm seems

Re: [HACKERS] [PERFORM] A Better External Sort?

2005-09-27 Thread Jeffrey W. Baker
On Tue, 2005-09-27 at 13:15 -0400, Ron Peacetree wrote: That Btree can be used to generate a physical reordering of the data in one pass, but that's the weakest use for it. The more powerful uses involve allowing the Btree to persist and using it for more efficient re-searches or combining

[PERFORM] PostgreSQL overall design

2005-09-27 Thread Gnanavel S
Hi All, Can anyone please tell/point me where I can get the postgresql system layout (I've an interest to contribute). I would also like to know the files involved for performing each task ( for eg when doing a select operation what is exactly happening in postgres along with the files). I was

[PERFORM] The need for full vacuum / reindex

2005-09-27 Thread Tobias Brox
By occation, we dropped the whole production database and refreshed it from a database backup - and all our performance problems seems to have gone. I suppose this means that to keep the database efficient, one eventually does have to do reindexing and/or full vacuum from time to time? --

Re: [PERFORM] The need for full vacuum / reindex

2005-09-27 Thread Bruno Wolff III
On Wed, Sep 28, 2005 at 05:33:27 +0200, Tobias Brox [EMAIL PROTECTED] wrote: By occation, we dropped the whole production database and refreshed it from a database backup - and all our performance problems seems to have gone. I suppose this means that to keep the database efficient, one