X-Original-To: pgsql-sql-postgresql.org@localhost.postgresql.org
Received: from localhost (av.hub.org [200.46.204.144])
	by postgresql.org (Postfix) with ESMTP id 6782E9DC83F
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Fri,  3 Mar 2006 15:36:13 -0400 (AST)
Received: from postgresql.org ([200.46.204.71])
	by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024)
	with ESMTP id 40511-04
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Fri,  3 Mar 2006 15:36:10 -0400 (AST)
X-Greylist: from auto-whitelisted by SQLgrey-
Received: from outbound.internet-mail-service.net
	(outbound6.internet-mail-service.net [216.240.47.197])
	by postgresql.org (Postfix) with ESMTP id E04B39DCC75
	for <pgsql-sql@postgresql.org>; Fri,  3 Mar 2006 15:36:07 -0400 (AST)
Received: from idiom.com (idiom.com [216.240.32.1])
	by outbound.internet-mail-service.net (Postfix) with ESMTP id
	8A462240D9F
	for <pgsql-sql@postgresql.org>; Fri,  3 Mar 2006 11:36:04 -0800 (PST)
Received: from [192.168.1.109] (adsl-69-107-112-198.dsl.pltn13.pacbell.net
	[69.107.112.198])
	by idiom.com (8.12.11/8.12.11) with ESMTP id k23Ja3Lv095193
	for <pgsql-sql@postgresql.org>; Fri, 3 Mar 2006 11:36:04 -0800 (PST)
	(envelope-from bryce1@obviously.com)
Message-ID: <44089A9B.2030509@obviously.com>
Date: Fri, 03 Mar 2006 11:35:55 -0800
From: Bryce Nesbitt <bryce1@obviously.com>
User-Agent: Mozilla/5.0 (X11; U; Linux i686; en-US; rv:1.7.12) Gecko/20050921
X-Accept-Language: en-us, en
MIME-Version: 1.0
To: pgsql-sql@postgresql.org
Subject: Re: Sequential scan where Index scan expected (update)
References: <244004377.20060302124132@isb.com.pl>
	<2873.1141312882@sss.pgh.pa.us> <4407F031.6050706@obviously.com>
	<20060303124712.GC7057@phlogiston.dyndns.org>
In-Reply-To: <20060303124712.GC7057@phlogiston.dyndns.org>
Content-Type: multipart/alternative;
	boundary="------------030602000409020405020606"
X-Virus-Scanned: by amavisd-new at hub.org
X-Spam-Status: No, score=0.147 required=5 tests=[AWL=0.146,
 HTML_MESSAGE=0.001]
X-Spam-Score: 0.147
X-Spam-Level: 
X-Archive-Number: 200603/79
X-Sequence-Number: 24425

This is a multi-part message in MIME format.
--------------030602000409020405020606
Content-Type: text/plain; charset=us-ascii
Content-Transfer-Encoding: 7bit

Andrew Sullivan wrote:
>> thing_event_id=10000;
>>     
>                  ^^^^^
>
> in quotes.  The automatic int4-int8 coercion is probably your
> problem.  Also
>   
Yup that's it.  But this project uses (ugh) Hibernate.  I can't change
it.  I may have to change
from BIGINT primary keys to INT.

Also:
Any hints on the table statistics?  I turn them on, in a session, as
user postgres, but get nothing:

stage=# SHOW ALL;
              name              |    setting
--------------------------------+----------------
...
 stats_block_level              | on
 stats_command_string           | on
 stats_reset_on_server_start    | on
 stats_row_level                | on
 stats_start_collector          | on

stage=# select * from pg_statio_user_indexes;
  relid   | indexrelid | schemaname |           relname          
|           indexrelname           | idx_blks_read | idx_blks_hit
----------+------------+------------+-----------------------------+----------------------------------+---------------+--------------
 18810975 |   18811183 | public     | eg_invoice                  |
ix22f7bc70c7de2059               |             0 |            0
 18810979 |   18811184 | public     | eg_order                    |
ix522779518edf278d               |             0 |            0
 18810984 |   18811185 | public     | eg_invoice_tax              |
ix2f10773c8edf278d               |             0 |            0
 18810986 |   18811186 | public     | eg_order_line               |
ixf8331222783867cc               |             0 |            0


--------------030602000409020405020606
Content-Type: text/html; charset=us-ascii
Content-Transfer-Encoding: 7bit

<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
  <meta content="text/html;charset=ISO-8859-1" http-equiv="Content-Type">
  <title></title>
</head>
<body bgcolor="#ffffff" text="#000000">
Andrew Sullivan wrote:<br>
<blockquote cite="mid20060303124712.GC7057@phlogiston.dyndns.org"
 type="cite">
  <blockquote type="cite">
    <pre wrap="">thing_event_id=10000;
    </pre>
  </blockquote>
  <pre wrap=""><!---->                 ^^^^^

in quotes.  The automatic int4-int8 coercion is probably your
problem.  Also
  </pre>
</blockquote>
Yup that's it.&nbsp; But this project uses (ugh) Hibernate.&nbsp; I can't change
it.&nbsp; I may have to change<br>
from BIGINT primary keys to INT.<br>
<br>
Also:<br>
Any hints on the table statistics?&nbsp; I turn them on, in a session, as
user postgres, but get nothing:<br>
<br>
<tt>stage=# SHOW ALL;<br>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; name&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; |&nbsp;&nbsp;&nbsp; setting<br>
--------------------------------+----------------<br>
...<br>
&nbsp;stats_block_level&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; | on<br>
&nbsp;stats_command_string&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; | on<br>
&nbsp;stats_reset_on_server_start&nbsp;&nbsp;&nbsp; | on<br>
&nbsp;stats_row_level&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; | on<br>
&nbsp;stats_start_collector&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; | on<br>
<br>
stage=# select * from pg_statio_user_indexes;<br>
&nbsp; relid&nbsp;&nbsp; | indexrelid | schemaname |&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; relname&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
|&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; indexrelname&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; | idx_blks_read | idx_blks_hit<br>
----------+------------+------------+-----------------------------+----------------------------------+---------------+--------------<br>
&nbsp;18810975 |&nbsp;&nbsp; 18811183 | public&nbsp;&nbsp;&nbsp;&nbsp; | eg_invoice&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; |
ix22f7bc70c7de2059&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; |&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 0 |&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 0<br>
&nbsp;18810979 |&nbsp;&nbsp; 18811184 | public&nbsp;&nbsp;&nbsp;&nbsp; | eg_order&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; |
ix522779518edf278d&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; |&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 0 |&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 0<br>
&nbsp;18810984 |&nbsp;&nbsp; 18811185 | public&nbsp;&nbsp;&nbsp;&nbsp; | eg_invoice_tax&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; |
ix2f10773c8edf278d&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; |&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 0 |&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 0<br>
&nbsp;18810986 |&nbsp;&nbsp; 18811186 | public&nbsp;&nbsp;&nbsp;&nbsp; | eg_order_line&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; |
ixf8331222783867cc&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; |&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 0 |&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 0<br>
</tt><br>
</body>
</html>

--------------030602000409020405020606--