Received: from localhost (unknown [200.46.204.183]) by postgresql.org (Postfix) with ESMTP id 79405650EBE for ; Thu, 26 Jun 2008 02:30:28 -0300 (ADT) Received: from postgresql.org ([200.46.204.86]) by localhost (mx1.hub.org [200.46.204.183]) (amavisd-maia, port 10024) with ESMTP id 17835-04 for ; Thu, 26 Jun 2008 02:30:16 -0300 (ADT) X-Greylist: from auto-whitelisted by SQLgrey-1.7.6 Received: from relay-1.mail.nethere.net (relay-1.mail.nethere.net [66.63.128.161]) by postgresql.org (Postfix) with ESMTP id 525F7650E78 for ; Thu, 26 Jun 2008 02:30:11 -0300 (ADT) Received: from scan-1.mail.nethere.net (scan-1.mail.nethere.net [66.63.128.132]) by relay-1.mail.nethere.net (Postfix) with ESMTP id 48060289C7 for ; Wed, 25 Jun 2008 18:53:14 -0700 (PDT) Received: from mta-1.mail.nethere.net by scan-1.mail.nethere.net with LMTP id TB93k08Seg2E; Wed, 25 Jun 2008 18:53:14 -0700 (PDT) Received: from lisa-james-computer.local (wsip-68-224-174-233.sd.sd.cox.net [68.224.174.233]) by mta-1.mail.nethere.net (Postfix) with ESMTP id 18E3CF1814 for ; Wed, 25 Jun 2008 18:53:14 -0700 (PDT) Message-ID: <4862F67B.1010109@emolecules.com> Date: Wed, 25 Jun 2008 18:52:59 -0700 From: Craig James User-Agent: Thunderbird 2.0.0.14 (Macintosh/20080421) MIME-Version: 1.0 To: pgsql-performance@postgresql.org Subject: Re: RAID 10 Benchmark with different I/O schedulers References: <47FFA7FF.10801@postnewspapers.com.au> <544D07A2-EB3F-4D91-A749-0C4D5D38AB80@gmail.com> <481F992D.7070509@emolecules.com> <4820A3C2.4000307@emolecules.com> <4820C30A.8010709@emolecules.com> In-Reply-To: Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200806/156 X-Sequence-Number: 30506 This seems like a bug to me, but it shows up as a performance problem. Since the column being queried is an integer, the second query (see below) can't possibly match, yet Postgres uses a typecast, forcing a full table scan for a value that can't possibly be in the table. The application could intercept these bogus queries, but that requires building schema-specific and postgres-specific knowledge into the application (i.e. "What is the maximum legal integer for this column?"). Craig explain analyze select version_id, parent_id from version where version_id = 99999; QUERY PLAN ---------------------------------------------------------------------------------------------------------------------- Index Scan using version_pkey on version (cost=0.00..9.89 rows=1 width=8) (actual time=0.054..0.054 rows=0 loops=1) Index Cond: (version_id = 99999) Total runtime: 0.130 ms (3 rows) emol_warehouse_1=> explain analyze select version_id, parent_id from version where version_id = 999999999999999999999999999; QUERY PLAN ---------------------------------------------------------------------------------------------------------------- Seq Scan on version (cost=0.00..253431.77 rows=48393 width=8) (actual time=3135.530..3135.530 rows=0 loops=1) Filter: ((version_id)::numeric = 999999999999999999999999999::numeric) Total runtime: 3135.557 ms (3 rows) \d version Table "emol_warehouse_1.version" Column | Type | Modifiers ------------+---------+----------- version_id | integer | not null parent_id | integer | not null ... more columns Indexes: "version_pkey" PRIMARY KEY, btree (version_id)