X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.2]) by svr1.postgresql.org (Postfix) with ESMTP id DAF42D1DF80 for ; Thu, 22 Apr 2004 03:03:17 -0300 (ADT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (neptune.hub.org [200.46.204.2]) (amavisd-new, port 10024) with ESMTP id 94710-04 for ; Thu, 22 Apr 2004 03:03:10 -0300 (ADT) Received: from www.postgresql.com (www.postgresql.com [200.46.204.209]) by svr1.postgresql.org (Postfix) with ESMTP id CA090D1E9E9 for ; Thu, 22 Apr 2004 03:01:48 -0300 (ADT) Received: from sss.pgh.pa.us (sss.pgh.pa.us [66.207.139.130]) by www.postgresql.com (Postfix) with ESMTP id 67520CF6879 for ; Thu, 22 Apr 2004 01:15:15 -0300 (ADT) Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1]) by sss.pgh.pa.us (8.12.11/8.12.11) with ESMTP id i3M4FDVO019516; Thu, 22 Apr 2004 00:15:13 -0400 (EDT) To: =?ISO-8859-1?Q?Pailloncy_Jean-G=E9rard?= Cc: pgsql-performance@postgresql.org Subject: Re: 225 times slower In-reply-to: References: Comments: In-reply-to =?ISO-8859-1?Q?Pailloncy_Jean-G=E9rard?= message dated "Tue, 20 Apr 2004 19:10:50 +0200" Date: Thu, 22 Apr 2004 00:15:13 -0400 Message-ID: <19515.1082607313@sss.pgh.pa.us> From: Tom Lane X-Virus-Scanned: by amavisd-new at postgresql.org X-Spam-Status: No, hits=0.0 tagged_above=0.0 required=5.0 tests= X-Spam-Level: X-Archive-Number: 200404/373 X-Sequence-Number: 6673 =?ISO-8859-1?Q?Pailloncy_Jean-G=E9rard?= writes: > dps=# explain analyze SELECT rec_id FROM url WHERE crc32!=0 AND > crc32=419903683 AND status IN (200,304,206) ORDER BY rec_id LIMIT 1; > QUERY PLAN > ------------------------------------------------------------------------ > ---------------------------------------------------------- > Limit (cost=0.00..27.95 rows=1 width=4) (actual > time=11021.875..11021.876 rows=1 loops=1) > -> Index Scan using url_pkey on url (cost=0.00..11625.49 rows=416 > width=4) (actual time=11021.868..11021.868 rows=1 loops=1) > Filter: ((crc32 <> 0) AND (crc32 = 419903683) AND ((status = > 200) OR (status = 304) OR (status = 206))) > Total runtime: 11021.986 ms > (4 rows) > dps=# \q The planner is guessing that scanning in rec_id order will produce a matching row fairly quickly (sooner than selecting all the matching rows and sorting them would do). It's wrong in this case, but I'm not sure it could do better without very detailed cross-column statistics. Am I right to guess that the rows that match the WHERE clause are not evenly distributed in the rec_id order, but rather there are no such rows till you get well up in the ordering? regards, tom lane