[PERFORM] POWA doesn't show queries executed

[Neto pr]

Dear all

I have successfully installed POWA (http://dalibo.github.io/powa),
including all required extensions, see the following Printscreen of its
operation of end email.

But when executing queries in psql- comand line, this queries are not
monitored by powa. I have checked that only Postgresql internal catalog
queries are shown. .
I need the Optimize Query functionality and mainly the suggestion of
indexes.
But that does not work, by clicking on the optimize query option, returns
zero suggestions.

See below that I created a scenario, with a table with a large amount of
data, to check if the tool would suggest some index, and when making a
complex query, no index is suggested.

Someone uses POWA, knows if they have to configure something so that the
queries are monitored and show suggestions ??

-- Printscreens of my environment partially
working:--

https://sites.google.com/site/eletrolareshop/repositorio/powa1.jpeg
https://sites.google.com/site/eletrolareshop/repositorio/powa2.jpeg
https://sites.google.com/site/eletrolareshop/repositorio/powa3.jpeg

---
 scenario to verify the suggestion of indices

postgres=# create table city_habitant (number_habitant text);
CREATE TABLE
postgres=# insert into  city_habitant (number_habitant) select 'São Paulo'
from (select generate_series (1, 400)) a;
INSERT 0 400
postgres=# insert into  city_habitant (number_habitant) select 'Rio de
Janeiro' from (select generate_series (1, 800)) a;
INSERT 0 800
postgres=# insert into  city_habitant (number_habitant) select 'Recife'
from (select generate_series (1, 600)) a;
INSERT 0 600
postgres=# insert into  city_habitant (number_habitant) select 'Santos'
from (select generate_series (1, 200)) a;
INSERT 0 200
postgres=# insert into  city_habitant (number_habitant) select 'Chui' from
(select generate_series (1, 6)) a;
INSERT 0 6
postgres=# SELECT number_habitant, count(number_habitant) FROM
 city_habitant GROUP BY number_habitant;
 number_habitant   |  count
---+--
 Rio de Janeiro| 800
 Recife | 600
 Santos| 200
 São Paulo  | 400
 Chui   |  6
(5 rows)


Livre
de vírus. www.avast.com
.
<#DAB4FAD8-2DD7-40BB-A1B8-4E2AA1F9FDF2>

Re: [PERFORM] Index-Advisor Tools

[Neto pr]

Thanks for reply Antony.
But from what I've read, HYPOPG only allows you to create hypothetical
indexes, so the DBA can analyze if it brings benefits.
What I would like is a tool that from a SQL Query indicates which indexes
would be recommended to decrease the response time.

Best Regards
Neto

2017-10-31 15:19 GMT-02:00 Anthony Sotolongo <asotolo...@gmail.com>:

> Hi Neto,  maybe HypoPG
> Can help you:
>
> https://github.com/dalibo/hypopg
>
> El 31 oct. 2017 2:13 PM, "Neto pr" <neto...@gmail.com> escribió:
>
>>
>> Hello All I'm researching on Index-Advisor Tools to be applied in SQL
>> queries. At first I found this: - EnterpriseDB -
>> https://www.enterprisedb.com/docs/en/9.5/asguide/EDB_Postgre
>> s_Advanced_Server_Guide.1.56.html Someone would know of other tools for
>> this purpose. I'd appreciate it if you can help me.
>>
>> Best Regards
>> Neto
>>
>

[PERFORM] Index-Advisor Tools

[Neto pr]

Hello All I'm researching on Index-Advisor Tools to be applied in SQL
queries. At first I found this: - EnterpriseDB -
https://www.enterprisedb.com/docs/en/9.5/asguide/EDB_Postgres_Advanced_Server_Guide.1.56.html
Someone would know of other tools for this purpose. I'd appreciate it if
you can help me.

Best Regards
Neto

Re: [PERFORM] blocking index creation

[Neto pr]

Dear,
With alternative, I tested the creation using concurrency
(CREATE INDEX CONCURRENCY NAME_IDX ON TABLE USING HASH (COLUMN);

from what I saw the index already appeared in the query result, because
before this, the index did not even appear in the result, only the Lineitem
table:

SELECT
  L.mode, c.relname, locktype, l.GRANTED, l.transactionid,
virtualtransaction
FROM pg_locks l, pg_class c
where c.oid = l.relation

screen result after concurrency: https://i.stack.imgur.com/htzIY.jpg

Now, I'm waiting to finish creating the index.

2017-10-11 19:54 GMT-03:00 Neto pr <neto...@gmail.com>:

> Hello all,
> I ran the query on PG_STAT_ACTIVITY table (Select * From
> pg_stat_activity),  see the complete result in this worksheet of the link
> below.
>
> https://sites.google.com/site/goissbr/img/Resultado_pg_stat_
> activity-create_index.xls
>
> The CREATE INDEX command line is identified with the orange background.
> At this point 18 hours have passed and the creation of a single index has
> not yet been completed.
> I have verified that the command is Active status, but I do not know if
> it's waiting for anything, can you help me analyze the attached output.
>
> Regards
> Neto
>
> 2017-10-11 18:08 GMT-03:00 Tomas Vondra <tomas.von...@2ndquadrant.com>:
>
>>
>>
>> On 10/11/2017 04:11 PM, Neto pr wrote:
>> >
>> > 2017-10-11 10:46 GMT-03:00 Laurenz Albe <laurenz.a...@cybertec.at
>> > <mailto:laurenz.a...@cybertec.at>>:
>> >
>> > Neto pr wrote:
>> > > When creating index on table of approximately 10GB of data, the
>> DBMS hangs (I think),
>> > > because even after waiting 10 hours there was no return of the
>> command.
>> > > It happened by creating Hash indexes and B + tree indexes.
>> > > However, for some columns, it was successfully (L_RETURNFLAG,
>> L_PARTKEY).
>> >
>> > > If someone has a hint how to speed up index creation so that it
>> completes successfully.
>> >
>> > Look if CREATE INDEX is running or waiting for a lock (check the
>> > "pg_locks" table, see if the backend consumes CPU time).
>> >
>> >
>> > In this moment now, there is an index being created in the Lineitem
>> > table (+ - 10 Gb), and apparently it is locked, since it started 7 hours
>> > ago.
>> > I've looked at the pg_locks table and look at the result, it's with
>> > "ShareLock" lock mode.
>> > Is this blocking correct? or should it be another type?
>> >
>>
>> Yes, CREATE INDEX acquire SHARE lock, see
>>
>>https://www.postgresql.org/docs/9.1/static/explicit-locking.html
>>
>> > Before creating the index, should I set the type of transaction lock?
>> What?
>>
>> Eeee? Not sure I understand. The command acquires all necessary locks
>> automatically.
>>
>> > 
>> ---
>> > SELECT
>> >   L.mode, c.relname, locktype,  l.GRANTED, l.transactionid,
>> > virtualtransaction
>> > FROM   pg_locks l, pg_class   c
>> > where  c.oid = l.relation
>> >
>> > -- RESULT
>> > --
>> > AccessShareLock   pg_class_tblspc_relfilenode_index
>>  relationTRUE
>> > (null)3/71
>> > AccessShareLock   pg_class_relname_nsp_index  relation
>> TRUE(null)  3/71
>> > AccessShareLock   pg_class_oid_index  relationTRUE
>> (null)  3/71
>> > AccessShareLock   pg_classrelationTRUE(null)
>> 3/71
>> > AccessShareLock   pg_locksrelationTRUE(null)
>> 3/71
>> > ShareLock lineitemrelationTRUE(null)  21/3769
>> >
>> >
>>
>> Well, we see something is holding a SHARE lock on the "lineitem" table,
>> but we don't really know what the session is doing.
>>
>> There's a PID in the pg_locks table, you can use it to lookup the
>> session in pg_stat_activity which includes the query (and also "state"
>> column that will tell you if it's active or waiting for a lock.
>>
>> regards
>>
>> --
>> Tomas Vondra  http://www.2ndQuadrant.com
>> PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
>>
>
>

Re: [PERFORM] blocking index creation

[Neto pr]

Hello all,
I ran the query on PG_STAT_ACTIVITY table (Select * From
pg_stat_activity),  see the complete result in this worksheet of the link
below.

https://sites.google.com/site/goissbr/img/Resultado_pg_stat_activity-create_index.xls

The CREATE INDEX command line is identified with the orange background.
At this point 18 hours have passed and the creation of a single index has
not yet been completed.
I have verified that the command is Active status, but I do not know if
it's waiting for anything, can you help me analyze the attached output.

Regards
Neto

2017-10-11 18:08 GMT-03:00 Tomas Vondra <tomas.von...@2ndquadrant.com>:

>
>
> On 10/11/2017 04:11 PM, Neto pr wrote:
> >
> > 2017-10-11 10:46 GMT-03:00 Laurenz Albe <laurenz.a...@cybertec.at
> > <mailto:laurenz.a...@cybertec.at>>:
> >
> > Neto pr wrote:
> > > When creating index on table of approximately 10GB of data, the
> DBMS hangs (I think),
> > > because even after waiting 10 hours there was no return of the
> command.
> > > It happened by creating Hash indexes and B + tree indexes.
> > > However, for some columns, it was successfully (L_RETURNFLAG,
> L_PARTKEY).
> >
> > > If someone has a hint how to speed up index creation so that it
> completes successfully.
> >
> > Look if CREATE INDEX is running or waiting for a lock (check the
> > "pg_locks" table, see if the backend consumes CPU time).
> >
> >
> > In this moment now, there is an index being created in the Lineitem
> > table (+ - 10 Gb), and apparently it is locked, since it started 7 hours
> > ago.
> > I've looked at the pg_locks table and look at the result, it's with
> > "ShareLock" lock mode.
> > Is this blocking correct? or should it be another type?
> >
>
> Yes, CREATE INDEX acquire SHARE lock, see
>
>https://www.postgresql.org/docs/9.1/static/explicit-locking.html
>
> > Before creating the index, should I set the type of transaction lock?
> What?
>
> Eeee? Not sure I understand. The command acquires all necessary locks
> automatically.
>
> > 
> ---
> > SELECT
> >   L.mode, c.relname, locktype,  l.GRANTED, l.transactionid,
> > virtualtransaction
> > FROM   pg_locks l, pg_class   c
> > where  c.oid = l.relation
> >
> > -- RESULT
> > --
> > AccessShareLock   pg_class_tblspc_relfilenode_index   relation
>   TRUE
> > (null)3/71
> > AccessShareLock   pg_class_relname_nsp_index  relation
> TRUE(null)  3/71
> > AccessShareLock   pg_class_oid_index  relationTRUE
> (null)  3/71
> > AccessShareLock   pg_classrelationTRUE(null)
> 3/71
> > AccessShareLock   pg_locksrelationTRUE(null)
> 3/71
> > ShareLock lineitemrelationTRUE(null)  21/3769
> >
> >
>
> Well, we see something is holding a SHARE lock on the "lineitem" table,
> but we don't really know what the session is doing.
>
> There's a PID in the pg_locks table, you can use it to lookup the
> session in pg_stat_activity which includes the query (and also "state"
> column that will tell you if it's active or waiting for a lock.
>
> regards
>
> --
> Tomas Vondra  http://www.2ndQuadrant.com
> PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
>

Re: [PERFORM] blocking index creation

[Neto pr]

2017-10-11 10:46 GMT-03:00 Laurenz Albe <laurenz.a...@cybertec.at>:

> Neto pr wrote:
> > When creating index on table of approximately 10GB of data, the DBMS
> hangs (I think),
> > because even after waiting 10 hours there was no return of the command.
> > It happened by creating Hash indexes and B + tree indexes.
> > However, for some columns, it was successfully (L_RETURNFLAG, L_PARTKEY).
>
> > If someone has a hint how to speed up index creation so that it
> completes successfully.
>
> Look if CREATE INDEX is running or waiting for a lock (check the
> "pg_locks" table, see if the backend consumes CPU time).
>
>
In this moment now, there is an index being created in the Lineitem table
(+ - 10 Gb), and apparently it is locked, since it started 7 hours ago.
I've looked at the pg_locks table and look at the result, it's with
"ShareLock" lock mode.
Is this blocking correct? or should it be another type?

Before creating the index, should I set the type of transaction lock? What?
---
SELECT
  L.mode, c.relname, locktype,  l.GRANTED, l.transactionid,
virtualtransaction
FROM   pg_locks l, pg_class   c
where  c.oid = l.relation

-- RESULT
--
AccessShareLock pg_class_tblspc_relfilenode_index relation TRUE (null) 3/71
AccessShareLock pg_class_relname_nsp_index relation TRUE (null) 3/71
AccessShareLock pg_class_oid_index relation TRUE (null) 3/71
AccessShareLock pg_class relation TRUE (null) 3/71
AccessShareLock pg_locks relation TRUE (null) 3/71
ShareLock lineitem relation TRUE (null) 21/3769

> Maybe there is a long-running transaction that blocks the
> ACCESS EXCLUSIVE lock required.  It could also be a prepared
> transaction.
>
> Yours,
> Laurenz Albe
>

Best Regards
Neto

[PERFORM] blocking index creation

[Neto pr]

Hello all,

My scenario is: postgresql 10, Processor Xeon 2.8GHz / 4-core- 8gb Ram, OS
Debian 8.

When creating index on table of approximately 10GB of data, the DBMS hangs
(I think), because even after waiting 10 hours there was no return of the
command. It happened by creating Hash indexes and B + tree indexes.
However, for some columns, it was successfully (L_RETURNFLAG, L_PARTKEY).
The data environment is the LINEITEM table (TPC-H benchmark) of link 1
below. The
columns/indexes that caught the creation were: * Hash Index in column:
L_TAX * Btree Index in column: L_RECEIPTDATE.

If someone has a hint how to speed up index creation so that it completes
successfully. I know that PostgreSQL 10 has some parallelism features and
since my server is dedicated only to the DBMS, do I change the parameters:
force_parallel_mode, max_parallel_workers_per_gather could speed up index
creation on large tables? Any tip is welcome.

DDL comand :
L_ORDERKEY BIGINT NOT NULL, - references O_ORDERKEY
L_PARTKEY BIGINT NOT NULL, - references P_PARTKEY (compound fk to PARTSUPP)
L_SUPPKEY BIGINT NOT NULL, - references S_SUPPKEY (compound fk to PARTSUPP)
L_LINENUMBER INTEGER,
L_QUANTITY DECIMAL,
L_EXTENDEDPRICE DECIMAL,
L_DISCOUNT DECIMAL,
L_TAX DECIMAL,
L_RETURNFLAG CHAR (1),
L_LINESTATUS CHAR (1),
L_SHIPDATE DATE,
L_COMMITDATE DATE,
L_RECEIPTDATE DATE,
L_SHIPINSTRUCT CHAR (25),
L_SHIPMODE CHAR (10),
L_COMMENT VARCHAR (44),PRIMARY KEY (L_ORDERKEY, L_LINENUMBER)

1- http://kejser.org/wp-content/uploads/2014/06/image_thumb2.png

best Regards

Neto

Re: [PERFORM] Pageinspect bt_metap help

[Neto pr]

Very interesting information.
See if I'm right, so for performance purposes, would it be better to
consider the columns: fast_root and fast_level instead of the root and
level columns?

I have read that even deleting records the B-tree tree is not rebuilt, so
it does not cause overhead in dbms, and can have null pointers.

In my example, the values ​​of fast_root, fast_root are equal to root,
level, I believe that due to the newly created index and no delete
operations occurred in the table.

Best Regards
Neto

2017-09-17 18:59 GMT-03:00 Peter Geoghegan <p...@bowt.ie>:

> On Sun, Sep 17, 2017 at 2:52 PM, Neto pr <neto...@gmail.com> wrote:
> > I am using Postgresql extension pageinspect.
> >
> > Could someone tell me the meaning of these columns: magic, version, root,
> > level, fastroot, fastlevel of the bt_metap function.
> >
> > This information is not presents in the documentation.
>
> A magic number distinguishes the meta-page as a B-Tree meta-page. A
> version number is used for each major incompatible revision of the
> B-Tree code (these are very infrequent).
>
> The fast root can differ from the true root following a deletion
> pattern that leaves a "skinny index". The implementation can never
> remove a level, essentially because it's optimized for concurrency,
> though it can have a fast root, to just skip levels. This happens to
> levels that no longer contain any distinguishing information in their
> single internal page.
>
> I imagine that in practice the large majority of B-Trees never have a
> true root that differs from its fast root - you see this with repeated
> large range deletions. Probably nothing to worry about.
>
> > The height of the b-tree (position of node farthest from root to leaf),
> is
> > the column Level?
>
> Yes.
>
> If you want to learn more about the B-Tree code, I suggest that you
> start by looking at the code for contrib/amcheck.
>
> --
> Peter Geoghegan
>

[PERFORM] Pageinspect bt_metap help

[Neto pr]

Hello All

I am using Postgresql extension pageinspect.

Could someone tell me the meaning of these columns: magic, version, root,
level, fastroot, fastlevel of the bt_metap function.

This information is not presents in the documentation.

The height of the b-tree (position of node farthest from root to leaf), is
the column Level?

See below a return query that I ran on an index called
idx_l_shipmodelineitem000

--
postgres # SELECT * FROM bt_metap ('idx_l_shipmodelineitem000');
postgres # magic  | version  | root | level | fastroot | fastlevel
postgres # 340322  | 2  | 41827 | 3   | 41827   | 3

Best regards
Neto

Re: [PERFORM] Explain Analyze - actual time in loops

[Neto pr]

Thanks for reply Tom and Igor.

Just only more information:

I need to know the height of a B-tree index (level of the leaf node
farthest from the root).

I tried to find this data in PG_INDEXES and PG_CLASS views, but I did not
find it.
Does anyone know if Postgresql stores this information, referring to the
height of the index tree?

Regards


2017-09-08 6:44 GMT-07:00 Tom Lane <t...@sss.pgh.pa.us>:

> Neto pr <neto...@gmail.com> writes:
> > After analyzing, I saw that in some places of the plan, it is being used
> > Parallelism. Does this explain why the final value spent (in minutes) to
> go
> > through the index (184 minutes) is greater than the total query time (66
> > minutes)?
>
> I was just about to ask you about that.  If this is under a Gather node,
> I believe that the numbers include time expended in all processes.
> So if you had three or more workers these results would make sense.
>
> regards, tom lane
>

Re: [PERFORM] Explain Analyze - actual time in loops

[Neto pr]

Igor,

You're right, I confused the radix character.

But even so the result is approximate to the previous message, 182 minutes, see
below:

419.113 / 1000 = 0.41 seconds * 26469 (loops) = 11093.50 seconds or 184
minutes

After analyzing, I saw that in some places of the plan, it is being used
Parallelism. Does this explain why the final value spent (in minutes) to go
through the index (184 minutes) is greater than the total query time (66
minutes)?

Regards
Neto

2017-09-08 5:46 GMT-07:00 Igor Neyman <iney...@perceptron.com>:

> *From:* pgsql-performance-ow...@postgresql.org [mailto:pgsql-performance-
> ow...@postgresql.org] *On Behalf Of *Neto pr
> *Sent:* Thursday, September 07, 2017 11:17 PM
> *To:* pgsql-performance@postgresql.org
> *Subject:* [PERFORM] Explain Analyze - actual time in loops
>
>
>
> …
>
>  ###
>-> Index Scan using idx_l_partkeylineitem000x on lineitem (cost =
> 0.57..97.65 rows = 26 width = 36)
>   (current time = 23.615..419.113 rows = 30 loops = 26469)
>   Index Cond: (l_partkey = part.p_partkey)
> ## #
> According to the documentation, one should multiply the Actual Time by the
> number of Loops.
> That is: 419113 ms -> 419113/1000/60 = 6.9 minutes * 26469 (loops) = 182.6
> minutes.
>
> But how does this stretch take 182.6 minutes, if the entire query ran in
> 66 minutes?
>
> …….
> thank you and best regards
> [] 's Neto
>
> Neto,
>
> The time you see there is in ms, so the point (‘.’) you see is the digital
> point.
>
> So, it is 419.113ms or a little less than half a second (0.419sec).
>
> Igor Neyman
>

[PERFORM] Explain Analyze - actual time in loops

[Neto pr]

Dear,
I'm trying to interpret an Explain Analyze, but I did not understand this:

-> According to the Postgresql documentation at: https://www.postgresql.org/
docs/9.6/static/using-explain.html

" the loops value reports the total number of executions of the node, and
the actual time and rows values shown are averages per-execution.
 Multiply by the loops value to get the total time actually spent in
the node"

But look at this case, in which the total query time was 66 minutes.
(Explain Analyze complete and Query at this link: https://goo.gl/Kp45fu )

What interests me is this section:

 ###
   -> Index Scan using idx_l_partkeylineitem000x on lineitem (cost =
0.57..97.65 rows = 26 width = 36)
  (current time = 23.615..419.113 rows = 30 loops = 26469)
  Index Cond: (l_partkey = part.p_partkey)
## #
According to the documentation, one should multiply the Actual Time by the
number of Loops.
That is: 419113 ms -> 419113/1000/60 = 6.9 minutes * 26469 (loops) = 182.6
minutes.

But how does this stretch take 182.6 minutes, if the entire query ran in 66
minutes?

Of course I'm making a miscalculation, but if anyone can give me a hint as
to how I would calculate this time.
What I need to know is the time spent go through the
idx_l_partkeylineitem000x index, remembering that I did an Explain Analyze
which is theoretically the actual time spent and not an estimate
as happens with the simple Explain .

thank you and best regards
[] 's Neto

Re: [PERFORM] Execution plan analysis

[Neto pr]

2017-08-25 5:31 GMT-03:00 Neto pr <neto...@gmail.com>:
> Dear all
>
> Someone help me analyze the execution plans below, is the  query 12 of
> TPC-H benchmark [1].
> I need to find out why the query without index runs faster (7 times)
> than with index, although the costs are smaller (see table).
> I have other cases that happened in the same situation. The server
> parameters have been set with PGTUNE. I use postgresql version 9.6.4
> on Debian 8 OS with 4 GB memory.
>
> Query|Index(yes/no) |Time Spend|Cost Total
> ===
> 12   Yes   00:08:58  2710805.51
> 12No00:01:42 3365996.34
>
>
> -   Explain Analyze  Query 12  WITH INDEX
> 
> Sort  (cost=2710805.51..2710805.51 rows=1 width=27) (actual
> time=537713.672..537713.672 rows=2 loops=1)
>   Sort Key: lineitem.l_shipmode
> Sort Method:  quicksort  Memory: 25kB
>   ->  HashAggregate  (cost=2710805.47..2710805.50 rows=1 width=27)
> (actual time=537713.597..537713.598 rows=2 loops=1)
>   ->  Merge Join  (cost=1994471.69..2708777.28 rows=270426
> width=27) (actual time=510717.977..536818.802 rows=311208 loops=1)
>   Merge Cond: (orders.o_orderkey = lineitem.l_orderkey)
> ->  Index Scan using orders_pkey on orders
> (cost=0.00..672772.57 rows=1545 width=20) (actual
> time=0.019..20898.325 rows=1472 loops=1)
>   ->  Sort  (cost=1994455.40..1995131.47
> rows=270426 width=19) (actual time=510690.114..510915.678 rows=311208
> loops=1)
>  Sort Key: lineitem.l_orderkey
> Sort Method:  external sort  Disk: 11568kB
>  ->  Bitmap Heap Scan on
> lineitem  (cost=336295.10..1970056.39 rows=270426 width=19) (actual
> time=419620.817..509685.421 rows=311208 loops=1)
>Recheck Cond:
> (l_shipmode = ANY (_{TRUCK,AIR}_::bpchar[]))
> Filter:
> ((l_commitdate < l_receiptdate) AND (l_shipdate < l_commitdate) AND
> (l_receiptdate >= _1997-01-01_::date) AND (l_receiptdate < _1998-01-01
> 00:00:00_::timestamp without time zone))
> ->  Bitmap
> Index Scan on idx_l_shipmodelineitem000  (cost=0.00..336227.49
> rows=15942635 width=0) (actual time=419437.172..419437.172
> rows=17133713 loops=1)
>   Index
> Cond: (l_shipmode = ANY (_{TRUCK,AIR}_::bpchar[]))
>
> Total runtime: 537728.848 ms
>
>
> -  Explain Analyze Query 12  WITHOUT INDEX
> 
> Sort  (cost=3365996.33..3365996.34 rows=1 width=27) (actual
> time=101850.883..101850.884 rows=2 loops=1)
>   Sort Key: lineitem.l_shipmode  Sort Method:  quicksort  Memory: 25kB
> ->  HashAggregate  (cost=3365996.30..3365996.32 rows=1 width=27)
> (actual time=101850.798..101850.800 rows=2 loops=1)
> ->  Merge Join  (cost=2649608.28..3363936.68 rows=274616
> width=27) (actual time=75497.181..100938.830 rows=311208 loops=1)
>  Merge Cond: (orders.o_orderkey = lineitem.l_orderkey)
>  ->  Index Scan using orders_pkey on orders
> (cost=0.00..672771.90 rows=1500 width=20) (actual
> time=0.020..20272.828 rows=1472 loops=1)
>   ->  Sort  (cost=2649545.68..2650232.22
> rows=274616 width=19) (actual time=75364.450..75618.772 rows=311208
> loops=1)
> Sort Key: lineitem.l_orderkey
> Sort Method:  external sort
> Disk: 11568kB
>->  Seq Scan on lineitem
> (cost=0.00..2624738.17 rows=274616 width=19) (actual
> time=0.839..74391.087 rows=311208 loops=1)
>  Filter: ((l_shipmode
> = ANY (_{TRUCK,AIR}_::bpchar[])) AND (l_commitdate < l_receiptdate)
> AND (l_shipdate < l_commitdate) AND (l_receiptdate >=
> _1997-01-01_::date) AND (l_receiptdate < _1998-01-01
> 00:00:00_::timestamp without time zone))
>Total runtime:
> 101865.253 ms
>
>  -=-- SQL query 12 --
>   select
> l_shipmode,
> sum(case
> when o_orderpriority = '1-URGENT'
> or o_orderpriority = '2-HIGH'
> then 1
> else 0
> end) as high_line_count,
> sum(case
> when o_orderpriority <> '1-

[PERFORM] Execution plan analysis

[Neto pr]

Dear all

Someone help me analyze the execution plans below, is the  query 12 of
TPC-H benchmark [1].
I need to find out why the query without index runs faster (7 times)
than with index, although the costs are smaller (see table).
I have other cases that happened in the same situation. The server
parameters have been set with PGTUNE. I use postgresql version 9.6.4
on Debian 8 OS with 4 GB memory.

Query|Index(yes/no) |Time Spend|Cost Total
===
12   Yes   00:08:58  2710805.51
12No00:01:42 3365996.34


-   Explain Analyze  Query 12  WITH INDEX

Sort  (cost=2710805.51..2710805.51 rows=1 width=27) (actual
time=537713.672..537713.672 rows=2 loops=1)
  Sort Key: lineitem.l_shipmode
Sort Method:  quicksort  Memory: 25kB
  ->  HashAggregate  (cost=2710805.47..2710805.50 rows=1 width=27)
(actual time=537713.597..537713.598 rows=2 loops=1)
  ->  Merge Join  (cost=1994471.69..2708777.28 rows=270426
width=27) (actual time=510717.977..536818.802 rows=311208 loops=1)
  Merge Cond: (orders.o_orderkey = lineitem.l_orderkey)
->  Index Scan using orders_pkey on orders
(cost=0.00..672772.57 rows=1545 width=20) (actual
time=0.019..20898.325 rows=1472 loops=1)
  ->  Sort  (cost=1994455.40..1995131.47
rows=270426 width=19) (actual time=510690.114..510915.678 rows=311208
loops=1)
 Sort Key: lineitem.l_orderkey
Sort Method:  external sort  Disk: 11568kB
 ->  Bitmap Heap Scan on
lineitem  (cost=336295.10..1970056.39 rows=270426 width=19) (actual
time=419620.817..509685.421 rows=311208 loops=1)
   Recheck Cond:
(l_shipmode = ANY (_{TRUCK,AIR}_::bpchar[]))
Filter:
((l_commitdate < l_receiptdate) AND (l_shipdate < l_commitdate) AND
(l_receiptdate >= _1997-01-01_::date) AND (l_receiptdate < _1998-01-01
00:00:00_::timestamp without time zone))
->  Bitmap
Index Scan on idx_l_shipmodelineitem000  (cost=0.00..336227.49
rows=15942635 width=0) (actual time=419437.172..419437.172
rows=17133713 loops=1)
  Index
Cond: (l_shipmode = ANY (_{TRUCK,AIR}_::bpchar[]))

Total runtime: 537728.848 ms


-  Explain Analyze Query 12  WITHOUT INDEX

Sort  (cost=3365996.33..3365996.34 rows=1 width=27) (actual
time=101850.883..101850.884 rows=2 loops=1)
  Sort Key: lineitem.l_shipmode  Sort Method:  quicksort  Memory: 25kB
->  HashAggregate  (cost=3365996.30..3365996.32 rows=1 width=27)
(actual time=101850.798..101850.800 rows=2 loops=1)
->  Merge Join  (cost=2649608.28..3363936.68 rows=274616
width=27) (actual time=75497.181..100938.830 rows=311208 loops=1)
 Merge Cond: (orders.o_orderkey = lineitem.l_orderkey)
 ->  Index Scan using orders_pkey on orders
(cost=0.00..672771.90 rows=1500 width=20) (actual
time=0.020..20272.828 rows=1472 loops=1)
  ->  Sort  (cost=2649545.68..2650232.22
rows=274616 width=19) (actual time=75364.450..75618.772 rows=311208
loops=1)
Sort Key: lineitem.l_orderkey
Sort Method:  external sort
Disk: 11568kB
   ->  Seq Scan on lineitem
(cost=0.00..2624738.17 rows=274616 width=19) (actual
time=0.839..74391.087 rows=311208 loops=1)
 Filter: ((l_shipmode
= ANY (_{TRUCK,AIR}_::bpchar[])) AND (l_commitdate < l_receiptdate)
AND (l_shipdate < l_commitdate) AND (l_receiptdate >=
_1997-01-01_::date) AND (l_receiptdate < _1998-01-01
00:00:00_::timestamp without time zone))
   Total runtime:
101865.253 ms

 -=-- SQL query 12 --
  select
l_shipmode,
sum(case
when o_orderpriority = '1-URGENT'
or o_orderpriority = '2-HIGH'
then 1
else 0
end) as high_line_count,
sum(case
when o_orderpriority <> '1-URGENT'
and o_orderpriority <> '2-HIGH'
then 1
else 0
end) as low_line_count
from
orders,
lineitem
where
o_orderkey = l_orderkey
and l_shipmode in ('TRUCK', 'AIR')
and l_commitdate < l_receiptdate
and l_shipdate < l_commitdate
and l_receiptdate >= date '1997-01-01'
and l_receiptdate < date '1997-01-01' + interval '1' year
group by
l_shipmode
order by
l_shipmode


-- 
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription: