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 C1D67D1B4ED for ; Wed, 12 Nov 2003 15:56:30 +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 11948-01 for ; Wed, 12 Nov 2003 11:56:02 -0400 (AST) Received: from shire.ontko.com (shire.ontko.com [199.164.165.1]) by svr1.postgresql.org (Postfix) with ESMTP id 4B67FD1B521 for ; Wed, 12 Nov 2003 11:55:58 -0400 (AST) Received: from nick (bilbo.ontko.com [199.164.165.101]) by shire.ontko.com (8.12.3/8.12.3/Debian-6.6) with SMTP id hACFtx5f015013; Wed, 12 Nov 2003 10:56:01 -0500 Reply-To: From: "Nick Fankhauser" To: "Manfred Koizar" , "Pgsql-Performance@Postgresql. Org" Subject: Re: Seeking help with a query that takes too long Date: Wed, 12 Nov 2003 10:55:48 -0500 Message-ID: MIME-Version: 1.0 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: 7bit X-Priority: 3 (Normal) X-MSMail-Priority: Normal X-Mailer: Microsoft Outlook IMO, Build 9.0.2416 (9.0.2911.0) In-Reply-To: <03i4rv8cl6bgvr8r2c77ert3nfs0glfib8@email.aon.at> X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2800.1165 Importance: Normal X-Virus-Scanned: by amavisd-new at postgresql.org X-Spam-Status: No, hits=0.1 tagged_above=0.0 required=5.0 tests=IN_REP_TO, MSGID_GOOD_EXCHANGE, QUOTED_EMAIL_TEXT, UPPERCASE_25_50 X-Spam-Level: X-Archive-Number: 200311/65 X-Sequence-Number: 4625 >(actual time=37.62..677.44 rows=3501 loops=1) ^^^^^^^^^ > Nick, can you find out why this row count estimation is so far off? It's actually correct: prod1=# select count(actor_id) from actor where actor_full_name_uppercase like 'SANDERS%'; count ------- 3501 (1 row) Of course, I merely chose "SANDERS" arbitrarily as a name that falls somewhere near the middle of the frequency range for names. SMITH or JONES would represent a worst-case, and something like KOIZAR would probably be unique. Here are the stats: prod1=# SELECT * FROM pg_stats prod1-# WHERE tablename='actor' AND attname='actor_full_name_uppercase'; -[ RECORD 1 ]-----+------------------------------------------------------------------- ---------------------------------------------------------------------------- ---------------------------------------------------------------------------- ------------------------------------------------------------- schemaname | public tablename | actor attname | actor_full_name_uppercase null_frac | 0.000333333 avg_width | 21 n_distinct | 24215 most_common_vals | {"STATE OF INDIANA","INDIANA DEPARTMENT OF REVENUE","BARTH CONS SCHOOL CORP","HOWARD COUNTY CLERK","ADVANCED RECOVERY SERVICES","STATE OF INDIANA-DEPT OF REVENUE","ALLIED COLLECTION SERVICE INC","CREDIT BUREAU OF LAPORTE","MIDWEST COLLECTION SVC INC","NCO FINANCIAL SYSTEMS INC"} most_common_freqs | {0.0153333,0.0143333,0.00433333,0.00433333,0.004,0.00366667,0.00333333,0.003 33333,0.00266667,0.00266667} histogram_bounds | {"(POE) ESTELLE, DENISE","BRIEN, LIISI","COTTRELL, CAROL","FAMILY RENTALS","HAYNES, TAMIKA","KESSLER, VICTORIA","MEFFORD, VERNON L","PHILLIPS, GERALD L","SHELTON, ANTOINETTE","TRICARICO, MELISSA SUE","ZUEHLKE, THOMAS L"} correlation | -0.00147395 I think this means that the average is 357 per actor. As you can see, the range of assignments varies from people with a single parking ticket to "State of Indiana", which is party to many thousands of cases. > BTW, there seem to be missing cases: > > -> Nested Loop (cost=0.00..2214.66 rows=2 width=115) > > (actual time=59.05..119929.71 rows=5879 loops=1) > ^^^^ > > -> Nested Loop (cost=0.00..2205.26 rows=3 width=76) > > (actual time=51.46..66089.04 rows=5882 loops=1) This is expected- We actually aggregate data from many county court databases, with varying levels of data "cleanliness". Regards, -Nick