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 5C793D1B502 for ; Wed, 12 Nov 2003 17:11:10 +0000 (GMT) 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 22209-06 for ; Wed, 12 Nov 2003 13:10:39 -0400 (AST) Received: from sss.pgh.pa.us (unknown [192.204.191.242]) by svr1.postgresql.org (Postfix) with ESMTP id 4F85CD1B4F2 for ; Wed, 12 Nov 2003 13:10:38 -0400 (AST) Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1]) by sss.pgh.pa.us (8.12.10/8.12.10) with ESMTP id hACHAa19010145; Wed, 12 Nov 2003 12:10:36 -0500 (EST) To: nickf@ontko.com Cc: "Manfred Koizar" , "Pgsql-Performance@Postgresql. Org" Subject: Re: Seeking help with a query that takes too long In-reply-to: References: Comments: In-reply-to "Nick Fankhauser" message dated "Wed, 12 Nov 2003 11:52:51 -0500" Date: Wed, 12 Nov 2003 12:10:35 -0500 Message-ID: <10144.1068657035@sss.pgh.pa.us> From: Tom Lane X-Virus-Scanned: by amavisd-new at postgresql.org X-Archive-Number: 200311/71 X-Sequence-Number: 4631 "Nick Fankhauser" writes: > This indicates to me that 1 isn't too shabby as an estimate if the whole > name is specified, but I'm not sure how this gets altered in the case of a > "LIKE" For a pattern like "SANDERS%", the estimate is basically a range estimate for this condition: > ((actor_full_name_uppercase >= 'SANDERS'::character varying) AND > (actor_full_name_uppercase < 'SANDERT'::character varying)) > n_distinct | -0.14701 > Question: What does it mean when n_distinct is negative? It means that the number of distinct values is estimated as a fraction of the table size, rather than an absolute number. In this case 14.7% of the table size, which is a bit off compared to the correct value of 43% (1453371/3386359), but at least it's of the right order of magnitude now ... > -> Index Scan using > actor_full_name_uppercase on actor (cost=0.00..456.88 rows=113 width=42) > (actual time=32.80..3197.28 rows=3501 loops=1) Hmm. Better, but not enough better to force a different plan choice. You might have to resort to brute force, like "set enable_nestloop=false". Just out of curiosity, what do you get if you do that? regards, tom lane