From: Kaloyan Iliev Iliev <kaloyan@digsys.bg>
To: pgsql-performance@postgresql.org
Subject: Change query join order
Date: Fri, 08 Jan 2010 19:58:02 +0200
Message-ID: <4B47722A.4010502@digsys.bg> (raw)
Hi ,
I have a simple query with two tables.
ms_data ~ 4500000 rows
ms_commands_history ~ 500000 rows
I have done analyze and there are indexes.
My question is why the planner didn't do the index scan first on ms_data
to reduce the rows to ~ 11000 and the use the PK index on
ms_commands_history.
Now, if I red the explain correctly it first do the seq_scan on
ms_commands_history the then the index scan on ms_data.
Any Ideas?
Thanks in advance.
Kaloyan Iliev
SELECT version();
version
---------------------------------------------------------------------------------------------------------
PostgreSQL 8.4.0 on i386-portbld-freebsd7.2, compiled by GCC cc (GCC)
4.2.1 20070719 [FreeBSD], 32-bit
(1 row)
explain analyze SELECT COUNT(*) as count
FROM
ms_data AS DT,
ms_commands_history AS CH
WHERE
DT.ms_command_history_id = CH.id AND
CH.ms_device_id = 1
AND
DT.ms_value_type_id
= 1 AND
DT.meassure_date::date >= '2010-01-01' AND
DT.meassure_date::date <= '2010-01-08';
QUERY
PLAN
------------------------------------------------------------------------------------------------------------------------------------------------------------------
Aggregate (cost=88778.73..88778.74 rows=1 width=0) (actual
time=16979.109..16979.112 rows=1 loops=1)
-> Hash Join (cost=63056.45..88750.77 rows=11183 width=0) (actual
time=13774.132..16958.507 rows=11093 loops=1)
Hash Cond: (dt.ms_command_history_id = ch.id)
-> Index Scan using ms_data_meassure_date_idx on ms_data dt
(cost=0.01..23485.68 rows=11183 width=8) (actual time=58.869..2701.928
rows=11093 loops=1)
Index Cond: (((meassure_date)::date >= '2010-01-01'::date)
AND ((meassure_date)::date <= '2010-01-08'::date))
Filter: (ms_value_type_id = 1)
-> Hash (cost=55149.22..55149.22 rows=481938 width=8) (actual
time=13590.853..13590.853 rows=481040 loops=1)
-> Seq Scan on ms_commands_history ch
(cost=0.00..55149.22 rows=481938 width=8) (actual time=0.078..12321.037
rows=481040 loops=1)
Filter: (ms_device_id = 1)
Total runtime: 16979.326 ms
(10 rows)
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: kaloyan@digsys.bg
Subject: Re: Change query join order
In-Reply-To: <4B47722A.4010502@digsys.bg>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox