Received: from localhost (unknown [200.46.204.183]) by postgresql.org (Postfix) with ESMTP id C4F2E37BFAA for ; Mon, 29 Sep 2008 10:43:18 -0300 (ADT) Received: from postgresql.org ([200.46.204.86]) by localhost (mx1.hub.org [200.46.204.183]) (amavisd-maia, port 10024) with ESMTP id 47292-09 for ; Mon, 29 Sep 2008 10:43:15 -0300 (ADT) X-Greylist: delayed 00:17:16.96682 by SQLgrey-1.7.6 Received: from wayr.org (wayr.org [88.191.18.57]) by postgresql.org (Postfix) with ESMTP id 7AF5237BE34 for ; Mon, 29 Sep 2008 10:43:15 -0300 (ADT) Received: by wayr.org (Postfix, from userid 1005) id 8B8A681C4; Mon, 29 Sep 2008 15:25:56 +0200 (CEST) Received: from imp.wayr.org (localhost.localdomain [127.0.0.1]) by wayr.org (Postfix) with ESMTP id 78A5D81D5 for ; Mon, 29 Sep 2008 15:25:53 +0200 (CEST) MIME-Version: 1.0 Date: Mon, 29 Sep 2008 15:25:53 +0200 From: To: pgsql-performance@postgresql.org Subject: dedicated server & postgresql 8.1 conf tunning Message-ID: X-Sender: paul@wayr.org User-Agent: RoundCube Webmail/0.1-rc1 Content-Type: text/plain; charset="UTF-8" Content-Transfer-Encoding: 8bit X-Bogosity: Ham, tests=bogofilter, spamicity=0.000000, version=1.1.3 X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=0 tagged_above=0 required=5 tests=none X-Spam-Level: X-Archive-Number: 200809/263 X-Sequence-Number: 31576 Hello I'm running pgsql 8.1.11 (from debian stable) on a server with 16GB RAM (Linux helios 2.6.18-6-amd64 #1 SMP Tue Aug 19 04:30:56 UTC 2008 x86_64 GNU/Linux). I have a table "tickets" with 1 000 000 insert by month ( ~2600 each 2hours ) (for the moment 13000000 rows for 5GB ) and i have to extract statistics ( number of calls, number of calls less than X seconds, number of news calles, number of calls from the new callers, ...) 1°) The server will handle max 15 queries at a time. So this is my postgresql.conf max_connections = 15 shared_buffers = 995600 # ~1Go temp_buffers = 1000 work_mem = 512000 # ~512Ko maintenance_work_mem = 1048576 # 1Mo max_fsm_pages = 41522880 # ~40Mo max_fsm_relations = 8000 checkpoint_segments = 10 checkpoint_timeout = 3600 effective_cache_size = 13958643712 # 13Go stats_start_collector = on stats_command_string = on stats_block_level = on stats_row_level = on autovacuum = off How can i optimize the configuration? 2°) My queries look like SELECT tday AS n, COUNT(DISTINCT(a.appelant)) AS new_callers, COUNT(a.appelant) AS new_calls FROM cirpacks.tickets AS a WHERE LENGTH(a.appelant) > 4 AND a.service_id IN ( 95, 224, 35, 18 ) AND a.exploitant_id = 66 AND a.tyear = 2008 AND a.tmonth = 08 AND EXISTS ( SELECT 1 FROM cirpacks.clients AS b WHERE b.appelant = a.appelant AND b.service_id IN ( 95, 224, 35, 18 ) AND b.heberge_id = 66 HAVING to_char(MIN(b.premier_appel), 'YYYYMMDD') = to_char(a.date, 'YYYYMMDD') ) GROUP BY n ORDER BY n; or select ... SUM( CASE WHEN condition THEN value ELSE 0) ... FROM cirpacks.tickets WHERE tyear = ... and tmonth = ... and tday = ... AND audiotel IN ( '...', '...' ....); or select ... SUM( CASE WHEN condition THEN value ELSE 0) ... FROM cirpacks.tickets WHERE '2007-01-01' <= date AND date <= '2008-08-31' AND audiotel IN ( '...', '...' ....); which indexes are the best ? case 0: index_0_0 (service_id, exploitant_id, palier_id, habillage_id, tweek, tday, thour, tmonth, tyear, length(appelant::text)) index_0_1 (audiotel, cat, tweek, tday, thour, tmonth, tyear, length(appelant::text)) or case 1 index_1_0 (audiotel, cat, service_id, exploitant_id, palier_id, habillage_id, tweek, tday, thour, tmonth, tyear, length(appelant::text)) or case 2: index_2_0 (tweek, tday, thour, tmonth, tyear, length(appelant::text)) index_2_1 (service_id, exploitant_id, palier_id, habillage_id) index_2_2 (audiotel, cat) or even (case 3) index_3_0 (service_id, exploitant_id, palier_id, habillage_id, tyear, length(appelant::text)) index_3_1 (service_id, exploitant_id, palier_id, habillage_id, tmonth, tyear, length(appelant::text)) index_3_2 (service_id, exploitant_id, palier_id, habillage_id, tday, tmonth, tyear, length(appelant::text)) [...]