X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (developer.postgresql.org [64.117.224.193]) by svr1.postgresql.org (Postfix) with ESMTP id AC13FB4315E for ; Wed, 9 Jul 2003 02:49:13 +0000 (GMT) Received: from svr1.postgresql.org ([64.117.224.193]) by localhost (svr1.postgresql.org [64.117.224.193]) (amavisd-new, port 10024) with ESMTP id 55871-01 for ; Tue, 8 Jul 2003 23:49:02 -0300 (ADT) Received: from megazone.bigpanda.com (megazone.bigpanda.com [63.150.15.178]) by svr1.postgresql.org (Postfix) with ESMTP id 2F077B430DB for ; Tue, 8 Jul 2003 23:49:01 -0300 (ADT) Received: by megazone.bigpanda.com (Postfix, from userid 1001) id 1C5D8D614; Tue, 8 Jul 2003 19:49:05 -0700 (PDT) Received: from localhost (localhost [127.0.0.1]) by megazone.bigpanda.com (Postfix) with ESMTP id 1218C5C0C; Tue, 8 Jul 2003 19:49:05 -0700 (PDT) Date: Tue, 8 Jul 2003 19:49:04 -0700 (PDT) From: Stephan Szabo To: Martin Foster Cc: PostgreSQL Performance Subject: Re: Efficiency of timestamps In-Reply-To: <3F0B71CC.1030106@ethereal-realms.org> Message-ID: <20030708192541.D5781-100000@megazone23.bigpanda.com> MIME-Version: 1.0 Content-Type: TEXT/PLAIN; charset=US-ASCII X-Virus-Scanned: by amavisd-new at postgresql.org X-Archive-Number: 200307/149 X-Sequence-Number: 2442 On Tue, 8 Jul 2003, Martin Foster wrote: > Stephan Szabo wrote: > > > > > > I think you might get better results with some kind of multi-column index. > > It's using the index to avoid a sort it looks like, but it's not helping > > to find the conditions. I can't remember the correct ordering, but maybe > > (posttimestamp, realmname, postidnumber). Having separate indexes on the > > fields won't help currently since only one index will get chosen for the > > scan. Also, what does explain analyze show? > > > > Hope that shed's light on the matter. > > Limit (cost=0.00..260237.32 rows=100 width=48) (actual > time=68810.26..68820.83 rows=55 loops=1) > -> Index Scan using pkpost on post (cost=0.00..3020594.00 > rows=1161 width=48) (actual time=68810.25..68820.72 rows=55 loops=1) > Filter: ((posttimestamp > (('now'::text)::timestamp(6) without > time zone - '00:10'::interval)) AND (realmname = 'Amalgam'::character > varying) AND ((postto = 'all'::character varying) OR (postto = > 'root'::character varying)) AND ((NOT (subplan)) OR (puppeteerlogin IS > NULL)) AND ((NOT (subplan)) OR (puppetname IS NULL))) > SubPlan > -> Index Scan using pkpuppetignore on puppetignore > (cost=0.00..13.31 rows=1 width=10) (actual time=0.02..0.02 rows=0 loops=55) > Index Cond: (puppeteerlogin = 'root'::character varying) > Filter: ((puppetignore = 'global'::character varying) > AND (puppetlogin = $0)) > -> Index Scan using pkpuppetignore on puppetignore > (cost=0.00..5.84 rows=1 width=15) (actual time=0.01..0.01 rows=0 loops=55) > Index Cond: ((puppeteerlogin = 'root'::character > varying) AND (puppetname = $1)) > Filter: (puppetignore = 'single'::character varying) > Total runtime: 68821.11 msec The row estimate is high. How many rows meet the various conditions and some of the combinations? And how many rows does it estimate if you do a simpler query on those with explain? I still think some variety of multi-column index to make the above index conditions would help, but you'd probably need to play with which ones help, and with the cost cut for the limit, I don't know if it'd actually get a better plan, but it may be worth trying a bunch and seeing which ones are useful and then dropping the rest.