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 E47A953D04 for ; Thu, 21 Apr 2005 09:14:40 -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 60161-08 for ; Thu, 21 Apr 2005 12:14:35 +0000 (GMT) Received: from localhost.net (cust.89.121.adsl.cistron.nl [195.64.89.121]) by svr1.postgresql.org (Postfix) with ESMTP id DA82353E1E for ; Thu, 21 Apr 2005 09:14:33 -0300 (ADT) Received: from [127.0.0.1] (localhost [127.0.0.1]) by localhost.net (Postfix) with ESMTP id 51F6C83D6D; Thu, 21 Apr 2005 14:14:35 +0200 (CEST) Message-ID: <42679922.3040300@trust-factory.com> Date: Thu, 21 Apr 2005 14:14:26 +0200 From: Richard van den Berg Organization: Trust Factory User-Agent: Debian Thunderbird 1.0.2 (X11/20050331) X-Accept-Language: en-us, en MIME-Version: 1.0 To: John A Meinel Cc: pgsql-perform Subject: Re: When are index scans used over seq scans? References: <426665A0.1080102@trust-factory.com> <42666890.1000401@arbash-meinel.com> <42667219.7050907@trust-factory.com> <4266773E.405@arbash-meinel.com> In-Reply-To: <4266773E.405@arbash-meinel.com> X-Enigmail-Version: 0.90.2.0 X-Enigmail-Supports: pgp-inline, pgp-mime Content-Type: text/plain; charset=ISO-8859-1 Content-Transfer-Encoding: 7bit X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.05 tagged_above=0 required=5 tests=FORGED_RCVD_HELO X-Spam-Level: X-Archive-Number: 200504/553 X-Sequence-Number: 11950 Thanks a lot John for the correct search terms. :-) The suggestion in http://archives.postgresql.org/pgsql-performance/2005-04/msg00029.php to add a constraint that checks (finishtime >= starttime) does not make a difference for me. Still seq scans are used. The width solution explained in http://archives.postgresql.org/pgsql-performance/2005-04/msg00027.php and http://archives.postgresql.org/pgsql-performance/2005-04/msg00116.php does make a huge difference when selecting 1 timestamp using a BETWEEN (2ms vs 2sec), but as soon as I put 2 timestamps in a table and try a join, everything goes south (7.7sec). I have 10k timestamps in the duration table. :-( I'm getting more confused on how the planner decides to use indexes. For example, if I try: explain analyze select us.oid from sessions us where '2005-04-10 23:11:00' between us.starttimetrunc and us.finishtimetrunc; QUERY PLAN -------------------------------------------------------------------------------------------------------------------------------------------------------------------- Index Scan using sessions_st_ft_idx2 on sessions us (cost=0.00..18320.73 rows=4765 width=4) (actual time=0.063..2.455 rows=279 loops=1) Index Cond: (('2005-04-10 23:11:00'::timestamp without time zone <= finishtimetrunc) AND ('2005-04-10 23:11:00'::timestamp without time zone >= starttimetrunc)) Total runtime: 2.616 ms is uses the index! However, if I change the date it does not: explain analyze select us.oid from sessions us where '2005-04-09 23:11:00' between us.starttimetrunc and us.finishtimetrunc; QUERY PLAN ---------------------------------------------------------------------------------------------------------------------------------------------------------------- Seq Scan on sessions us (cost=0.00..68173.04 rows=41575 width=4) (actual time=553.424..1981.695 rows=64 loops=1) Filter: (('2005-04-09 23:11:00'::timestamp without time zone >= starttimetrunc) AND ('2005-04-09 23:11:00'::timestamp without time zone <= finishtimetrunc)) Total runtime: 1981.802 ms The times in sessions go from '2005-04-04 00:00:00' to '2005-04-10 23:59:00' so both are valid times to query for, but April 10th is more towards the end. A little experimenting shows that if I go earlier than '2005-04-10 13:26:15' seq scans are being used. I was thinking this timestamp would have something to do with the histogram_bounds in pg_stats, but I cannot find a match: starttimetrunc | {"2005-04-04 00:05:00","2005-04-04 11:49:00","2005-04-04 22:03:00","2005-04-05 10:54:00","2005-04-05 21:08:00","2005-04-06 10:28:00","2005-04-07 01:57:00","2005-04-07 15:55:00","2005-04-08 10:18:00","2005-04-08 17:12:00","2005-04-10 23:57:00"} finishtimetrunc | {"2005-04-04 00:05:00.93","2005-04-04 11:53:00.989999","2005-04-04 22:35:00.38","2005-04-05 11:13:00.029999","2005-04-05 21:31:00.989999","2005-04-06 10:45:01","2005-04-07 02:08:08.25","2005-04-07 16:20:00.93","2005-04-08 10:25:00.409999","2005-04-08 17:15:00.949999","2005-04-11 02:08:19"} -- Richard van den Berg, CISSP ------------------------------------------- Trust Factory B.V. | www.dna-portal.net Bazarstraat 44a | www.trust-factory.com 2518AK The Hague | Phone: +31 70 3620684 The Netherlands | Fax : +31 70 3603009 ------------------------------------------- Have you visited our new DNA Portal? -------------------------------------------