X-Original-To: pgsql-performance@postgresql.org Received: from localhost (postgresql.org [64.49.215.8]) by postgresql.org (Postfix) with ESMTP id 404FC47625C for ; Tue, 4 Mar 2003 12:29:13 -0500 (EST) Received: from postgresql.org ([64.49.215.8]) by localhost (postgresql.org [64.49.215.8:10024]) (amavisd-new) with SMTP id 43465-04 for ; Tue, 4 Mar 2003 12:29:11 -0500 (EST) Received: from serwer.skawsoft.com.pl (serwer.skawsoft.com.pl [213.25.37.66]) by postgresql.org (Postfix) with ESMTP id 1FF0C47624C for ; Tue, 4 Mar 2003 12:29:11 -0500 (EST) Received: from klaster.net (pa160.krakow.cvx.ppp.tpnet.pl [213.76.36.160]) by serwer.skawsoft.com.pl (Postfix) with ESMTP id 774252B88F; Tue, 4 Mar 2003 18:27:29 +0100 (CET) Message-ID: <3E64E27A.3050708@klaster.net> Date: Tue, 04 Mar 2003 18:29:30 +0100 From: Tomasz Myrta User-Agent: Mozilla/5.0 (Windows; U; Win 9x 4.90; PL; rv:1.1) Gecko/20020826 X-Accept-Language: pl, en-us, en MIME-Version: 1.0 To: Robert Treat Cc: pgsql-performance@postgresql.org Subject: Re: Slow query performance on large table References: <000001c2e268$b1e47210$0c64a8c0@paulspc> <1046797349.16957.20.camel@camel> Content-Type: text/plain; charset=us-ascii; format=flowed Content-Transfer-Encoding: 7bit X-Virus-Scanned: by amavisd-new X-Archive-Number: 200303/16 X-Sequence-Number: 1314 Robert Treat wrote: > Maybe it's just me, but I get the feeling you need to work some regular > reindexing into your maintenance schedule. Given your query is using > between, I don't think it would use the index on the time field anyway > (and explain analyze seems to be supporting this). Rewrite it so that > you have a and time > foo and time < bar and I think you'll see a > difference. With that in mind, I think your speedier query results are > due more to having a non-bloated index freshly created than the fact > that it being a dual column index. > > Robert Treat Do you know anything about between, what should we know? I made some tests, and there was no noticable difference between them: pvwatch=# EXPLAIN analyze * from stats where hostid=1 and stp between 1 and 2; QUERY PLAN ---------------------------------------------------------------------------------------------------------------- Index Scan using ind_stats on stats (cost=0.00..6.01 rows=1 width=28) (actual time=0.00..0.00 rows=0 loops=1) Index Cond: ((hostid = 1) AND (stp >= 1) AND (stp <= 2)) Total runtime: 0.00 msec (3 rows) pvwatch=# EXPLAIN analyze SELECT * from stats where hostid=1 and stp> 1 and stp<2; QUERY PLAN ---------------------------------------------------------------------------------------------------------------- Index Scan using ind_stats on stats (cost=0.00..6.01 rows=1 width=28) (actual time=0.00..0.00 rows=0 loops=1) Index Cond: ((hostid = 1) AND (stp > 1) AND (stp < 2)) Total runtime: 0.00 msec (3 rows) Regards, Tomasz Myrta