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 BA651329CDB for ; Sat, 18 Sep 2004 01:23:56 +0100 (BST) 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 63299-03 for ; Sat, 18 Sep 2004 00:23:50 +0000 (GMT) Received: from mproxy.gmail.com (rproxy.gmail.com [64.233.170.206]) by svr1.postgresql.org (Postfix) with ESMTP id F345E329CBC for ; Sat, 18 Sep 2004 01:23:52 +0100 (BST) Received: by mproxy.gmail.com with SMTP id v18so528095rnb for ; Fri, 17 Sep 2004 17:23:52 -0700 (PDT) Received: by 10.38.8.29 with SMTP id 29mr262418rnh; Fri, 17 Sep 2004 17:23:44 -0700 (PDT) Received: by 10.38.8.79 with HTTP; Fri, 17 Sep 2004 17:23:44 -0700 (PDT) Message-ID: <3f71fdf1040917172395a3be5@mail.gmail.com> Date: Fri, 17 Sep 2004 19:23:44 -0500 From: Stephen Crowley Reply-To: Stephen Crowley To: pgsql-performance@postgresql.org Subject: Re: Large # of rows in query extremely slow, not using Cc: Manfred Koizar , =?ISO-8859-1?Q?Pierre-Fr=E9d=E9ric_Cai_llaud?= In-Reply-To: Mime-Version: 1.0 Content-Type: text/plain; charset=US-ASCII Content-Transfer-Encoding: 7bit References: <3f71fdf104091317515c756512@mail.gmail.com> <20040914184358.08e271fe@kingfisher.intern.logi-track.com> <3f71fdf1040916185113e277e6@mail.gmail.com> X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.0 tagged_above=0.0 required=5.0 tests= X-Spam-Level: X-Archive-Number: 200409/189 X-Sequence-Number: 8317 Ok.. now I ran "VACUUM FULL' and things seem to be working as they should.. explain analyze select * from history where date='2004-09-07' and stock='MSFT'; Seq Scan on island_history (cost=0.00..275359.13 rows=292274 width=83) (actual time=50.000..411683.000 rows=265632 loops=1) Filter: ((date = '2004-09-07'::date) AND ((stock)::text = 'MSFT'::text)) Total runtime: 412703.000 ms random_page_cost and effective_cache_size are both default, 8 and 1000 explain analyze select * from history where date='2004-09-07' and stock='ORCL'; "Index Scan using island_history_date_stock_time on island_history (cost=0.00..181540.07 rows=102166 width=83) (actual time=551.000..200268.000 rows=159618 loops=1)" " Index Cond: ((date = '2004-09-07'::date) AND ((stock)::text = 'ORCL'::text))" "Total runtime: 201009.000 ms" So now this in all in proportion and works as expected.. the question is, why would the fact that it needs to be vaccumed cause such a huge hit in performance? When i vacuumed it did free up nearly 25% of the space. --Stephen On Fri, 17 Sep 2004 22:44:05 +0200, Manfred Koizar wrote: > On Thu, 16 Sep 2004 20:51:11 -0500, Stephen Crowley > wrote: > >explain analyze select * from history where date='2004-09-07' and > >stock='ORCL' LIMIT 10; > > >" -> Index Scan using island_history_date_stock_time on > >island_history (cost=0.00..183099.72 rows=102166 width=83) (actual > >time=1612.000..1702.000 rows=10 loops=1)" > ^^ > LIMIT 10 hides what would be the most interesting info here. I don't > believe that > EXPLAIN ANALYSE SELECT * FROM history WHERE ... > consumes lots of memory. Please try it. > > And when you post the results please include your Postgres version, some > info about hardware and OS, and your non-default settings, especially > random_page_cost and effective_cache_size. > > May I guess that the correlation of the physical order of tuples in your > table to the contents of the date column is pretty good (examine > correlation in pg_stats) and that island_history_date_stock_time is a > 3-column index? > > It is well known that the optimizer overestimates the cost of index > scans in those situations. This can be compensated to a certain degree > by increasing effective_cache_size and/or decreasing random_page_cost > (which might harm other planner decisions). > > You could also try > CREATE INDEX history_date_stock ON history("date", stock); > > This will slow down INSERTs and UPDATEs, though. > > Servus > Manfred >