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 507A053692 for ; Sat, 2 Apr 2005 06:59:52 +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 85003-08 for ; Sat, 2 Apr 2005 05:59:46 +0000 (GMT) Received: from priv-edtnes56.telusplanet.net (outbound01.telus.net [199.185.220.220]) by svr1.postgresql.org (Postfix) with ESMTP id 2B75952A9B for ; Sat, 2 Apr 2005 06:59:45 +0100 (BST) Received: from localhost ([199.185.220.240]) by priv-edtnes56.telusplanet.net (InterMail vM.6.01.04.00 201-2131-118-20041027) with ESMTP id <20050402055945.CINB22140.priv-edtnes56.telusplanet.net@localhost>; Fri, 1 Apr 2005 22:59:45 -0700 Received: from 64.180.225.200 ( [64.180.225.200]) as user a3a18850@192.168.200.1 by webmail.telus.net with HTTP; Fri, 1 Apr 2005 21:59:44 -0800 Message-ID: <1112421584.424e34d0d2d63@webmail.telus.net> Date: Fri, 1 Apr 2005 21:59:44 -0800 From: Mischa To: "H.J. Sanders" Cc: pgsql-performance@postgresql.org Subject: Re: date - range References: In-Reply-To: MIME-Version: 1.0 Content-Type: text/plain; charset=ISO-8859-1 Content-Transfer-Encoding: 8bit User-Agent: Internet Messaging Program (IMP) 3.1-cvs X-Originating-IP: 64.180.225.200 X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=1.082 tagged_above=0 required=5 tests=AWL, DNS_FROM_RFC_POST, FORGED_RCVD_HELO X-Spam-Level: * X-Archive-Number: 200504/27 X-Sequence-Number: 11424 Quoting "H.J. Sanders" : > > Anybody a solution for the next problem: > people can subscribe to a service for 1 or more days (upto a max. of 365). > So in the database is stored: first_date and last_date > To select which people are subscribed for a certain date (e.g. today) we use > a select like > > select ....... where first_date <= today and last_date >= today > > Whatever index we create system always does a sequential scan (which I can > understand). Has someone a smarter solution? Yep, standard SQL problem. The answer is sort of a hand-rolled GIST index. To save typing, I'm going to pretend all your dates are stored as integers. In reality, you'll probably be writing views with lots of EXTRACT(EPOCH...)'s in them, to achieve the same result. Suppose you have table People(id, first_date, last_date, ...) Each such range "fits" in some larger fixed range of 1,2,4, ... days that starts and ends on a fixed (epoch) date multiple of 1,2,4,... For example, if your range were days (1040..1080), then that fits in the 64-wide range (1024...1088]. You calculate the start and width of the range that just fits, and store that in People, too. Now, you index on (start,width). Now, when you want to query for a given "today", you have to try for all possible widths in People. Fortunately, that's darn few! The ranges up to a decade (!) will still mean only 16 different widths. A max range of one year (<512 days) means only 9 widths. You can do this with a tiny static table. Then: the query: SELECT People.* FROM People JOIN Widths ON People.start = today - today % Widths.width AND People.width = Widths.width Though this may look gross, it makes an index work where no normal BTree index would. I've used it for some really nasty data conversions of 100M-row tables. Your first name wouldn't be "Harlan", would it? :-) -- "Dreams come true, not free."