From: Jaime Casanova <systemguards@gmail.com>
To: Andy <frum@ar-sd.net>
Cc: Pandurangan R S <pandurangan.r.s@gmail.com>
Cc: pgsql-performance@postgresql.org
Subject: Re: Improving Inner Join Performance
Date: Fri, 6 Jan 2006 10:26:43 -0500
Message-ID: <c2d9e70e0601060726n5f6dfdf4p447c8ad6cec63d1e@mail.gmail.com> (raw)
In-Reply-To: <00c001c612aa$f39ed1d0$0b00a8c0@forge>
References: <001d01c6120b$0d783600$0b00a8c0@forge>
<5e744e3d0601060156y3f68ad0dn152c946254ee9fd3@mail.gmail.com>
<00c001c612aa$f39ed1d0$0b00a8c0@forge>
On 1/6/06, Andy <frum@ar-sd.net> wrote:
> At the moment: o.id_status>3 can have values only 4 and 6. The 6 is around
> 90% from the whole table. This is why seq scan is made.
>
given this if you make id_status > 3 you will never use an index
because you will be scanning 4 and 6 the only values in this field as
you say, and even if there were any other value 6 is 90% of whole
table, so an index for this will not be used...
> Now, depending on the user input the query can have more where fields. For
> example:
> SELECT count(*) FROM orders o
> INNER JOIN report r ON r.id_order=o.id
> WHERE o.id_status > 3 AND r.id_zufriden=7
>
here the planner can be more selective, and of course the query is
faster... if you will be loading data load it all then make tests...
but because your actual data the planner will always choose to scan
the entire orders table for o.id_status > 3...
--
regards,
Jaime Casanova
(DBA: DataBase Aniquilator ;)
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: systemguards@gmail.com, frum@ar-sd.net, pandurangan.r.s@gmail.com
Subject: Re: Improving Inner Join Performance
In-Reply-To: <c2d9e70e0601060726n5f6dfdf4p447c8ad6cec63d1e@mail.gmail.com>
* 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