X-Original-To: pgsql-hackers-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id 9A431D1B1DC for ; Fri, 23 Jul 2004 05:27:00 -0300 (ADT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 99263-05 for ; Fri, 23 Jul 2004 08:27:05 +0000 (GMT) Received: from pns.mm.eutelsat.org (pns.mm.eutelsat.org [194.214.173.227]) by svr1.postgresql.org (Postfix) with ESMTP id 2F730D1B195 for ; Fri, 23 Jul 2004 05:26:56 -0300 (ADT) Received: from nts-03.mm.eutelsat.org (localhost [127.0.0.1]) by pns.mm.eutelsat.org (8.11.6/linuxconf) with ESMTP id i6N8Quv17518 for ; Fri, 23 Jul 2004 10:26:56 +0200 Received: from [127.0.0.1] (accesspoint.mm.eutelsat.org [194.214.173.4]) by nts-03.mm.eutelsat.org (8.11.6/linuxconf) with ESMTP id i6N8R2X21926 for ; Fri, 23 Jul 2004 10:27:02 +0200 Message-ID: <4100CBCC.5050308@bigfoot.com> Date: Fri, 23 Jul 2004 10:26:52 +0200 From: Gaetano Mendola User-Agent: Mozilla Thunderbird 0.7.1 (Windows/20040626) X-Accept-Language: en-us, en MIME-Version: 1.0 To: "pgsql-hackers@postgresql.org" Subject: Wrong index choosen? X-Enigmail-Version: 0.84.2.0 X-Enigmail-Supports: pgp-inline, pgp-mime Content-Type: text/plain; charset=us-ascii; format=flowed Content-Transfer-Encoding: 7bit X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.0 tagged_above=0.0 required=5.0 tests=TO_ADDRESS_EQ_REAL X-Spam-Level: X-Archive-Number: 200407/1134 X-Sequence-Number: 56695 I hall I have a query in this form: empdb=# explain analyze select * from v_past_connections where id_user = 26195 and login_time > '2004-07-21'; QUERY PLAN ----------------------------------------------------------------------------------------------------------------------------------------- Index Scan using idx_user_logs_login_time on user_logs (cost=0.00..14.10 rows=1 width=28) (actual time=66.890..198.998 rows=5 loops=1) Index Cond: (login_time > '2004-07-21 00:00:00+02'::timestamp with time zone) Filter: (id_user = 26195) Total runtime: 199.083 ms (4 rows) as you see the index on the time stamp column is used The table have indexes on both columns: empdb=# explain analyze select * from v_past_connections where login_time > '2004-07-21'; QUERY PLAN ---------------------------------------------------------------------------------------------------------------------------------------------- Index Scan using idx_user_logs_login_time on user_logs (cost=0.00..12.90 rows=481 width=28) (actual time=7.338..661.300 rows=22477 loops=1) Index Cond: (login_time > '2004-07-21 00:00:00+02'::timestamp with time zone) Total runtime: 676.472 ms (3 rows) empdb=# explain analyze select * from v_past_connections where id_user = 26195; QUERY PLAN --------------------------------------------------------------------------------------------------------------------------------------- Index Scan using idx_user_user_logs on user_logs (cost=0.00..252.47 rows=320 width=28) (actual time=4.420..100.122 rows=221 loops=1) Index Cond: (id_user = 26195) Total runtime: 100.348 ms (3 rows) The rows filtered out with both condictions are two order of magnitude differents, also the extimated rows are close to real numbers: empdb=# select count(*) from v_past_connections where id_user = 26195; count ------- 221 (1 row) empdb=# select count(*) from v_past_connections where login_time > '2004-07-21'; count ------- 22441 (1 row) why then the planner choose to do an index scan using the filter that retrieve a bigger ammount of rows ? A bug ? Regards Gaetano Mendola