Thread: help understanding explain output

help understanding explain output

From

Luca Ferrari

Date:

15 February 2011, 21:43:16

Hello,
I've got a doubt about partial indexes and the path chosen by the optimizer.
Consider this simple scenario:

CREATE TABLE p( pk serial NOT NULL , val2 text, val1 text, b boolean, PRIMARY
KEY (pk) );
INSERT INTO p(pk, val1, val2, b) VALUES( generate_series(1,1000000), 'val1b',
'val2b', true );
INSERT INTO p(pk, val1, val2, b) VALUES( generate_series(1000001,2000000),
'val1Notb', 'val2Notb', false );
CREATE INDEX i_p_b ON p (b) WHERE b = true;
ANALYZE p;

So I create a table with 2-million rows, the first million with b = true and
the second one with b = false.
Now doing an explain for a query that selects only on the b attribute I got:

EXPLAIN SELECT * FROM p WHERE b = false;
                         QUERY PLAN
------------------------------------------------------------
 Seq Scan on p  (cost=0.00..34706.00 rows=1000133 width=28)
   Filter: (NOT b)


So a sequential scan. I know that the optimizer will not consider an index if
it is not filtering, but I don't understand exactly why in this case. In fact,
considering that the above query could remove the first half data pages (where
b = true), and considering that:

SELECT reltype, relval1, relpages, reltuples
FROM pg_class WHERE relval1 IN ('p', 'i_p_b');
 reltype | relval1  | relpages | reltuples
---------+----------+----------+-----------
  615079 | p        |    14706 |     2e+06
       0 | i_p_b |     2745 |    999867

The sequential access requires 14706 pages, while using the index for filtering
almost the half of those, we've got 2745 + 7353 = around 10000 pages.
I've tried to change the index type to an hash, but the situation did not
change. Even with enable_seqscan = off the above query is executed
sequentially, but with a different initial cost:


EXPLAIN SELECT * FROM p WHERE b = false;
                                 QUERY PLAN
----------------------------------------------------------------------------
 Seq Scan on p  (cost=10000000000.00..10000034706.00 rows=1000133 width=28)
   Filter: (NOT b)


And here comes the second doubt: since in both cases the planner is doing a
sequential access, why the first case has an initial cost = 0 and this one has
a cost of 1 million?
I'm getting lost here, I need some hint to understand what is happening.

I'm running
PostgreSQL 9.0.2 on x86_64-unknown-linux-gnu, compiled by GCC gcc (GCC) 4.1.2
20080704 (Red Hat 4.1.2-46), 64-bit


Thanks,
Luca

Re: help understanding explain output

From

Guillaume Lelarge

Date:

15 February 2011, 22:05:28

Le 15/02/2011 15:49, Luca Ferrari a écrit :
> Hello,
> I've got a doubt about partial indexes and the path chosen by the optimizer.
> Consider this simple scenario:
>
> CREATE TABLE p( pk serial NOT NULL , val2 text, val1 text, b boolean, PRIMARY
> KEY (pk) );
> INSERT INTO p(pk, val1, val2, b) VALUES( generate_series(1,1000000), 'val1b',
> 'val2b', true );
> INSERT INTO p(pk, val1, val2, b) VALUES( generate_series(1000001,2000000),
> 'val1Notb', 'val2Notb', false );
> CREATE INDEX i_p_b ON p (b) WHERE b = true;
> ANALYZE p;
>
> So I create a table with 2-million rows, the first million with b = true and
> the second one with b = false.
> Now doing an explain for a query that selects only on the b attribute I got:
>
> EXPLAIN SELECT * FROM p WHERE b = false;
>                          QUERY PLAN
> ------------------------------------------------------------
>  Seq Scan on p  (cost=0.00..34706.00 rows=1000133 width=28)
>    Filter: (NOT b)
>
>
> So a sequential scan. I know that the optimizer will not consider an index if
> it is not filtering, but I don't understand exactly why in this case. In fact,
> considering that the above query could remove the first half data pages (where
> b = true), and considering that:
>
> SELECT reltype, relval1, relpages, reltuples
> FROM pg_class WHERE relval1 IN ('p', 'i_p_b');
>  reltype | relval1  | relpages | reltuples
> ---------+----------+----------+-----------
>   615079 | p        |    14706 |     2e+06
>        0 | i_p_b |     2745 |    999867
>
> The sequential access requires 14706 pages, while using the index for filtering
> almost the half of those, we've got 2745 + 7353 = around 10000 pages.

Accessing a page in an index is way costier then accessing a page in an
table with a sequential scan. By default, random_page_cost is 4 times
seq_page_cost. So it's not really surprising that when you want to get
half the table, PostgreSQL won't use the index. You would need to have a
really selective query to make an index scan interesting to use.

> I've tried to change the index type to an hash, but the situation did not
> change. Even with enable_seqscan = off the above query is executed
> sequentially, but with a different initial cost:
>
>
> EXPLAIN SELECT * FROM p WHERE b = false;
>                                  QUERY PLAN
> ----------------------------------------------------------------------------
>  Seq Scan on p  (cost=10000000000.00..10000034706.00 rows=1000133 width=28)
>    Filter: (NOT b)
>
> And here comes the second doubt: since in both cases the planner is doing a
> sequential access, why the first case has an initial cost = 0 and this one has
> a cost of 1 million?

When you disable enable_seqscan, you actually say to the planner to add
a really big number to the estimated cost of a seq scan. Which usually
disables the use of the seqscan. Not in your case.


--
Guillaume
 http://www.postgresql.fr
 http://dalibo.com

Re: help understanding explain output

From

Chris

Date:

15 February 2011, 22:09:06

On 16/02/11 01:49, Luca Ferrari wrote:
> Hello,
> I've got a doubt about partial indexes and the path chosen by the optimizer.
> Consider this simple scenario:
>
> CREATE TABLE p( pk serial NOT NULL , val2 text, val1 text, b boolean, PRIMARY
> KEY (pk) );
> INSERT INTO p(pk, val1, val2, b) VALUES( generate_series(1,1000000), 'val1b',
> 'val2b', true );
> INSERT INTO p(pk, val1, val2, b) VALUES( generate_series(1000001,2000000),
> 'val1Notb', 'val2Notb', false );
> CREATE INDEX i_p_b ON p (b) WHERE b = true;
> ANALYZE p;
>
> So I create a table with 2-million rows, the first million with b = true and
> the second one with b = false.
> Now doing an explain for a query that selects only on the b attribute I got:
>
> EXPLAIN SELECT * FROM p WHERE b = false;
>                           QUERY PLAN
> ------------------------------------------------------------
>   Seq Scan on p  (cost=0.00..34706.00 rows=1000133 width=28)
>     Filter: (NOT b)
>
>
> So a sequential scan. I know that the optimizer will not consider an index if
> it is not filtering, but I don't understand exactly why in this case.

It is filtering, but it's not filtering enough - you're hitting 1 out of
2 rows. Postgres doesn't keep information about the data visibility in
the indexes so if it were to do an index scan, it would need to check
the index for filtering and then go back to the data to see if it's
still correct the current transaction.

So doing that double hit isn't worth it - instead it just trawls through
the data files to find the right rows.

--
Postgresql & php tutorials
http://www.designmagick.com/

Re: help understanding explain output

From

Tom Lane

Date:

15 February 2011, 22:24:26

Guillaume Lelarge <guillaume@lelarge.info> writes:
> Le 15/02/2011 15:49, Luca Ferrari a �crit :
>> So a sequential scan. I know that the optimizer will not consider an index if
>> it is not filtering, but I don't understand exactly why in this case.

> Accessing a page in an index is way costier then accessing a page in an
> table with a sequential scan. By default, random_page_cost is 4 times
> seq_page_cost. So it's not really surprising that when you want to get
> half the table, PostgreSQL won't use the index. You would need to have a
> really selective query to make an index scan interesting to use.

As a rough rule of thumb, a regular indexscan is useful when the query
selects not more than about 1% of rows, while a bitmap indexscan is
useful up to about 10% of the table.  More than that, a seqscan is the
right thing to use.  If the table's row ordering is very well correlated
with the index, or if you've reduced random_page_cost, the cutoff
percentages are higher.  But in no case is it likely to be a win to use
an index to fetch half of a table.

(BTW, in the given test case, the reason the planner isn't using the
index even with seqscan off is that it *can't*.  You got the WHERE
condition backwards.)

            regards, tom lane

Re: help understanding explain output

From

Rob Sargent

Date:

16 February 2011, 06:04:46


Luca Ferrari wrote:
> Hello,
> I've got a doubt about partial indexes and the path chosen by the optimizer.
> Consider this simple scenario:
>
> CREATE TABLE p( pk serial NOT NULL , val2 text, val1 text, b boolean, PRIMARY
> KEY (pk) );
> INSERT INTO p(pk, val1, val2, b) VALUES( generate_series(1,1000000), 'val1b',
> 'val2b', true );
> INSERT INTO p(pk, val1, val2, b) VALUES( generate_series(1000001,2000000),
> 'val1Notb', 'val2Notb', false );
> CREATE INDEX i_p_b ON p (b) WHERE b = true;
> ANALYZE p;
>
> So I create a table with 2-million rows, the first million with b = true and
> the second one with b = false.
> Now doing an explain for a query that selects only on the b attribute I got:
>
> EXPLAIN SELECT * FROM p WHERE b = false;
>                          QUERY PLAN
> ------------------------------------------------------------
>  Seq Scan on p  (cost=0.00..34706.00 rows=1000133 width=28)
>    Filter: (NOT b)
>
>
> So a sequential scan. I know that the optimizer will not consider an index if
> it is not filtering, but I don't understand exactly why in this case. In fact,
> considering that the above query could remove the first half data pages (where
> b = true), and considering that:
>
> SELECT reltype, relval1, relpages, reltuples
> FROM pg_class WHERE relval1 IN ('p', 'i_p_b');
>  reltype | relval1  | relpages | reltuples
> ---------+----------+----------+-----------
>   615079 | p        |    14706 |     2e+06
>        0 | i_p_b |     2745 |    999867
>
> The sequential access requires 14706 pages, while using the index for filtering
> almost the half of those, we've got 2745 + 7353 = around 10000 pages.
> I've tried to change the index type to an hash, but the situation did not
> change. Even with enable_seqscan = off the above query is executed
> sequentially, but with a different initial cost:
>
>
> EXPLAIN SELECT * FROM p WHERE b = false;
>                                  QUERY PLAN
> ----------------------------------------------------------------------------
>  Seq Scan on p  (cost=10000000000.00..10000034706.00 rows=1000133 width=28)
>    Filter: (NOT b)
>
>
> And here comes the second doubt: since in both cases the planner is doing a
> sequential access, why the first case has an initial cost = 0 and this one has
> a cost of 1 million?
> I'm getting lost here, I need some hint to understand what is happening.
>
> I'm running
> PostgreSQL 9.0.2 on x86_64-unknown-linux-gnu, compiled by GCC gcc (GCC) 4.1.2
> 20080704 (Red Hat 4.1.2-46), 64-bit
>
>
> Thanks,
> Luca
>
>
I don't see where you've told the engine anything about the "b" column?
Naturally a boolean can only have two values, but you haven't indexed on
column b, nor partitioned the table on the values in the "b" column. I
don't think it can just _know_ that the first n records have b=true.
What behaviour would you expect if you had alternated the inserts (or
just alternated the primary keys by assigning them even and odd values
in the inserts)?

Re: help understanding explain output

From

pasman pasmański

Date:

16 February 2011, 14:13:03

> Naturally a boolean can only have two values,


really?

------------
pasman