X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id E53FC528D9 for ; Wed, 22 Jun 2005 04:45:27 -0300 (ADT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 47698-05 for ; Wed, 22 Jun 2005 07:45:22 +0000 (GMT) Received: from wproxy.gmail.com (wproxy.gmail.com [64.233.184.192]) by svr1.postgresql.org (Postfix) with ESMTP id A9CEA528A8 for ; Wed, 22 Jun 2005 04:45:20 -0300 (ADT) Received: by wproxy.gmail.com with SMTP id 68so166457wra for ; Wed, 22 Jun 2005 00:45:22 -0700 (PDT) DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=beta; d=gmail.com; h=received:message-id:date:from:reply-to:to:subject:cc:in-reply-to:mime-version:content-type:content-transfer-encoding:content-disposition:references; b=XIa3IVnYhFiQA/L6BZq/4i4DBfnsKT4GOU/1o4E8poCwbTcOqYBPPbJ9+8XQQLX9VDQN00o3NvVbfsAVnPfCoElc7gS8caUy5gUH52yOnb0esfOKfbQwTgGCKAmC8hxTSKRWMYbSKwy1Vk3TfbmFOzxjO9z9P6CbdglJGqvgWMg= Received: by 10.54.2.26 with SMTP id 26mr293319wrb; Wed, 22 Jun 2005 00:45:22 -0700 (PDT) Received: by 10.54.104.3 with HTTP; Wed, 22 Jun 2005 00:45:22 -0700 (PDT) Message-ID: Date: Wed, 22 Jun 2005 09:45:22 +0200 From: Kjell Tore Fossbakk Reply-To: Kjell Tore Fossbakk To: PFC Subject: Re: Querying 19million records very slowly Cc: Paul Ramsey , pgsql-performance@postgresql.org In-Reply-To: Mime-Version: 1.0 Content-Type: text/plain; charset=ISO-8859-1 Content-Transfer-Encoding: quoted-printable Content-Disposition: inline References: <42B8719A.6040809@refractions.net> X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.361 tagged_above=0 required=5 tests=AWL, RCVD_BY_IP X-Spam-Level: X-Archive-Number: 200506/423 X-Sequence-Number: 13060 database=3D> set enable_seqscan to on; SET Time: 0.34 ms database=3D> explain analyze select count(*) from test where p1=3D53; QUERY PLAN ---------------------------------------------------------------------------= -------------------------------------------- Aggregate (cost=3D522824.50..522824.50 rows=3D1 width=3D0) (actual time=3D56380.72..56380.72 rows=3D1 loops=3D1) -> Seq Scan on test (cost=3D0.00..517383.30 rows=3D2176479 width=3D0) (actual time=3D9.61..47677.48 rows=3D2220746 loops=3D1) Filter: (p1 =3D 53) Total runtime: 56380.79 msec (4 rows) Time: 56381.40 ms database=3D> explain analyze select count(*) from test where p1=3D53 and time > now() - interval '24 hours' ; QUERY PLAN ---------------------------------------------------------------------------= --------------------------------------------- Aggregate (cost=3D661969.01..661969.01 rows=3D1 width=3D0) (actual time=3D45787.02..45787.02 rows=3D1 loops=3D1) -> Seq Scan on test (cost=3D0.00..660155.28 rows=3D725493 width=3D0) (actual time=3D37799.32..45613.58 rows=3D42424 loops=3D1) Filter: ((p1 =3D 53) AND ("time" > (now() - '1 day'::interval))) Total runtime: 45787.09 msec (4 rows) Time: 45787.79 ms database=3D> explain analyze select date_trunc('hour', time),count(*) as total from test where p1=3D53 and time>now()-interval '24 hours' group by date_trunc order by date_trunc; QUERY PLAN ---------------------------------------------------------------------------= --------------------------------------------------------- Aggregate (cost=3D755116.97..760558.17 rows=3D72549 width=3D8) (actual time=3D46040.63..46717.61 rows=3D23 loops=3D1) -> Group (cost=3D755116.97..758744.44 rows=3D725493 width=3D8) (actual time=3D46022.06..46548.84 rows=3D42407 loops=3D1) -> Sort (cost=3D755116.97..756930.70 rows=3D725493 width=3D8) (actual time=3D46022.04..46198.94 rows=3D42407 loops=3D1) Sort Key: date_trunc('hour'::text, "time") -> Seq Scan on test (cost=3D0.00..660155.28 rows=3D725493 width=3D8) (actual time=3D37784.91..45690.88 rows=3D42407 loops=3D1) Filter: ((p1 =3D 53) AND ("time" > (now() - '1 day'::interval))) Total runtime: 46718.43 msec (7 rows) Time: 46719.44 ms database=3D> create index test_time_p1_idx on test(time,p1) ; CREATE INDEX Time: 178926.02 ms database=3D> vacuum analyze test ; VACUUM Time: 73058.33 ms database=3D> \d test Table "public.test" Column | Type | Modifiers -------------+--------------------------+----------- time | timestamp with time zone | source | inet | destination | inet | p1 | integer | p2 | integer | database=3D> \di public | test_time_idx | index | database | test public | test_source_idx | index | database | test public | test_destination_idx | index | database | test public | test_p1_idx | index | database | test public | test_p2_idx | index | database | test public | test_time_p1_idx | index | database | test database=3D> set enable_seqscan to off ; SET Time: 0.28 ms database=3D> explain analyze select date_trunc('hour', time),count(*) as total from test where p1=3D53 and time>now()-interval '24 hours' group by date_trunc order by date_trunc; Aggregate (cost=3D2315252.66..2320767.17 rows=3D73527 width=3D8) (actual time=3D2081.15..2720.44 rows=3D23 loops=3D1) -> Group (cost=3D2315252.66..2318929.00 rows=3D735268 width=3D8) (actual time=3D2079.76..2564.22 rows=3D41366 loops=3D1) -> Sort (cost=3D2315252.66..2317090.83 rows=3D735268 width=3D8) (actual time=3D2079.74..2243.32 rows=3D41366 loops=3D1) Sort Key: date_trunc('hour'::text, "time") -> Index Scan using test_time_p1_idx on test=20 (cost=3D0.00..2218878.46 rows=3D735268 width=3D8) (actual time=3D29.50..1774.52 rows=3D41366 loops=3D1) Index Cond: (("time" > (now() - '1 day'::interval)) AND (p1 =3D 53)) Total runtime: 2735.42 msec Time: 2736.48 ms database=3D> explain analyze select date_trunc('hour', time),count(*) as total from test where p1=3D80 and time>now()-interval '24 hours' group by date_trunc order by date_trunc; Aggregate (cost=3D1071732.15..1074305.59 rows=3D34313 width=3D8) (actual time=3D6353.93..7321.99 rows=3D22 loops=3D1) -> Group (cost=3D1071732.15..1073447.77 rows=3D343125 width=3D8) (actual time=3D6323.76..7078.10 rows=3D64267 loops=3D1) -> Sort (cost=3D1071732.15..1072589.96 rows=3D343125 width=3D8) (actual time=3D6323.75..6579.42 rows=3D64267 loops=3D1) Sort Key: date_trunc('hour'::text, "time") -> Index Scan using test_time_p1_idx on test=20 (cost=3D0.00..1035479.58 rows=3D343125 width=3D8) (actual time=3D0.20..5858= .67 rows=3D64267 loops=3D1) Index Cond: (("time" > (now() - '1 day'::interval)) AND (p1 =3D 80)) Total runtime: 7322.82 msec Time: 7323.90 ms database=3D> explain analyze select date_trunc('hour', time),count(*) as total from test where p1=3D139 and time>now()-interval '24 hours' group by date_trunc order by date_trunc; Aggregate (cost=3D701562.34..703250.12 rows=3D22504 width=3D8) (actual time=3D2448.41..3033.80 rows=3D22 loops=3D1) -> Group (cost=3D701562.34..702687.53 rows=3D225037 width=3D8) (actual time=3D2417.39..2884.25 rows=3D36637 loops=3D1) -> Sort (cost=3D701562.34..702124.94 rows=3D225037 width=3D8) (actual time=3D2417.38..2574.19 rows=3D36637 loops=3D1) Sort Key: date_trunc('hour'::text, "time") -> Index Scan using test_time_p1_idx on test=20 (cost=3D0.00..679115.34 rows=3D225037 width=3D8) (actual time=3D8.47..2156.= 18 rows=3D36637 loops=3D1) Index Cond: (("time" > (now() - '1 day'::interval)) AND (p1 =3D 139)) Total runtime: 3034.57 msec Time: 3035.70 ms Now, this query gives me all the hours in a day, with the count of all p1=3D53 for each hour. Pg uses 46.7 seconds to run with seqscan, while 2.7 seconds indexing on (time,p1). I think I turned "set enable_seqscan to on;" again, and then the planner used seqscan, and not index. - Why does Pg not see the benefits of using index? - and how can i tune the optimisation fields in postgresql.conf to help him= ? So now my PG uses a reasonable amout of time on these queries (with enable_seqscan turned off) The next place which seems to slow my queries, is probably my connection to PHP. I got a bash script running in cron on my server (freebsd 4.11), which runs php on a php file. To force PG to not use seqscans, I have modifies the postgresql.conf: .. enable_seqscan =3D false enable_indexscan =3D true .. effective_cache_size =3D 10000 random_page_cost =3D 2 .. I save the file, type 'pg_crl reload' then enter 'psql database'. database=3D> show enable_seqscan ; enable_seqscan ---------------- on (1 row) argus=3D> show effective_cache_size ; effective_cache_size ---------------------- 1000 (1 row) I have used the manual pages on postgresql, postmaster, and so on, but I cant find anywhere to specify which config file Pg is to use. I'm not entirely sure if he uses the one im editing (/usr/local/etc/postgresql.conf). Any hints, tips or help is most appreciated! Kjell Tore. On 6/21/05, PFC wrote: >=20 > use CURRENT_TIME which is a constant instead of now() which is not =20 > considered constant... (I think) >