agora inbox for pgsql-performance@postgresql.org
help / color / mirror / Atom feedQuery performance
48+ messages / 29 participants
[nested] [flat]
* Query performance
@ 2004-06-28 03:26 Bill <bill@math.uchicago.edu>
2004-06-28 05:23 ` Re: Query performance Mischa Sandberg <mischa_sandberg@telus.net>
2004-06-28 09:14 ` Re: Query performance Richard Huxton <dev@archonet.com>
0 siblings, 2 replies; 48+ messages in thread
From: Bill @ 2004-06-28 03:26 UTC (permalink / raw)
To: pgsql-performance
Actually, I have some queries that are slow, however I was wondering if you
could help me write a query that is rather simple, but I, as a true database
novice, can't seem to conjure. So we have stocks, as I have previously
said, and I have a huge table which contains all of the opening and closing
prices of some stocks from each day. What I like to do, in English, for
each stock in each day is find a ratio: abs(closing-opening)/opening. Then
I would like to average all of the ratios of each day of each individual
stock together to find a final ratio for each stock, then I would like to
find the highest average, to find the best performing stock. So what query
can I use, and (as is appropriate for this group), how can it be optimized
to run the fastest?
^ permalink raw reply [nested|flat] 48+ messages in thread
* Re: Query performance
2004-06-28 03:26 Query performance Bill <bill@math.uchicago.edu>
@ 2004-06-28 05:23 ` Mischa Sandberg <mischa_sandberg@telus.net>
1 sibling, 0 replies; 48+ messages in thread
From: Mischa Sandberg @ 2004-06-28 05:23 UTC (permalink / raw)
To: pgsql-performance
Usually, when you post a request like this, you should provide something a little more concrete (the CREATE TABLE statement for that table, with
Since you didn't, I'll posit something that sounds like what you're using, and take a stab at your problem.
TABLE Prices (
stock VARCHAR(9)
,asof DATE,
,opening MONEY
,closing MONEY
,PRIMARY KEY (stock, asof)
)
SELECT stock, AVG((closing-opening)/opening) as ratio
FROM Prices
GROUP BY stock
ORDER BY ratio DESC LIMIT 10; -- top 10 best-performing stocks.
""Bill"" <bill@math.uchicago.edu> wrote in message news:BILLSA1XvpFVjCRGryW00000002@bill.fefferman.org...
Actually, I have some queries that are slow, however I was wondering if you could help me write a query that is rather simple, but I, as a true database novice, can't seem to conjure. So we have stocks, as I have previously said, and I have a huge table which contains all of the opening and closing prices of some stocks from each day. What I like to do, in English, for each stock in each day is find a ratio: abs(closing-opening)/opening. Then I would like to average all of the ratios of each day of each individual stock together to find a final ratio for each stock, then I would like to find the highest average, to find the best performing stock. So what query can I use, and (as is appropriate for this group), how can it be optimized to run the fastest?
^ permalink raw reply [nested|flat] 48+ messages in thread
* Re: Query performance
2004-06-28 03:26 Query performance Bill <bill@math.uchicago.edu>
@ 2004-06-28 09:14 ` Richard Huxton <dev@archonet.com>
2004-06-28 17:02 ` Re: Query performance Bill <bill@math.uchicago.edu>
1 sibling, 1 reply; 48+ messages in thread
From: Richard Huxton @ 2004-06-28 09:14 UTC (permalink / raw)
To: Bill <bill@math.uchicago.edu>; +Cc: pgsql-performance
Bill wrote:
> Actually, I have some queries that are slow, however I was wondering if you
> could help me write a query that is rather simple, but I, as a true database
> novice, can't seem to conjure. So we have stocks, as I have previously
> said, and I have a huge table which contains all of the opening and closing
> prices of some stocks from each day.
Schemas, Bill - show us your table definitions so people can see exactly
where they stand.
--
Richard Huxton
Archonet Ltd
^ permalink raw reply [nested|flat] 48+ messages in thread
* Re: Query performance
2004-06-28 03:26 Query performance Bill <bill@math.uchicago.edu>
2004-06-28 09:14 ` Re: Query performance Richard Huxton <dev@archonet.com>
@ 2004-06-28 17:02 ` Bill <bill@math.uchicago.edu>
2004-06-29 08:37 ` Re: Query performance Richard Huxton <dev@archonet.com>
0 siblings, 1 reply; 48+ messages in thread
From: Bill @ 2004-06-28 17:02 UTC (permalink / raw)
To: pgsql-performance
Ok....so here lies the output of oclh (i.e "\d oclh")
Table "public.oclh"
Column | Type | Modifiers
--------+-----------------------+-------------------------------
symbol | character varying(10) | not null default ''
date | date | not null default '0001-01-01'
open | numeric(12,2) | not null default '0.00'
close | numeric(12,2) | not null default '0.00'
low | numeric(12,2) | not null default '0.00'
high | numeric(12,2) | not null default '0.00'
Indexes: symbol_2_oclh_index btree (symbol, date),
symbol_oclh_index btree (symbol, date)
-----Original Message-----
From: pgsql-performance-owner@postgresql.org
[mailto:pgsql-performance-owner@postgresql.org] On Behalf Of Richard Huxton
Sent: Monday, June 28, 2004 4:14 AM
To: Bill
Cc: pgsql-performance@postgresql.org
Subject: Re: [PERFORM] Query performance
Bill wrote:
> Actually, I have some queries that are slow, however I was wondering if
you
> could help me write a query that is rather simple, but I, as a true
database
> novice, can't seem to conjure. So we have stocks, as I have previously
> said, and I have a huge table which contains all of the opening and
closing
> prices of some stocks from each day.
Schemas, Bill - show us your table definitions so people can see exactly
where they stand.
--
Richard Huxton
Archonet Ltd
---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster
^ permalink raw reply [nested|flat] 48+ messages in thread
* Re: Query performance
2004-06-28 03:26 Query performance Bill <bill@math.uchicago.edu>
2004-06-28 09:14 ` Re: Query performance Richard Huxton <dev@archonet.com>
2004-06-28 17:02 ` Re: Query performance Bill <bill@math.uchicago.edu>
@ 2004-06-29 08:37 ` Richard Huxton <dev@archonet.com>
2004-06-29 17:33 ` Re: Query performance Bill <bill@math.uchicago.edu>
0 siblings, 1 reply; 48+ messages in thread
From: Richard Huxton @ 2004-06-29 08:37 UTC (permalink / raw)
To: Bill <bill@math.uchicago.edu>; +Cc: pgsql-performance
Bill wrote:
> Ok....so here lies the output of oclh (i.e "\d oclh")
>
> Table "public.oclh"
> Column | Type | Modifiers
> --------+-----------------------+-------------------------------
> symbol | character varying(10) | not null default ''
> date | date | not null default '0001-01-01'
> open | numeric(12,2) | not null default '0.00'
> close | numeric(12,2) | not null default '0.00'
> low | numeric(12,2) | not null default '0.00'
> high | numeric(12,2) | not null default '0.00'
> Indexes: symbol_2_oclh_index btree (symbol, date),
> symbol_oclh_index btree (symbol, date)
Well, I'm not sure why the two indexes on the same columns, and I'm not
sure it makes sense to have defaults for _any_ of the columns there.
So - you want:
1. ratio = abs(closing-opening)/opening
2. average = all the ratios of each day of each stock
3. Highest average
Well, I don't know what you mean by #2, but #1 is just:
SELECT
symbol,
"date",
abs(close - open)/open AS ratio
FROM
oclh
GROUP BY
symbol, date;
I'd probably fill in a summary table with this and use that as the basis
for your further queries. Presumably from "yesterday" back, the
ratios/averages won't change.
--
Richard Huxton
Archonet Ltd
^ permalink raw reply [nested|flat] 48+ messages in thread
* Re: Query performance
2004-06-28 03:26 Query performance Bill <bill@math.uchicago.edu>
2004-06-28 09:14 ` Re: Query performance Richard Huxton <dev@archonet.com>
2004-06-28 17:02 ` Re: Query performance Bill <bill@math.uchicago.edu>
2004-06-29 08:37 ` Re: Query performance Richard Huxton <dev@archonet.com>
@ 2004-06-29 17:33 ` Bill <bill@math.uchicago.edu>
2004-06-29 19:03 ` Re: Query performance Richard Huxton <dev@archonet.com>
2004-06-29 19:51 ` Re: Query performance Bruno Wolff III <bruno@wolff.to>
0 siblings, 2 replies; 48+ messages in thread
From: Bill @ 2004-06-29 17:33 UTC (permalink / raw)
To: pgsql-performance
Ok, thanks. So let me explain the query number 2 as this is the more
difficult to write. So I have a list of stocks, this table contains the
price of all of the stocks at the open and close date. Ok, now we have a
ratio from query (1) that returns at least a very rough index of the daily
performance of a given stock, with each ratio representing the stock's
performance in one day. Now we need to average this with the same stock's
ratio every day, to get a total average for each stock contained in the
database. Now I would simply like to find a ratio like this that represents
the average of every stock in the table and simply find the greatest ratio.
Sorry about the lousy explanation before, is this a bit better?
Here is an example if needed.
Say we have a stock by the name of YYY
I know, due to query 1 that stock YYY has a abs(close-open)/open price ratio
of for example, 1.3 on Dec 1 and (for simplicity let's say we only have two
dates) and Dec 2 the ratio for YYY is 1.5. So the query averages and gets
1.4. Now it needs to do this for all of the stocks in the table and sort by
increasing ratio.
Thanks.
-----Original Message-----
From: pgsql-performance-owner@postgresql.org
[mailto:pgsql-performance-owner@postgresql.org] On Behalf Of Richard Huxton
Sent: Tuesday, June 29, 2004 3:38 AM
To: Bill
Cc: pgsql-performance@postgresql.org
Subject: Re: [PERFORM] Query performance
Bill wrote:
> Ok....so here lies the output of oclh (i.e "\d oclh")
>
> Table "public.oclh"
> Column | Type | Modifiers
> --------+-----------------------+-------------------------------
> symbol | character varying(10) | not null default ''
> date | date | not null default '0001-01-01'
> open | numeric(12,2) | not null default '0.00'
> close | numeric(12,2) | not null default '0.00'
> low | numeric(12,2) | not null default '0.00'
> high | numeric(12,2) | not null default '0.00'
> Indexes: symbol_2_oclh_index btree (symbol, date),
> symbol_oclh_index btree (symbol, date)
Well, I'm not sure why the two indexes on the same columns, and I'm not
sure it makes sense to have defaults for _any_ of the columns there.
So - you want:
1. ratio = abs(closing-opening)/opening
2. average = all the ratios of each day of each stock
3. Highest average
Well, I don't know what you mean by #2, but #1 is just:
SELECT
symbol,
"date",
abs(close - open)/open AS ratio
FROM
oclh
GROUP BY
symbol, date;
I'd probably fill in a summary table with this and use that as the basis
for your further queries. Presumably from "yesterday" back, the
ratios/averages won't change.
--
Richard Huxton
Archonet Ltd
---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org
^ permalink raw reply [nested|flat] 48+ messages in thread
* Re: Query performance
2004-06-28 03:26 Query performance Bill <bill@math.uchicago.edu>
2004-06-28 09:14 ` Re: Query performance Richard Huxton <dev@archonet.com>
2004-06-28 17:02 ` Re: Query performance Bill <bill@math.uchicago.edu>
2004-06-29 08:37 ` Re: Query performance Richard Huxton <dev@archonet.com>
2004-06-29 17:33 ` Re: Query performance Bill <bill@math.uchicago.edu>
@ 2004-06-29 19:03 ` Richard Huxton <dev@archonet.com>
1 sibling, 0 replies; 48+ messages in thread
From: Richard Huxton @ 2004-06-29 19:03 UTC (permalink / raw)
To: Bill <bill@math.uchicago.edu>; +Cc: pgsql-performance
Bill wrote:
> Ok, thanks. So let me explain the query number 2 as this is the more
> difficult to write. So I have a list of stocks, this table contains the
> price of all of the stocks at the open and close date. Ok, now we have a
> ratio from query (1) that returns at least a very rough index of the daily
> performance of a given stock, with each ratio representing the stock's
> performance in one day. Now we need to average this with the same stock's
> ratio every day, to get a total average for each stock contained in the
> database. Now I would simply like to find a ratio like this that represents
> the average of every stock in the table and simply find the greatest ratio.
> Sorry about the lousy explanation before, is this a bit better?
>
> Here is an example if needed.
>
> Say we have a stock by the name of YYY
>
> I know, due to query 1 that stock YYY has a abs(close-open)/open price ratio
> of for example, 1.3 on Dec 1 and (for simplicity let's say we only have two
> dates) and Dec 2 the ratio for YYY is 1.5. So the query averages and gets
> 1.4. Now it needs to do this for all of the stocks in the table and sort by
> increasing ratio.
Well, the simplest would be something like:
CREATE VIEW my_ratios AS SELECT ...(select details we used for #1
previously)
Query #1 then becomes:
SELECT * FROM my_ratios;
Then you could do:
SELECT
symbol,
avg(ratio) as ratio_avg
FROM
my_ratios
GROUP BY
symbol
ORDER BY
avg(ratio)
;
Now, in practice, I'd probably create a symbol_ratio table and fill that
one day at a time. Then #2,#3 would be easier.
--
Richard Huxton
Archonet Ltd
^ permalink raw reply [nested|flat] 48+ messages in thread
* Re: Query performance
2004-06-28 03:26 Query performance Bill <bill@math.uchicago.edu>
2004-06-28 09:14 ` Re: Query performance Richard Huxton <dev@archonet.com>
2004-06-28 17:02 ` Re: Query performance Bill <bill@math.uchicago.edu>
2004-06-29 08:37 ` Re: Query performance Richard Huxton <dev@archonet.com>
2004-06-29 17:33 ` Re: Query performance Bill <bill@math.uchicago.edu>
@ 2004-06-29 19:51 ` Bruno Wolff III <bruno@wolff.to>
2004-06-30 13:47 ` Re: Query performance Bill <bill@math.uchicago.edu>
1 sibling, 1 reply; 48+ messages in thread
From: Bruno Wolff III @ 2004-06-29 19:51 UTC (permalink / raw)
To: Bill <bill@math.uchicago.edu>; +Cc: pgsql-performance
On Tue, Jun 29, 2004 at 12:33:51 -0500,
Bill <bill@math.uchicago.edu> wrote:
> Ok, thanks. So let me explain the query number 2 as this is the more
> difficult to write. So I have a list of stocks, this table contains the
> price of all of the stocks at the open and close date. Ok, now we have a
> ratio from query (1) that returns at least a very rough index of the daily
> performance of a given stock, with each ratio representing the stock's
> performance in one day. Now we need to average this with the same stock's
> ratio every day, to get a total average for each stock contained in the
> database. Now I would simply like to find a ratio like this that represents
> the average of every stock in the table and simply find the greatest ratio.
> Sorry about the lousy explanation before, is this a bit better?
You can do something like:
SELECT symbol, avg((open-close)/open) GROUP BY symbol
ORDER BY avg((open-close)/open) DESC LIMIT 1;
If you aren't interested in the variance of the daily change, it seems like
you would be best off using the opening price for the first day you have
recorded for the stock and the closing price on the last day and looking
at the relative change.
^ permalink raw reply [nested|flat] 48+ messages in thread
* Re: Query performance
2004-06-28 03:26 Query performance Bill <bill@math.uchicago.edu>
2004-06-28 09:14 ` Re: Query performance Richard Huxton <dev@archonet.com>
2004-06-28 17:02 ` Re: Query performance Bill <bill@math.uchicago.edu>
2004-06-29 08:37 ` Re: Query performance Richard Huxton <dev@archonet.com>
2004-06-29 17:33 ` Re: Query performance Bill <bill@math.uchicago.edu>
2004-06-29 19:51 ` Re: Query performance Bruno Wolff III <bruno@wolff.to>
@ 2004-06-30 13:47 ` Bill <bill@math.uchicago.edu>
2004-06-30 14:27 ` Re: Query performance Rod Taylor <pg@rbt.ca>
0 siblings, 1 reply; 48+ messages in thread
From: Bill @ 2004-06-30 13:47 UTC (permalink / raw)
To: pgsql-performance
Thanks this query works for what I want. So here is an output of the
explain analyze:
QUERY
PLAN
----------------------------------------------------------------------------
------------------------------------------------------------------------
Limit (cost=2421582.59..2421582.65 rows=25 width=29) (actual
time=1985800.32..1985800.44 rows=25 loops=1)
-> Sort (cost=2421582.59..2424251.12 rows=1067414 width=29) (actual
time=1985800.31..1985800.35 rows=26 loops=1)
Sort Key: avg(((open - "close") / (open + 1::numeric)))
-> Aggregate (cost=2200163.04..2280219.09 rows=1067414 width=29)
(actual time=910291.94..1984972.93 rows=22362 loops=1)
-> Group (cost=2200163.04..2253533.74 rows=10674140
width=29) (actual time=910085.96..1105064.28 rows=10674140 loops=1)
-> Sort (cost=2200163.04..2226848.39 rows=10674140
width=29) (actual time=910085.93..988909.94 rows=10674140 loops=1)
Sort Key: symbol
-> Seq Scan on oclh (cost=0.00..228404.40
rows=10674140 width=29) (actual time=20.00..137720.61 rows=10674140 loops=1)
Total runtime: 1986748.44 msec
(9 rows)
Can I get any better performance?
Thanks.
-----Original Message-----
From: Bruno Wolff III [mailto:bruno@wolff.to]
Sent: Tuesday, June 29, 2004 2:52 PM
To: Bill
Cc: pgsql-performance@postgresql.org
Subject: Re: [PERFORM] Query performance
On Tue, Jun 29, 2004 at 12:33:51 -0500,
Bill <bill@math.uchicago.edu> wrote:
> Ok, thanks. So let me explain the query number 2 as this is the more
> difficult to write. So I have a list of stocks, this table contains the
> price of all of the stocks at the open and close date. Ok, now we have a
> ratio from query (1) that returns at least a very rough index of the daily
> performance of a given stock, with each ratio representing the stock's
> performance in one day. Now we need to average this with the same stock's
> ratio every day, to get a total average for each stock contained in the
> database. Now I would simply like to find a ratio like this that
represents
> the average of every stock in the table and simply find the greatest
ratio.
> Sorry about the lousy explanation before, is this a bit better?
You can do something like:
SELECT symbol, avg((open-close)/open) GROUP BY symbol
ORDER BY avg((open-close)/open) DESC LIMIT 1;
If you aren't interested in the variance of the daily change, it seems like
you would be best off using the opening price for the first day you have
recorded for the stock and the closing price on the last day and looking
at the relative change.
^ permalink raw reply [nested|flat] 48+ messages in thread
* Re: Query performance
2004-06-28 03:26 Query performance Bill <bill@math.uchicago.edu>
2004-06-28 09:14 ` Re: Query performance Richard Huxton <dev@archonet.com>
2004-06-28 17:02 ` Re: Query performance Bill <bill@math.uchicago.edu>
2004-06-29 08:37 ` Re: Query performance Richard Huxton <dev@archonet.com>
2004-06-29 17:33 ` Re: Query performance Bill <bill@math.uchicago.edu>
2004-06-29 19:51 ` Re: Query performance Bruno Wolff III <bruno@wolff.to>
2004-06-30 13:47 ` Re: Query performance Bill <bill@math.uchicago.edu>
@ 2004-06-30 14:27 ` Rod Taylor <pg@rbt.ca>
0 siblings, 0 replies; 48+ messages in thread
From: Rod Taylor @ 2004-06-30 14:27 UTC (permalink / raw)
To: Bill <bill@math.uchicago.edu>; +Cc: pgsql-performance
> Can I get any better performance?
You can try bumping your sort memory way up (for this query only).
Another method would be to cluster the table by the symbol column
(eliminates the expensive sort).
If you could run a very simple calculation against open & close numbers
to eliminate a majority of symbols early, that would be useful as well.
^ permalink raw reply [nested|flat] 48+ messages in thread
* Query Performance
@ 2004-08-21 00:03 Danilo Mota <dmota@nexen.com.br>
2004-08-21 01:02 ` Re: Query Performance Brad Bulger <brad@madfish.com>
0 siblings, 1 reply; 48+ messages in thread
From: Danilo Mota @ 2004-08-21 00:03 UTC (permalink / raw)
To: pgsql-performance
Hi all,
the following query is working well without the AND on WHERE clause, so
I need suggestions about how could I rewrite the query to get the same
result with less cost of time and resources.
I've already created indexes on all foreign key columns.
Thanks in advance.
Danilo Mota
========================================================================
============
SELECT
sn.notafiscalnumero,
sn.notafiscalserie,
CASE sn.notafiscaldata WHEN '00000000' THEN NULL ELSE
to_date(sn.notafiscaldata,'YYYYMMDD') END,
sn.modalidade,
rcm.pkclientemarca,
sn.notafiscalvalor/100,
sn.entrada/100,
sn.cliente
FROM r_clientemarca AS rcm
INNER JOIN r_cliente AS rc ON rc.pkcliente = rcm.fkcliente
INNER JOIN sav_cliente_lg AS sc ON sc.cpfcnpj = rc.cpfcnpj
INNER JOIN sav_nota_lg AS sn ON sn.cliente = sc.codigo
WHERE rcm.fkmarca = 1
AND sn.notafiscalnumero||sn.notafiscalserie||sn.cliente NOT IN (
SELECT numero||serie||codigo
FROM r_contrato AS rcon
WHERE savfonte = 'lg')
========================================================================
============
TABLES
------------------------------------------------------------------------
-----------------------------------------------------
r_cliente: 75820 records
r_clientemarca: 97719 records
r_contrato: 782058 records
sav_cliente_lg: 65671 records
sav_nota_lg: 297329 rcords
MY SERVER
------------------------------------------------------------------------
-----------------------------------------------------
Pentium 4 2.4 GHz
1 GB RAM
36 GB SCSI
Postgresql 7.4.2
POSTGRESQL.CONF
------------------------------------------------------------------------
-----------------------------------------------------
shared_buffers = 7800
sort_mem = 4096
checkpoint_segments = 5
effective_cache_size = 12000
cpu_operator_cost = 0.0015
stats_start_collector = false
QUERY PLAN
------------------------------------------------------------------------
-----------------------------------------------------
Hash Join (cost=27149.61..3090289650.24 rows=128765 width=4)
Hash Cond: ("outer".cliente = "inner".codigo)
-> Seq Scan on sav_nota_lg sn (cost=0.00..3090258517.99 rows=148665
width=8)
Filter: (NOT (subplan))
SubPlan
-> Seq Scan on r_contrato rcon (cost=0.00..20362.47
rows=282845 width=19)
Filter: ((savfonte)::text = 'lg'::text)
-> Hash (cost=26869.29..26869.29 rows=56880 width=4)
-> Hash Join (cost=22473.95..26869.29 rows=56880 width=4)
Hash Cond: ("outer".fkcliente = "inner".pkcliente)
-> Index Scan using ix_r_clientemarca_fkmarca on
r_clientemarca rcm (cost=0.00..2244.46 rows=65665 width=4)
Index Cond: (fkmarca = 1)
-> Hash (cost=22118.44..22118.44 rows=65672 width=8)
-> Hash Join (cost=6613.22..22118.44 rows=65672
width=8)
Hash Cond: (("outer".cpfcnpj)::text =
("inner".cpfcnpj)::text)
-> Seq Scan on r_cliente rc
(cost=0.00..12891.16 rows=75816 width=23)
-> Hash (cost=6129.71..6129.71 rows=65671
width=23)
-> Seq Scan on sav_cliente_lg sc
(cost=0.00..6129.71 rows=65671 width=23)
^ permalink raw reply [nested|flat] 48+ messages in thread
* Re: Query Performance
2004-08-21 00:03 Query Performance Danilo Mota <dmota@nexen.com.br>
@ 2004-08-21 01:02 ` Brad Bulger <brad@madfish.com>
0 siblings, 0 replies; 48+ messages in thread
From: Brad Bulger @ 2004-08-21 01:02 UTC (permalink / raw)
To: dmota@nexen.com.br; +Cc: pgsql-performance
Have you tried
AND (sn.notafiscalnumero, sn.notafiscalserie, sn.cliente) NOT IN (
SELECT numero, serie, codigo FROM r_contrato WHERE savfonte = 'lg')
or
and not exists(select true from r_contrato where savfonte = 'lg' and numero =
sn.notafiscalnumero and serie = sn.notafiscalserie and codigo = sn.cliente)
Danilo Mota wrote:
> Hi all,
>
>
>
> the following query is working well without the AND on WHERE clause, so
> I need suggestions about how could I rewrite the query to get the same
> result with less cost of time and resources.
>
>
>
> I’ve already created indexes on all foreign key columns.
>
>
>
> Thanks in advance.
>
>
>
> Danilo Mota
>
>
>
> ====================================================================================
>
> SELECT
>
> sn.notafiscalnumero,
>
> sn.notafiscalserie,
>
> CASE sn.notafiscaldata WHEN '00000000' THEN NULL ELSE
> to_date(sn.notafiscaldata,'YYYYMMDD') END,
>
> sn.modalidade,
>
> rcm.pkclientemarca,
>
> sn.notafiscalvalor/100,
>
> sn.entrada/100,
>
> sn.cliente
>
> FROM r_clientemarca AS rcm
>
> INNER JOIN r_cliente AS rc ON rc.pkcliente = rcm.fkcliente
>
> INNER JOIN sav_cliente_lg AS sc ON sc.cpfcnpj = rc.cpfcnpj
>
> INNER JOIN sav_nota_lg AS sn ON sn.cliente = sc.codigo
>
> WHERE rcm.fkmarca = 1
>
> AND sn.notafiscalnumero||sn.notafiscalserie||sn.cliente NOT IN (
> SELECT numero||serie||codigo
>
>
> FROM r_contrato AS rcon
>
>
> WHERE savfonte = 'lg')
>
>
>
> ====================================================================================
>
>
>
>
>
> TABLES
>
> -----------------------------------------------------------------------------------------------------------------------------
>
> r_cliente: 75820 records
>
> r_clientemarca: 97719 records
>
> r_contrato: 782058 records
>
> sav_cliente_lg: 65671 records
>
> sav_nota_lg: 297329 rcords
>
> MY SERVER
>
> -----------------------------------------------------------------------------------------------------------------------------
>
> Pentium 4 2.4 GHz
>
> 1 GB RAM
>
> 36 GB SCSI
>
> Postgresql 7.4.2
>
>
>
> POSTGRESQL.CONF
>
> -----------------------------------------------------------------------------------------------------------------------------
>
> shared_buffers = 7800
>
> sort_mem = 4096
>
> checkpoint_segments = 5
>
> effective_cache_size = 12000
>
> cpu_operator_cost = 0.0015
>
> stats_start_collector = false
>
>
>
> QUERY PLAN
>
> -----------------------------------------------------------------------------------------------------------------------------
>
> Hash Join (cost=27149.61..3090289650.24 rows=128765 width=4)
>
> Hash Cond: ("outer".cliente = "inner".codigo)
>
> -> Seq Scan on sav_nota_lg sn (cost=0.00..3090258517.99 rows=148665
> width=8)
>
> Filter: (NOT (subplan))
>
> SubPlan
>
> -> Seq Scan on r_contrato rcon (cost=0.00..20362.47
> rows=282845 width=19)
>
> Filter: ((savfonte)::text = 'lg'::text)
>
> -> Hash (cost=26869.29..26869.29 rows=56880 width=4)
>
> -> Hash Join (cost=22473.95..26869.29 rows=56880 width=4)
>
> Hash Cond: ("outer".fkcliente = "inner".pkcliente)
>
> -> Index Scan using ix_r_clientemarca_fkmarca on
> r_clientemarca rcm (cost=0.00..2244.46 rows=65665 width=4)
>
> Index Cond: (fkmarca = 1)
>
> -> Hash (cost=22118.44..22118.44 rows=65672 width=8)
>
> -> Hash Join (cost=6613.22..22118.44 rows=65672
> width=8)
>
> Hash Cond: (("outer".cpfcnpj)::text =
> ("inner".cpfcnpj)::text)
>
> -> Seq Scan on r_cliente rc
> (cost=0.00..12891.16 rows=75816 width=23)
>
> -> Hash (cost=6129.71..6129.71 rows=65671
> width=23)
>
> -> Seq Scan on sav_cliente_lg sc
> (cost=0.00..6129.71 rows=65671 width=23)
>
>
>
>
>
^ permalink raw reply [nested|flat] 48+ messages in thread
* Query performance
@ 2005-03-11 18:47 Lou O'Quin <loquin@talleyds.com>
2005-03-11 19:10 ` Re: Query performance Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 48+ messages in thread
From: Lou O'Quin @ 2005-03-11 18:47 UTC (permalink / raw)
To: pgsql-performance
As a test, I ran a query in the pgAdmin query tool, which returns about 15K records from a PostgreSQL v8.01 table on my Win2K server.
I ran the same query from the local server, from another PC on the same 100 mbit local network, and from a PC on a different network, over the internet.
The times for the query to run and the data to return for each of the three
locations are shown here: Local Server : 571+521 ms Local network: 1187+1266 ms Internet:14579+4016 msMy question is this: Why does the execution time for the query to run increase so much? Since the query should be running on the server, it's time should be somewhat independent of the network transport delay. (unlike the data transport time) However, it appears to actually be hypersensitive to the transport delay. The ratios of time for the data transport (assuming 1 for the local server) are:
1 : 2.43 : 7.71
whereas the query execution time ratios are:
1 : 2.08 : 25.5 (!!!)
Obviously, the transport times will be greater. But why does the execution time bloat so?
^ permalink raw reply [nested|flat] 48+ messages in thread
* Re: Query performance
2005-03-11 18:47 Query performance Lou O'Quin <loquin@talleyds.com>
@ 2005-03-11 19:10 ` Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 0 replies; 48+ messages in thread
From: Tom Lane @ 2005-03-11 19:10 UTC (permalink / raw)
To: Lou O'Quin <loquin@talleyds.com>; +Cc: pgsql-performance
"Lou O'Quin" <loquin@talleyds.com> writes:
> it appears to actually be hypersensitive to the transport delay. The =
> ratios of time for the data transport (assuming 1 for the local server) =
> are:
> 1 : 2.43 : 7.71
> whereas the query execution time ratios are:
> 1 : 2.08 : 25.5 (!!!)
How do you know that's what the data transport time is --- ie, how can
you measure that separately from the total query time?
regards, tom lane
^ permalink raw reply [nested|flat] 48+ messages in thread
* Re: Query performance
@ 2005-03-11 19:38 Lou O'Quin <loquin@talleyds.com>
2005-03-11 20:21 ` Re: Query performance Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 48+ messages in thread
From: Lou O'Quin @ 2005-03-11 19:38 UTC (permalink / raw)
To: tgl@sss.pgh.pa.us; +Cc: pgsql-performance
Hi Tom. I referenced the status line of pgAdmin. Per the pgAdmin help file:
"The status line will show how long the last query took to complete. If a dataset was returned, not only the elapsed time for server execution is displayed, but also the time to retrieve the data from the server to the Data Output page."
Lou
>>> Tom Lane <tgl@sss.pgh.pa.us> 3/11/2005 12:10 PM >>>
"Lou O'Quin" <loquin@talleyds.com> writes:
> it appears to actually be hypersensitive to the transport delay. The =
> ratios of time for the data transport (assuming 1 for the local server) =
> are:
> 1 : 2.43 : 7.71
> whereas the query execution time ratios are:
> 1 : 2.08 : 25.5 (!!!)
How do you know that's what the data transport time is --- ie, how can
you measure that separately from the total query time?
regards, tom lane
^ permalink raw reply [nested|flat] 48+ messages in thread
* Re: Query performance
2005-03-11 19:38 Re: Query performance Lou O'Quin <loquin@talleyds.com>
@ 2005-03-11 20:21 ` Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 0 replies; 48+ messages in thread
From: Tom Lane @ 2005-03-11 20:21 UTC (permalink / raw)
To: Lou O'Quin <loquin@talleyds.com>; +Cc: pgsql-performance
"Lou O'Quin" <loquin@talleyds.com> writes:
> Hi Tom. I referenced the status line of pgAdmin. Per the pgAdmin help
> file:
>
> "The status line will show how long the last query took to complete. If a
> dataset was returned, not only the elapsed time for server execution is
> displayed, but also the time to retrieve the data from the server to the
> Data Output page."
Well, you should probably ask the pgadmin boys exactly what they are
measuring. In any case, the Postgres server overlaps query execution
with result sending, so I don't think it's possible to get a pure
measurement of just one of those costs --- certainly not by looking at
it only from the client end.
BTW, one factor to consider is that if the test client machines weren't
all the same speed, that would have some impact on their ability to
absorb 15K records ...
regards, tom lane
^ permalink raw reply [nested|flat] 48+ messages in thread
* Re: Query performance
@ 2005-03-11 20:35 Lou O'Quin <loquin@talleyds.com>
0 siblings, 0 replies; 48+ messages in thread
From: Lou O'Quin @ 2005-03-11 20:35 UTC (permalink / raw)
To: tgl@sss.pgh.pa.us; +Cc: pgsql-performance
I'll post there concerning how they determine the query execution time vs. data retrieval time.
I did think about the processor/memory when choosing the machines - all three of the processors are similar. All are Pentium P4s with 512 MB memory.
the server is Win2K, P4, 2.3 gHz
the local network client is a WinXP Pro, P4, 2.2 gHz
the remote network client is WinXP Pro, P4, 1.9 gHz
Lou
>>> Tom Lane <tgl@sss.pgh.pa.us> 3/11/2005 1:21 PM >>>
"Lou O'Quin" <loquin@talleyds.com> writes:
> Hi Tom. I referenced the status line of pgAdmin. Per the pgAdmin help
> file:
>
> "The status line will show how long the last query took to complete. If a
> dataset was returned, not only the elapsed time for server execution is
> displayed, but also the time to retrieve the data from the server to the
> Data Output page."
Well, you should probably ask the pgadmin boys exactly what they are
measuring. In any case, the Postgres server overlaps query execution
with result sending, so I don't think it's possible to get a pure
measurement of just one of those costs --- certainly not by looking at
it only from the client end.
BTW, one factor to consider is that if the test client machines weren't
all the same speed, that would have some impact on their ability to
absorb 15K records ...
regards, tom lane
---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match
^ permalink raw reply [nested|flat] 48+ messages in thread
* Re: Query performance
@ 2006-05-22 23:33 ` Steinar H. Gunderson <sgunderson@bigfoot.com>
2006-05-23 07:10 ` Re: Query performance Antonio Batovanja <antonio.batovanja@humanomed.co.at>
2 siblings, 1 reply; 48+ messages in thread
From: Steinar H. Gunderson @ 2006-05-22 23:33 UTC (permalink / raw)
To: pgsql-performance
antonio.batovanja@humanomed.co.at wrote:
> The above query takes 5 seconds to execute!
>
> [...]
>
> Total runtime: 96109.571 ms
It sure doesn't look like it...
> Total runtime: 461.907 ms
>
> [...]
>
> Suddenly the query takes only 0.29 seconds!
How are you timing this, really?
/* Steinar */
--
Homepage: http://www.sesse.net/
^ permalink raw reply [nested|flat] 48+ messages in thread
* Re: Query performance
2006-05-22 23:33 ` Re: Query performance Steinar H. Gunderson <sgunderson@bigfoot.com>
@ 2006-05-23 07:10 ` Antonio Batovanja <antonio.batovanja@humanomed.co.at>
2006-05-31 01:07 ` Re: Query performance Christopher Kings-Lynne <chris.kings-lynne@calorieking.com>
0 siblings, 1 reply; 48+ messages in thread
From: Antonio Batovanja @ 2006-05-23 07:10 UTC (permalink / raw)
To: pgsql-performance
Steinar H. Gunderson wrote:
> antonio.batovanja@humanomed.co.at wrote:
>> The above query takes 5 seconds to execute!
>>
>> [...]
>>
>> Total runtime: 96109.571 ms
>
> It sure doesn't look like it...
>
>> Total runtime: 461.907 ms
>>
>> [...]
>>
>> Suddenly the query takes only 0.29 seconds!
>
> How are you timing this, really?
>
> /* Steinar */
I'm executing the queries from phpPgAdmin.
The above are for explain analyse. I was referring to the pure query
execution time.
Does anyone have an idea why the OR-query takes so long?
Any server-side tuning possibilities? I wouldn't like to change the code of
ldap's back-sql...
Toni
^ permalink raw reply [nested|flat] 48+ messages in thread
* Re: Query performance
2006-05-22 23:33 ` Re: Query performance Steinar H. Gunderson <sgunderson@bigfoot.com>
2006-05-23 07:10 ` Re: Query performance Antonio Batovanja <antonio.batovanja@humanomed.co.at>
@ 2006-05-31 01:07 ` Christopher Kings-Lynne <chris.kings-lynne@calorieking.com>
0 siblings, 0 replies; 48+ messages in thread
From: Christopher Kings-Lynne @ 2006-05-31 01:07 UTC (permalink / raw)
To: Antonio Batovanja <antonio.batovanja@humanomed.co.at>; +Cc: pgsql-performance
> I'm executing the queries from phpPgAdmin.
> The above are for explain analyse. I was referring to the pure query
> execution time.
> Does anyone have an idea why the OR-query takes so long?
> Any server-side tuning possibilities? I wouldn't like to change the code of
> ldap's back-sql...
If you're using phpPgAdmin's timings, they could be more off than the
real explain analyze timings. Make sure you're using the figure given
by explain analyze itself.
Chris
^ permalink raw reply [nested|flat] 48+ messages in thread
* Re: Query performance
@ 2006-05-28 21:56 ` Erwin Brandstetter <brsaweda@gmail.com>
2 siblings, 0 replies; 48+ messages in thread
From: Erwin Brandstetter @ 2006-05-28 21:56 UTC (permalink / raw)
To: pgsql-performance
Antonio Batovanja wrote:
> Laurenz Albe wrote:
>
>> Antonio Batovanja wrote:
>>> I'm having trouble understanding, why a specific query on a small
>>> database is taking so long...
>>>
>> Before I try to understand the execution plans:
>>
>> Have you run ANALYZE on the tables involved before you ran the query?
>
> Hi,
>
> Just to be on the safe side, I've run ANALYZE now.
> Here are the query plans for the two queries:
I suspect a misunderstanding here. What Laurenz probably meant is to run
analyze on the involved _tables_ so the statistics data is refreshed.
If the query planner runs with outdated statistics, queries may perform
very poorly. Try
vacuum full analyze yourdatabase
To fully vacuum your database and analyze all tables.
(vacuum full is extra, but can't hurt.)
http://www.postgresql.org/docs/8.1/static/sql-vacuum.html
http://www.postgresql.org/docs/8.1/static/sql-analyze.html
Regards, Erwin
^ permalink raw reply [nested|flat] 48+ messages in thread
* Re: Query performance
@ 2006-05-28 22:38 ` Erwin Brandstetter <brsaweda@gmail.com>
2 siblings, 0 replies; 48+ messages in thread
From: Erwin Brandstetter @ 2006-05-28 22:38 UTC (permalink / raw)
To: pgsql-performance
Antonio Batovanja wrote:
(...)
> 1) the slooooow query:
> EXPLAIN ANALYZE SELECT DISTINCT ldap_entries.id, organization.id,
> text('organization') AS objectClass, ldap_entries.dn AS dn FROM
> ldap_entries, organization, ldap_entry_objclasses WHERE
> organization.id=ldap_entries.keyval AND ldap_entries.oc_map_id=1 AND
> upper(ldap_entries.dn) LIKE '%DC=HUMANOMED,DC=AT' AND 1=1 OR
> (ldap_entries.id=ldap_entry_objclasses.entry_id AND
> ldap_entry_objclasses.oc_name='organization');
First, presenting your query in any readable form might be helpful if
you want the community to help you. (Hint! Hint!)
SELECT DISTINCT ldap_entries.id, organization.id,
text('organization') AS objectClass, ldap_entries.dn AS dn
FROM ldap_entries, organization, ldap_entry_objclasses
WHERE organization.id=ldap_entries.keyval
AND ldap_entries.oc_map_id=1
AND upper(ldap_entries.dn) LIKE '%DC=HUMANOMED,DC=AT'
AND 1=1
OR (ldap_entries.id=ldap_entry_objclasses.entry_id
AND ldap_entry_objclasses.oc_name='organization');
Next, you might want to use aliases to make it more readable.
SELECT DISTINCT e.id, o.id, text('organization') AS objectClass, e.dn AS dn
FROM ldap_entries AS e, organization AS o, ldap_entry_objclasses AS eo
WHERE o.id=e.keyval
AND e.oc_map_id=1
AND upper(e.dn) LIKE '%DC=HUMANOMED,DC=AT'
AND 1=1
OR (e.id=eo.entry_id
AND eo.oc_name='organization');
There are a couple redundant (nonsensical) items, syntax-wise. Let's
strip these:
SELECT DISTINCT e.id, o.id, text('organization') AS objectClass, e.dn
FROM ldap_entries AS e, organization AS o, ldap_entry_objclasses AS eo
WHERE o.id=e.keyval
AND e.oc_map_id=1
AND e.dn ILIKE '%DC=HUMANOMED,DC=AT'
OR e.id=eo.entry_id
AND eo.oc_name='organization';
And finally, I suspect the lexical precedence of AND and OR might be the
issue here.
http://www.postgresql.org/docs/8.1/static/sql-syntax.html#SQL-PRECEDENCE
Maybe that is what you really want (just guessing):
SELECT DISTINCT e.id, o.id, text('organization') AS objectClass, e.dn
FROM ldap_entries e
JOIN organization o ON o.id=e.keyval
LEFT JOIN ldap_entry_objclasses eo ON eo.entry_id=e.id
WHERE e.oc_map_id=1
AND e.dn ILIKE '%DC=HUMANOMED,DC=AT'
OR eo.oc_name='organization)';
I didn't take the time to read the rest. My appologies if I guessed wrong.
Regards, Erwin
^ permalink raw reply [nested|flat] 48+ messages in thread
* Query performance
@ 2013-06-13 07:49 K P Manoj <kpmanojpg@gmail.com>
2013-06-13 09:03 ` Re: Query performance Sergey Konoplev <gray.ru@gmail.com>
0 siblings, 1 reply; 48+ messages in thread
From: K P Manoj @ 2013-06-13 07:49 UTC (permalink / raw)
To: pgsql-performance
Hi All
One of my query treating performance issue on my production server.
Once i run query on my parent table with specific condition(hard coded
value) its uses only proper child table and its index on explain plan ,
but once i am using table conditions (instead of hard coded value), query
planner is going all the child tables, Can i know where i am worng
Postgresql version 9.2.2
Please find details below
==========================
XXX_db=> select id from xxx where d_id = '5';
id
-------
5
45
(2 rows)
XXX_db=> explain analyze SELECT * FROM xxx_parent_table WHERE id in
(5,45) and ( sts = 1 or status is null ) order by creation_time limit 40 ;
QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------------------
-
Limit (cost=12.21..12.21 rows=3 width=251) (actual time=6.585..6.585
rows=0 loops=1)
-> Sort (cost=12.21..12.21 rows=3 width=251) (actual time=6.582..6.582
rows=0 loops=1)
Sort Key: public.xxx_parent_tables.creation_time
Sort Method: quicksort Memory: 25kB
-> Result (cost=0.00..12.18 rows=3 width=251) (actual
time=6.571..6.571 rows=0 loops=1)
-> Append (cost=0.00..12.18 rows=3 width=251) (actual
time=6.569..6.569 rows=0 loops=1)
-> Seq Scan on xxx_parent_tables (cost=0.00..0.00
rows=1 width=324) (actual time=0.003..0.003 rows=0 loops=1)
Filter: ((id = ANY ('{5,45}'::bigint[])) AND
((status = 1) OR (status IS NULL)))
-> Bitmap Heap Scan on
xxx_parent_tables_table_details_ xxx_parent_tables (cost=4.52..6.53 rows=1
width=105) (actual ti
me=0.063..0.063 rows=0 loops=1)
Recheck Cond: ((status = 1) OR (status IS NULL))
Filter: (id = ANY ('{5,45}'::bigint[]))
-> BitmapOr (cost=4.52..4.52 rows=1 width=0)
(actual time=0.059..0.059 rows=0 loops=1)
-> Bitmap Index Scan on
xxx_parent_tables_table_details__status_idx (cost=0.00..2.26 rows=1
width=0)
(actual time=0.038..0.038 rows=0 loops=1)
Index Cond: (status = 1)
-> Bitmap Index Scan on
xxx_parent_tables_table_details__status_idx (cost=0.00..2.26 rows=1
width=0)
(actual time=0.019..0.019 rows=0 loops=1)
Index Cond: (status IS NULL)
-> Bitmap Heap Scan on
xxx_parent_tables_table_details_det xxx_parent_tables (cost=2.52..5.65
rows=1 width=324) (actual ti
me=6.502..6.502 rows=0 loops=1)
Recheck Cond: (id = ANY ('{5,45}'::bigint[]))
Filter: ((status = 1) OR (status IS NULL))
-> Bitmap Index Scan on
xxx_parent_tables_table_details_id_idx (cost=0.00..2.52 rows=2 width=0)
(actua
l time=6.499..6.499 rows=0 loops=1)
Index Cond: (id = ANY ('{5,45}'::bigint[]))
Total runtime: 6.823 ms
(22 rows)
XXX_db => explain analyze SELECT * FROM xxx_parent_tables WHERE cp_id
in (select id from xxx where d_id = '5') and ( status = 1 or status is null
) order by creation_time limit 40 ;
QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------------------
------------------------------------------------------------------------------------------------
Limit (cost=3.66..6067.89 rows=40 width=105) (actual
time=70479.596..70479.596 rows=0 loops=1)
-> Nested Loop Semi Join (cost=3.66..4587291.92 rows=30258 width=105)
(actual time=70479.593..70479.593 rows=0 loops=1)
Join Filter: (public.xxx_parent_tables.cp_id = cp_info.cp_id)
Rows Removed by Join Filter: 1416520
-> Merge Append (cost=3.66..4565956.68 rows=711059 width=105)
(actual time=67225.964..69635.016 rows=708260 loops=1)
Sort Key: public.xxx_parent_tables.creation_time
-> Sort (cost=0.01..0.02 rows=1 width=324) (actual
time=0.018..0.018 rows=0 loops=1)
Sort Key: public.xxx_parent_tables.creation_time
Sort Method: quicksort Memory: 25kB
-> Seq Scan on xxx_parent_tables (cost=0.00..0.00
rows=1 width=324) (actual time=0.011..0.011 rows=0 loops=1)
Filter: ((status = 1) OR (status IS NULL))
-> Index Scan using
xxx_parent_tables_automobiles_carwale_creation_time_idx on
xxx_parent_tables_automobiles_carwale xxx_parent_tables (co
st=0.00..649960.44 rows=17 width=105) (actual time=10219.559..10219.559
rows=0 loops=1)
Filter: ((status = 1) OR (status IS NULL))
Rows Removed by Filter: 3102241
-> Index Scan using
xxx_parent_tables_automobiles_sulekha_creation_time_idx on
xxx_parent_tables_automobiles_sulekha xxx_parent_tables (co
st=0.00..1124998.57 rows=1 width=105) (actual time=17817.577..17817.577
rows=0 loops=1)
Filter: ((status = 1) OR (status IS NULL))
Rows Removed by Filter: 4016234
-> Index Scan using
xxx_parent_tables_automobiles_verse_creation_time_idx on
xxx_parent_tables_automobiles_verse xxx_parent_tables (cost=0
.00..24068.88 rows=1 width=103) (actual time=675.291..675.291 rows=0
loops=1)
Filter: ((status = 1) OR (status IS NULL))
Rows Removed by Filter: 420616
-> Index Scan using
xxx_parent_tables_automobiles_yolist_creation_time_idx on
xxx_parent_tables_automobiles_yolist xxx_parent_tables (cost
=0.00..25.05 rows=2 width=324) (actual time=0.016..0.016 rows=0 loops=1)
Filter: ((status = 1) OR (status IS NULL))
-> Index Scan using
xxx_parent_tables_deals_bagittoday_creation_time_idx on
xxx_parent_tables_deals_bagittoday xxx_parent_tables (cost=0.0
0..23882.78 rows=1 width=105) (actual time=234.672..234.672 rows=0 loops=1)
Filter: ((status = 1) OR (status IS NULL))
Rows Removed by Filter: 84988
-> Index Scan using
xxx_parent_tables_deals_bindaasbargain_creation_time_idx on
xxx_parent_tables_deals_bindaasbargain xxx_parent_tables (
cost=0.00..25.05 rows=2 width=324) (actual time=0.016..0.016 rows=0 loops=1)
Filter: ((status = 1) OR (status IS NULL))
-> Index Scan using
xxx_parent_tables_deals_buzzr_creation_time_idx on
xxx_parent_tables_deals_buzzr xxx_parent_tables (cost=0.00..11435.4
1 rows=1 width=105) (actual time=109.466..109.466 rows=0 loops=1)
Filter: ((status = 1) OR (status IS NULL))
Rows Removed by Filter: 33750
-> Index Scan using
xxx_parent_tables_deals_dealdrums_creation_time_idx on
xxx_parent_tables_deals_dealdrums xxx_parent_tables (cost=0.00.
.51.61 rows=1 width=105) (actual time=0.917..0.917 rows=0 loops=1)
Filter: ((status = 1) OR (status IS NULL))
Rows Removed by Filter: 941
-> Index Scan using
xxx_parent_tables_deals_dealsandyou_creation_time_idx on
xxx_parent_tables_deals_dealsandyou xxx_parent_tables (cost=0
.00..25.05 rows=2 width=324) (actual time=0.012..0.012 rows=0 loops=1)
Filter: ((status = 1) OR (status IS NULL))
-> Index Scan using
xxx_parent_tables_deals_foodiebay_creation_time_idx on
xxx_parent_tables_deals_foodiebay xxx_parent_tables (cost=0.00.
.25.05 rows=2 width=324) (actual time=0.024..0.024 rows=0 loops=1)
Filter: ((status = 1) OR (status IS NULL))
-> Index Scan using
xxx_parent_tables_deals_futurebazaar_creation_time_idx on
xxx_parent_tables_deals_futurebazaar xxx_parent_tables (cost
=0.00..30.37 rows=1 width=109) (actual time=0.348..0.348 rows=0 loops=1)
Filter: ((status = 1) OR (status IS NULL))
-> Index Scan using
xxx_parent_tables_jobs_jobsa1_creation_time_idx on
xxx_parent_tables_jobs_jobsa1 xxx_parent_tables (cost=0.00..25.05 r
ows=2 width=324) (actual time=0.020..0.020 rows=0 loops=1)
Filter: ((status = 1) OR (status IS NULL))
-> Index Scan using
xxx_parent_tables_jobs_jobsinnigeria_creation_time_idx on
xxx_parent_tables_jobs_jobsinnigeria xxx_parent_tables (cost
=0.00..25.05 rows=2 width=324) (actual time=0.013..0.013 rows=0 loops=1)
Filter: ((status = 1) OR (status IS NULL))
-> Index Scan using
xxx_parent_tables_jobs_khojle_creation_time_idx on
xxx_parent_tables_jobs_khojle xxx_parent_tables (cost=0.00..25.05 r
ows=2 width=324) (actual time=0.013..0.013 rows=0 loops=1)
Filter: ((status = 1) OR (status IS NULL))
-> Index Scan using
xxx_parent_tables_jobs_midday_creation_time_idx on
xxx_parent_tables_jobs_midday xxx_parent_tables (cost=0.00..25.05 r
ows=2 width=324) (actual time=0.011..0.011 rows=0 loops=1)
Filter: ((status = 1) OR (status IS NULL))
-> Index Scan using
xxx_parent_tables_jobs_monsterindia_creation_time_idx on
xxx_parent_tables_jobs_monsterindia xxx_parent_tables (cost=0
.00..31569.68 rows=81849 width=105) (actual time=279.393..544.467
rows=78622 loops=1)
Filter: ((status = 1) OR (status IS NULL))
Rows Removed by Filter: 155151
-> Index Scan using
xxx_parent_tables_jobs_mprc_creation_time_idx on
xxx_parent_tables_jobs_mprc xxx_parent_tables (cost=0.00..25.05 rows=
2 width=324) (actual time=0.016..0.016 rows=0 loops=1)
Filter: ((status = 1) OR (status IS NULL))
-> Index Scan using
xxx_parent_tables_jobs_myjobsintanzania_creation_time_idx on
xxx_parent_tables_jobs_myjobsintanzania xxx_parent_tables
(cost=0.00..25.05 rows=2 width=324) (actual time=0.012..0.012 rows=0
loops=1)
Filter: ((status = 1) OR (status IS NULL))
-> Index Scan using
xxx_parent_tables_mobiles_verse_creation_time_idx on
xxx_parent_tables_mobiles_verse xxx_parent_tables (cost=0.00..25.
05 rows=2 width=324) (actual time=0.015..0.015 rows=0 loops=1)
Filter: ((status = 1) OR (status IS NULL))
-> Index Scan using
xxx_parent_tables_mobileseeker_quikr_creation_time_idx on
xxx_parent_tables_mobileseeker_quikr xxx_parent_tables (cost
=0.00..13.30 rows=1 width=105) (actual time=0.111..0.111 rows=0 loops=1)
Filter: ((status = 1) OR (status IS NULL))
Rows Removed by Filter: 61
Filter: ((status = 1) OR (status IS NULL))
-> Materialize (cost=0.00..3.47 rows=2 width=8) (actual
time=0.000..0.000 rows=2 loops=708260)
-> Seq Scan on cp_info (cost=0.00..3.46 rows=2 width=8)
(actual time=0.028..0.060 rows=2 loops=1)
Filter: (domain_id = 5::bigint)
Rows Removed by Filter: 115
Total runtime: 70481.560 ms
(xxx rows)
^ permalink raw reply [nested|flat] 48+ messages in thread
* Re: Query performance
2013-06-13 07:49 Query performance K P Manoj <kpmanojpg@gmail.com>
@ 2013-06-13 09:03 ` Sergey Konoplev <gray.ru@gmail.com>
0 siblings, 0 replies; 48+ messages in thread
From: Sergey Konoplev @ 2013-06-13 09:03 UTC (permalink / raw)
To: K P Manoj <kpmanojpg@gmail.com>; +Cc: pgsql-performance
On Thu, Jun 13, 2013 at 12:49 AM, K P Manoj <kpmanojpg@gmail.com> wrote:
> One of my query treating performance issue on my production server.
> Once i run query on my parent table with specific condition(hard coded
> value) its uses only proper child table and its index on explain plan ,
> but once i am using table conditions (instead of hard coded value), query
> planner is going all the child tables, Can i know where i am worng
^ permalink raw reply [nested|flat] 48+ messages in thread
* Query performance
@ 2015-01-25 05:41 Joe Van Dyk <joe@tanga.com>
2015-01-25 05:43 ` Re: Query performance Joe Van Dyk <joe@tanga.com>
2015-01-25 21:07 ` Re: Query performance Marc Mamin <M.Mamin@intershop.de>
0 siblings, 2 replies; 48+ messages in thread
From: Joe Van Dyk @ 2015-01-25 05:41 UTC (permalink / raw)
To: pgsql-performance
I have an events table that records page views and purchases (type =
'viewed' or type='purchased'). I have a query that figures out "people who
bought/viewed this also bought/viewed that".
It worked fine, taking about 0.1 seconds to complete, until a few hours ago
when it started taking hours to complete. Vacuum/analyze didn't help.
Turned out there was one session_id that had 400k rows in the system.
Deleting that made the query performant again.
Is there anything I can do to make the query work better in cases like
that? Missing index, or better query?
This is on 9.3.5.
The below is reproduced at the following URL if it's not formatted
correctly in the email.
https://gist.githubusercontent.com/joevandyk/cb8f4afdb6c1b178c606/raw/9940bbe033ebd56d38caa46e33c1dd...
explain select
e1.product_id,
e2.site_id,
e2.product_id,
count(nullif(e2.type='viewed', false)) view_count,
count(nullif(e2.type='purchased', false)) purchase_count
from events e1
join events e2 on e1.session_id = e2.session_id and e1.type = e2.type
where
e1.product_id = '82503' and
e1.product_id != e2.product_id
group by e1.product_id, e2.product_id, e2.site_id;
QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------
GroupAggregate (cost=828395.67..945838.90 rows=22110 width=19)
-> Sort (cost=828395.67..840117.89 rows=4688885 width=19)
Sort Key: e1.product_id, e2.product_id, e2.site_id
-> Nested Loop (cost=11.85..20371.14 rows=4688885 width=19)
-> Bitmap Heap Scan on events e1 (cost=11.29..1404.31
rows=369 width=49)
Recheck Cond: (product_id = '82503'::citext)
-> Bitmap Index Scan on
events_product_id_site_id_idx (cost=0.00..11.20 rows=369 width=0)
Index Cond: (product_id = '82503'::citext)
-> Index Scan using
events_session_id_type_product_id_idx on events e2 (cost=0.56..51.28
rows=12 width=51)
Index Cond: ((session_id = e1.session_id) AND
(type = e1.type))
Filter: (e1.product_id <> product_id)
(11 rows)
recommender_production=> \d events
Table "public.events"
Column | Type | Modifiers
-------------+--------------------------+-----------------------------------------------------
id | bigint | not null default
nextval('events_id_seq'::regclass)
user_id | citext |
session_id | citext | not null
product_id | citext | not null
site_id | citext | not null
type | text | not null
happened_at | timestamp with time zone | not null
created_at | timestamp with time zone | not null
Indexes:
"events_pkey" PRIMARY KEY, btree (id)
"events_product_id_site_id_idx" btree (product_id, site_id)
"events_session_id_type_product_id_idx" btree (session_id, type, product_id)
Check constraints:
"events_session_id_check" CHECK (length(session_id::text) < 255)
"events_type_check" CHECK (type = ANY (ARRAY['purchased'::text,
'viewed'::text]))
"events_user_id_check" CHECK (length(user_id::text) < 255)
^ permalink raw reply [nested|flat] 48+ messages in thread
* Re: Query performance
2015-01-25 05:41 Query performance Joe Van Dyk <joe@tanga.com>
@ 2015-01-25 05:43 ` Joe Van Dyk <joe@tanga.com>
2015-01-25 05:45 ` Re: Query performance Joe Van Dyk <joe@tanga.com>
1 sibling, 1 reply; 48+ messages in thread
From: Joe Van Dyk @ 2015-01-25 05:43 UTC (permalink / raw)
To: pgsql-performance
On Sat, Jan 24, 2015 at 9:41 PM, Joe Van Dyk <joe@tanga.com> wrote:
> I have an events table that records page views and purchases (type =
> 'viewed' or type='purchased'). I have a query that figures out "people who
> bought/viewed this also bought/viewed that".
>
> It worked fine, taking about 0.1 seconds to complete, until a few hours
> ago when it started taking hours to complete. Vacuum/analyze didn't help.
> Turned out there was one session_id that had 400k rows in the system.
> Deleting that made the query performant again.
>
> Is there anything I can do to make the query work better in cases like
> that? Missing index, or better query?
>
> This is on 9.3.5.
>
> The below is reproduced at the following URL if it's not formatted
> correctly in the email.
> https://gist.githubusercontent.com/joevandyk/cb8f4afdb6c1b178c606/raw/9940bbe033ebd56d38caa46e33c1dd...
>
> explain select
> e1.product_id,
> e2.site_id,
> e2.product_id,
> count(nullif(e2.type='viewed', false)) view_count,
> count(nullif(e2.type='purchased', false)) purchase_count
> from events e1
> join events e2 on e1.session_id = e2.session_id and e1.type = e2.type
> where
> e1.product_id = '82503' and
> e1.product_id != e2.product_id
> group by e1.product_id, e2.product_id, e2.site_id;
> QUERY PLAN
> ----------------------------------------------------------------------------------------------------------------------------
> GroupAggregate (cost=828395.67..945838.90 rows=22110 width=19)
> -> Sort (cost=828395.67..840117.89 rows=4688885 width=19)
> Sort Key: e1.product_id, e2.product_id, e2.site_id
> -> Nested Loop (cost=11.85..20371.14 rows=4688885 width=19)
> -> Bitmap Heap Scan on events e1 (cost=11.29..1404.31 rows=369 width=49)
> Recheck Cond: (product_id = '82503'::citext)
> -> Bitmap Index Scan on events_product_id_site_id_idx (cost=0.00..11.20 rows=369 width=0)
> Index Cond: (product_id = '82503'::citext)
> -> Index Scan using events_session_id_type_product_id_idx on events e2 (cost=0.56..51.28 rows=12 width=51)
> Index Cond: ((session_id = e1.session_id) AND (type = e1.type))
> Filter: (e1.product_id <> product_id)
> (11 rows)
>
> recommender_production=> \d events
> Table "public.events"
> Column | Type | Modifiers
> -------------+--------------------------+-----------------------------------------------------
> id | bigint | not null default nextval('events_id_seq'::regclass)
> user_id | citext |
> session_id | citext | not null
> product_id | citext | not null
> site_id | citext | not null
> type | text | not null
> happened_at | timestamp with time zone | not null
> created_at | timestamp with time zone | not null
> Indexes:
> "events_pkey" PRIMARY KEY, btree (id)
> "events_product_id_site_id_idx" btree (product_id, site_id)
> "events_session_id_type_product_id_idx" btree (session_id, type, product_id)
> Check constraints:
> "events_session_id_check" CHECK (length(session_id::text) < 255)
> "events_type_check" CHECK (type = ANY (ARRAY['purchased'::text, 'viewed'::text]))
> "events_user_id_check" CHECK (length(user_id::text) < 255)
>
>
>
>
After removing the session with 400k events, I was able to do an explain
analyze, here is one of them:
http://explain.depesz.com/s/PFNk
^ permalink raw reply [nested|flat] 48+ messages in thread
* Re: Query performance
2015-01-25 05:41 Query performance Joe Van Dyk <joe@tanga.com>
2015-01-25 05:43 ` Re: Query performance Joe Van Dyk <joe@tanga.com>
@ 2015-01-25 05:45 ` Joe Van Dyk <joe@tanga.com>
2015-01-25 06:12 ` Re: Query performance Pavel Stehule <pavel.stehule@gmail.com>
0 siblings, 1 reply; 48+ messages in thread
From: Joe Van Dyk @ 2015-01-25 05:45 UTC (permalink / raw)
To: pgsql-performance
Oops, didn't run vacuum analyze after deleting the events. Here is another
'explain analyze': http://explain.depesz.com/s/AviN
On Sat, Jan 24, 2015 at 9:43 PM, Joe Van Dyk <joe@tanga.com> wrote:
> On Sat, Jan 24, 2015 at 9:41 PM, Joe Van Dyk <joe@tanga.com> wrote:
>
>> I have an events table that records page views and purchases (type =
>> 'viewed' or type='purchased'). I have a query that figures out "people who
>> bought/viewed this also bought/viewed that".
>>
>> It worked fine, taking about 0.1 seconds to complete, until a few hours
>> ago when it started taking hours to complete. Vacuum/analyze didn't help.
>> Turned out there was one session_id that had 400k rows in the system.
>> Deleting that made the query performant again.
>>
>> Is there anything I can do to make the query work better in cases like
>> that? Missing index, or better query?
>>
>> This is on 9.3.5.
>>
>> The below is reproduced at the following URL if it's not formatted
>> correctly in the email.
>> https://gist.githubusercontent.com/joevandyk/cb8f4afdb6c1b178c606/raw/9940bbe033ebd56d38caa46e33c1dd...
>>
>> explain select
>> e1.product_id,
>> e2.site_id,
>> e2.product_id,
>> count(nullif(e2.type='viewed', false)) view_count,
>> count(nullif(e2.type='purchased', false)) purchase_count
>> from events e1
>> join events e2 on e1.session_id = e2.session_id and e1.type = e2.type
>> where
>> e1.product_id = '82503' and
>> e1.product_id != e2.product_id
>> group by e1.product_id, e2.product_id, e2.site_id;
>> QUERY PLAN
>> ----------------------------------------------------------------------------------------------------------------------------
>> GroupAggregate (cost=828395.67..945838.90 rows=22110 width=19)
>> -> Sort (cost=828395.67..840117.89 rows=4688885 width=19)
>> Sort Key: e1.product_id, e2.product_id, e2.site_id
>> -> Nested Loop (cost=11.85..20371.14 rows=4688885 width=19)
>> -> Bitmap Heap Scan on events e1 (cost=11.29..1404.31 rows=369 width=49)
>> Recheck Cond: (product_id = '82503'::citext)
>> -> Bitmap Index Scan on events_product_id_site_id_idx (cost=0.00..11.20 rows=369 width=0)
>> Index Cond: (product_id = '82503'::citext)
>> -> Index Scan using events_session_id_type_product_id_idx on events e2 (cost=0.56..51.28 rows=12 width=51)
>> Index Cond: ((session_id = e1.session_id) AND (type = e1.type))
>> Filter: (e1.product_id <> product_id)
>> (11 rows)
>>
>> recommender_production=> \d events
>> Table "public.events"
>> Column | Type | Modifiers
>> -------------+--------------------------+-----------------------------------------------------
>> id | bigint | not null default nextval('events_id_seq'::regclass)
>> user_id | citext |
>> session_id | citext | not null
>> product_id | citext | not null
>> site_id | citext | not null
>> type | text | not null
>> happened_at | timestamp with time zone | not null
>> created_at | timestamp with time zone | not null
>> Indexes:
>> "events_pkey" PRIMARY KEY, btree (id)
>> "events_product_id_site_id_idx" btree (product_id, site_id)
>> "events_session_id_type_product_id_idx" btree (session_id, type, product_id)
>> Check constraints:
>> "events_session_id_check" CHECK (length(session_id::text) < 255)
>> "events_type_check" CHECK (type = ANY (ARRAY['purchased'::text, 'viewed'::text]))
>> "events_user_id_check" CHECK (length(user_id::text) < 255)
>>
>>
>>
>>
> After removing the session with 400k events, I was able to do an explain
> analyze, here is one of them:
> http://explain.depesz.com/s/PFNk
>
^ permalink raw reply [nested|flat] 48+ messages in thread
* Re: Query performance
2015-01-25 05:41 Query performance Joe Van Dyk <joe@tanga.com>
2015-01-25 05:43 ` Re: Query performance Joe Van Dyk <joe@tanga.com>
2015-01-25 05:45 ` Re: Query performance Joe Van Dyk <joe@tanga.com>
@ 2015-01-25 06:12 ` Pavel Stehule <pavel.stehule@gmail.com>
2015-01-25 06:38 ` Re: Query performance Joe Van Dyk <joe@tanga.com>
0 siblings, 1 reply; 48+ messages in thread
From: Pavel Stehule @ 2015-01-25 06:12 UTC (permalink / raw)
To: Joe Van Dyk <joe@tanga.com>; +Cc: pgsql-performance
Hi
this plan looks well
Regards
Pavel
2015-01-25 6:45 GMT+01:00 Joe Van Dyk <joe@tanga.com>:
> Oops, didn't run vacuum analyze after deleting the events. Here is another
> 'explain analyze': http://explain.depesz.com/s/AviN
>
> On Sat, Jan 24, 2015 at 9:43 PM, Joe Van Dyk <joe@tanga.com> wrote:
>
>> On Sat, Jan 24, 2015 at 9:41 PM, Joe Van Dyk <joe@tanga.com> wrote:
>>
>>> I have an events table that records page views and purchases (type =
>>> 'viewed' or type='purchased'). I have a query that figures out "people who
>>> bought/viewed this also bought/viewed that".
>>>
>>> It worked fine, taking about 0.1 seconds to complete, until a few hours
>>> ago when it started taking hours to complete. Vacuum/analyze didn't help.
>>> Turned out there was one session_id that had 400k rows in the system.
>>> Deleting that made the query performant again.
>>>
>>> Is there anything I can do to make the query work better in cases like
>>> that? Missing index, or better query?
>>>
>>> This is on 9.3.5.
>>>
>>> The below is reproduced at the following URL if it's not formatted
>>> correctly in the email.
>>> https://gist.githubusercontent.com/joevandyk/cb8f4afdb6c1b178c606/raw/9940bbe033ebd56d38caa46e33c1dd...
>>>
>>> explain select
>>> e1.product_id,
>>> e2.site_id,
>>> e2.product_id,
>>> count(nullif(e2.type='viewed', false)) view_count,
>>> count(nullif(e2.type='purchased', false)) purchase_count
>>> from events e1
>>> join events e2 on e1.session_id = e2.session_id and e1.type = e2.type
>>> where
>>> e1.product_id = '82503' and
>>> e1.product_id != e2.product_id
>>> group by e1.product_id, e2.product_id, e2.site_id;
>>> QUERY PLAN
>>> ----------------------------------------------------------------------------------------------------------------------------
>>> GroupAggregate (cost=828395.67..945838.90 rows=22110 width=19)
>>> -> Sort (cost=828395.67..840117.89 rows=4688885 width=19)
>>> Sort Key: e1.product_id, e2.product_id, e2.site_id
>>> -> Nested Loop (cost=11.85..20371.14 rows=4688885 width=19)
>>> -> Bitmap Heap Scan on events e1 (cost=11.29..1404.31 rows=369 width=49)
>>> Recheck Cond: (product_id = '82503'::citext)
>>> -> Bitmap Index Scan on events_product_id_site_id_idx (cost=0.00..11.20 rows=369 width=0)
>>> Index Cond: (product_id = '82503'::citext)
>>> -> Index Scan using events_session_id_type_product_id_idx on events e2 (cost=0.56..51.28 rows=12 width=51)
>>> Index Cond: ((session_id = e1.session_id) AND (type = e1.type))
>>> Filter: (e1.product_id <> product_id)
>>> (11 rows)
>>>
>>> recommender_production=> \d events
>>> Table "public.events"
>>> Column | Type | Modifiers
>>> -------------+--------------------------+-----------------------------------------------------
>>> id | bigint | not null default nextval('events_id_seq'::regclass)
>>> user_id | citext |
>>> session_id | citext | not null
>>> product_id | citext | not null
>>> site_id | citext | not null
>>> type | text | not null
>>> happened_at | timestamp with time zone | not null
>>> created_at | timestamp with time zone | not null
>>> Indexes:
>>> "events_pkey" PRIMARY KEY, btree (id)
>>> "events_product_id_site_id_idx" btree (product_id, site_id)
>>> "events_session_id_type_product_id_idx" btree (session_id, type, product_id)
>>> Check constraints:
>>> "events_session_id_check" CHECK (length(session_id::text) < 255)
>>> "events_type_check" CHECK (type = ANY (ARRAY['purchased'::text, 'viewed'::text]))
>>> "events_user_id_check" CHECK (length(user_id::text) < 255)
>>>
>>>
>>>
>>>
>> After removing the session with 400k events, I was able to do an explain
>> analyze, here is one of them:
>> http://explain.depesz.com/s/PFNk
>>
>
>
^ permalink raw reply [nested|flat] 48+ messages in thread
* Re: Query performance
2015-01-25 05:41 Query performance Joe Van Dyk <joe@tanga.com>
2015-01-25 05:43 ` Re: Query performance Joe Van Dyk <joe@tanga.com>
2015-01-25 05:45 ` Re: Query performance Joe Van Dyk <joe@tanga.com>
2015-01-25 06:12 ` Re: Query performance Pavel Stehule <pavel.stehule@gmail.com>
@ 2015-01-25 06:38 ` Joe Van Dyk <joe@tanga.com>
2015-01-25 07:14 ` Re: Query performance Pavel Stehule <pavel.stehule@gmail.com>
2015-01-25 16:57 ` Re: Query performance Tomas Vondra <tomas.vondra@2ndquadrant.com>
0 siblings, 2 replies; 48+ messages in thread
From: Joe Van Dyk @ 2015-01-25 06:38 UTC (permalink / raw)
To: Pavel Stehule <pavel.stehule@gmail.com>; +Cc: pgsql-performance
On Sat, Jan 24, 2015 at 10:12 PM, Pavel Stehule <pavel.stehule@gmail.com>
wrote:
> Hi
>
> this plan looks well
>
> Regards
>
> Pavel
>
Here's one that's not quite as well: http://explain.depesz.com/s/SgT
Joe
>
> 2015-01-25 6:45 GMT+01:00 Joe Van Dyk <joe@tanga.com>:
>
>> Oops, didn't run vacuum analyze after deleting the events. Here is
>> another 'explain analyze': http://explain.depesz.com/s/AviN
>>
>> On Sat, Jan 24, 2015 at 9:43 PM, Joe Van Dyk <joe@tanga.com> wrote:
>>
>>> On Sat, Jan 24, 2015 at 9:41 PM, Joe Van Dyk <joe@tanga.com> wrote:
>>>
>>>> I have an events table that records page views and purchases (type =
>>>> 'viewed' or type='purchased'). I have a query that figures out "people who
>>>> bought/viewed this also bought/viewed that".
>>>>
>>>> It worked fine, taking about 0.1 seconds to complete, until a few hours
>>>> ago when it started taking hours to complete. Vacuum/analyze didn't help.
>>>> Turned out there was one session_id that had 400k rows in the system.
>>>> Deleting that made the query performant again.
>>>>
>>>> Is there anything I can do to make the query work better in cases like
>>>> that? Missing index, or better query?
>>>>
>>>> This is on 9.3.5.
>>>>
>>>> The below is reproduced at the following URL if it's not formatted
>>>> correctly in the email.
>>>> https://gist.githubusercontent.com/joevandyk/cb8f4afdb6c1b178c606/raw/9940bbe033ebd56d38caa46e33c1dd...
>>>>
>>>> explain select
>>>> e1.product_id,
>>>> e2.site_id,
>>>> e2.product_id,
>>>> count(nullif(e2.type='viewed', false)) view_count,
>>>> count(nullif(e2.type='purchased', false)) purchase_count
>>>> from events e1
>>>> join events e2 on e1.session_id = e2.session_id and e1.type = e2.type
>>>> where
>>>> e1.product_id = '82503' and
>>>> e1.product_id != e2.product_id
>>>> group by e1.product_id, e2.product_id, e2.site_id;
>>>> QUERY PLAN
>>>> ----------------------------------------------------------------------------------------------------------------------------
>>>> GroupAggregate (cost=828395.67..945838.90 rows=22110 width=19)
>>>> -> Sort (cost=828395.67..840117.89 rows=4688885 width=19)
>>>> Sort Key: e1.product_id, e2.product_id, e2.site_id
>>>> -> Nested Loop (cost=11.85..20371.14 rows=4688885 width=19)
>>>> -> Bitmap Heap Scan on events e1 (cost=11.29..1404.31 rows=369 width=49)
>>>> Recheck Cond: (product_id = '82503'::citext)
>>>> -> Bitmap Index Scan on events_product_id_site_id_idx (cost=0.00..11.20 rows=369 width=0)
>>>> Index Cond: (product_id = '82503'::citext)
>>>> -> Index Scan using events_session_id_type_product_id_idx on events e2 (cost=0.56..51.28 rows=12 width=51)
>>>> Index Cond: ((session_id = e1.session_id) AND (type = e1.type))
>>>> Filter: (e1.product_id <> product_id)
>>>> (11 rows)
>>>>
>>>> recommender_production=> \d events
>>>> Table "public.events"
>>>> Column | Type | Modifiers
>>>> -------------+--------------------------+-----------------------------------------------------
>>>> id | bigint | not null default nextval('events_id_seq'::regclass)
>>>> user_id | citext |
>>>> session_id | citext | not null
>>>> product_id | citext | not null
>>>> site_id | citext | not null
>>>> type | text | not null
>>>> happened_at | timestamp with time zone | not null
>>>> created_at | timestamp with time zone | not null
>>>> Indexes:
>>>> "events_pkey" PRIMARY KEY, btree (id)
>>>> "events_product_id_site_id_idx" btree (product_id, site_id)
>>>> "events_session_id_type_product_id_idx" btree (session_id, type, product_id)
>>>> Check constraints:
>>>> "events_session_id_check" CHECK (length(session_id::text) < 255)
>>>> "events_type_check" CHECK (type = ANY (ARRAY['purchased'::text, 'viewed'::text]))
>>>> "events_user_id_check" CHECK (length(user_id::text) < 255)
>>>>
>>>>
>>>>
>>>>
>>> After removing the session with 400k events, I was able to do an explain
>>> analyze, here is one of them:
>>> http://explain.depesz.com/s/PFNk
>>>
>>
>>
>
^ permalink raw reply [nested|flat] 48+ messages in thread
* Re: Query performance
2015-01-25 05:41 Query performance Joe Van Dyk <joe@tanga.com>
2015-01-25 05:43 ` Re: Query performance Joe Van Dyk <joe@tanga.com>
2015-01-25 05:45 ` Re: Query performance Joe Van Dyk <joe@tanga.com>
2015-01-25 06:12 ` Re: Query performance Pavel Stehule <pavel.stehule@gmail.com>
2015-01-25 06:38 ` Re: Query performance Joe Van Dyk <joe@tanga.com>
@ 2015-01-25 07:14 ` Pavel Stehule <pavel.stehule@gmail.com>
2015-01-25 07:20 ` Re: Query performance Joe Van Dyk <joe@tanga.com>
1 sibling, 1 reply; 48+ messages in thread
From: Pavel Stehule @ 2015-01-25 07:14 UTC (permalink / raw)
To: Joe Van Dyk <joe@tanga.com>; +Cc: pgsql-performance
2015-01-25 7:38 GMT+01:00 Joe Van Dyk <joe@tanga.com>:
>
>
> On Sat, Jan 24, 2015 at 10:12 PM, Pavel Stehule <pavel.stehule@gmail.com>
> wrote:
>
>> Hi
>>
>> this plan looks well
>>
>> Regards
>>
>> Pavel
>>
>
> Here's one that's not quite as well: http://explain.depesz.com/s/SgT
>
I see a possible issue
(product_id <> '81716'::citext) .. this operation is CPU expensive and
maybe nonsense
product_id should be integer -- and if it isn't - it should not be on 4M
rows extremly fast - mainly on citext
try to force a opposite cast - you will safe a case insensitive text
comparation
product_id::int <> 81716
Regards
Pavel
>
> Joe
>
>
>>
>> 2015-01-25 6:45 GMT+01:00 Joe Van Dyk <joe@tanga.com>:
>>
>>> Oops, didn't run vacuum analyze after deleting the events. Here is
>>> another 'explain analyze': http://explain.depesz.com/s/AviN
>>>
>>> On Sat, Jan 24, 2015 at 9:43 PM, Joe Van Dyk <joe@tanga.com> wrote:
>>>
>>>> On Sat, Jan 24, 2015 at 9:41 PM, Joe Van Dyk <joe@tanga.com> wrote:
>>>>
>>>>> I have an events table that records page views and purchases (type =
>>>>> 'viewed' or type='purchased'). I have a query that figures out "people who
>>>>> bought/viewed this also bought/viewed that".
>>>>>
>>>>> It worked fine, taking about 0.1 seconds to complete, until a few
>>>>> hours ago when it started taking hours to complete. Vacuum/analyze didn't
>>>>> help. Turned out there was one session_id that had 400k rows in the
>>>>> system. Deleting that made the query performant again.
>>>>>
>>>>> Is there anything I can do to make the query work better in cases like
>>>>> that? Missing index, or better query?
>>>>>
>>>>> This is on 9.3.5.
>>>>>
>>>>> The below is reproduced at the following URL if it's not formatted
>>>>> correctly in the email.
>>>>> https://gist.githubusercontent.com/joevandyk/cb8f4afdb6c1b178c606/raw/9940bbe033ebd56d38caa46e33c1dd...
>>>>>
>>>>> explain select
>>>>> e1.product_id,
>>>>> e2.site_id,
>>>>> e2.product_id,
>>>>> count(nullif(e2.type='viewed', false)) view_count,
>>>>> count(nullif(e2.type='purchased', false)) purchase_count
>>>>> from events e1
>>>>> join events e2 on e1.session_id = e2.session_id and e1.type = e2.type
>>>>> where
>>>>> e1.product_id = '82503' and
>>>>> e1.product_id != e2.product_id
>>>>> group by e1.product_id, e2.product_id, e2.site_id;
>>>>> QUERY PLAN
>>>>> ----------------------------------------------------------------------------------------------------------------------------
>>>>> GroupAggregate (cost=828395.67..945838.90 rows=22110 width=19)
>>>>> -> Sort (cost=828395.67..840117.89 rows=4688885 width=19)
>>>>> Sort Key: e1.product_id, e2.product_id, e2.site_id
>>>>> -> Nested Loop (cost=11.85..20371.14 rows=4688885 width=19)
>>>>> -> Bitmap Heap Scan on events e1 (cost=11.29..1404.31 rows=369 width=49)
>>>>> Recheck Cond: (product_id = '82503'::citext)
>>>>> -> Bitmap Index Scan on events_product_id_site_id_idx (cost=0.00..11.20 rows=369 width=0)
>>>>> Index Cond: (product_id = '82503'::citext)
>>>>> -> Index Scan using events_session_id_type_product_id_idx on events e2 (cost=0.56..51.28 rows=12 width=51)
>>>>> Index Cond: ((session_id = e1.session_id) AND (type = e1.type))
>>>>> Filter: (e1.product_id <> product_id)
>>>>> (11 rows)
>>>>>
>>>>> recommender_production=> \d events
>>>>> Table "public.events"
>>>>> Column | Type | Modifiers
>>>>> -------------+--------------------------+-----------------------------------------------------
>>>>> id | bigint | not null default nextval('events_id_seq'::regclass)
>>>>> user_id | citext |
>>>>> session_id | citext | not null
>>>>> product_id | citext | not null
>>>>> site_id | citext | not null
>>>>> type | text | not null
>>>>> happened_at | timestamp with time zone | not null
>>>>> created_at | timestamp with time zone | not null
>>>>> Indexes:
>>>>> "events_pkey" PRIMARY KEY, btree (id)
>>>>> "events_product_id_site_id_idx" btree (product_id, site_id)
>>>>> "events_session_id_type_product_id_idx" btree (session_id, type, product_id)
>>>>> Check constraints:
>>>>> "events_session_id_check" CHECK (length(session_id::text) < 255)
>>>>> "events_type_check" CHECK (type = ANY (ARRAY['purchased'::text, 'viewed'::text]))
>>>>> "events_user_id_check" CHECK (length(user_id::text) < 255)
>>>>>
>>>>>
>>>>>
>>>>>
>>>> After removing the session with 400k events, I was able to do an
>>>> explain analyze, here is one of them:
>>>> http://explain.depesz.com/s/PFNk
>>>>
>>>
>>>
>>
>
^ permalink raw reply [nested|flat] 48+ messages in thread
* Re: Query performance
2015-01-25 05:41 Query performance Joe Van Dyk <joe@tanga.com>
2015-01-25 05:43 ` Re: Query performance Joe Van Dyk <joe@tanga.com>
2015-01-25 05:45 ` Re: Query performance Joe Van Dyk <joe@tanga.com>
2015-01-25 06:12 ` Re: Query performance Pavel Stehule <pavel.stehule@gmail.com>
2015-01-25 06:38 ` Re: Query performance Joe Van Dyk <joe@tanga.com>
2015-01-25 07:14 ` Re: Query performance Pavel Stehule <pavel.stehule@gmail.com>
@ 2015-01-25 07:20 ` Joe Van Dyk <joe@tanga.com>
2015-01-25 08:03 ` Re: Query performance Pavel Stehule <pavel.stehule@gmail.com>
0 siblings, 1 reply; 48+ messages in thread
From: Joe Van Dyk @ 2015-01-25 07:20 UTC (permalink / raw)
To: Pavel Stehule <pavel.stehule@gmail.com>; +Cc: pgsql-performance
On Sat, Jan 24, 2015 at 11:14 PM, Pavel Stehule <pavel.stehule@gmail.com>
wrote:
>
>
> 2015-01-25 7:38 GMT+01:00 Joe Van Dyk <joe@tanga.com>:
>
>>
>>
>> On Sat, Jan 24, 2015 at 10:12 PM, Pavel Stehule <pavel.stehule@gmail.com>
>> wrote:
>>
>>> Hi
>>>
>>> this plan looks well
>>>
>>> Regards
>>>
>>> Pavel
>>>
>>
>> Here's one that's not quite as well: http://explain.depesz.com/s/SgT
>>
>
> I see a possible issue
>
> (product_id <> '81716'::citext) .. this operation is CPU expensive and
> maybe nonsense
>
> product_id should be integer -- and if it isn't - it should not be on 4M
> rows extremly fast - mainly on citext
>
> try to force a opposite cast - you will safe a case insensitive text
> comparation
>
> product_id::int <> 81716
>
It might not always be an integer, just happens to be so here. Should I try
text instead? I don't have to have the case-insensitive matching.
Joe
>
> Regards
>
> Pavel
>
>
>
>
>>
>> Joe
>>
>>
>>>
>>> 2015-01-25 6:45 GMT+01:00 Joe Van Dyk <joe@tanga.com>:
>>>
>>>> Oops, didn't run vacuum analyze after deleting the events. Here is
>>>> another 'explain analyze': http://explain.depesz.com/s/AviN
>>>>
>>>> On Sat, Jan 24, 2015 at 9:43 PM, Joe Van Dyk <joe@tanga.com> wrote:
>>>>
>>>>> On Sat, Jan 24, 2015 at 9:41 PM, Joe Van Dyk <joe@tanga.com> wrote:
>>>>>
>>>>>> I have an events table that records page views and purchases (type =
>>>>>> 'viewed' or type='purchased'). I have a query that figures out "people who
>>>>>> bought/viewed this also bought/viewed that".
>>>>>>
>>>>>> It worked fine, taking about 0.1 seconds to complete, until a few
>>>>>> hours ago when it started taking hours to complete. Vacuum/analyze didn't
>>>>>> help. Turned out there was one session_id that had 400k rows in the
>>>>>> system. Deleting that made the query performant again.
>>>>>>
>>>>>> Is there anything I can do to make the query work better in cases
>>>>>> like that? Missing index, or better query?
>>>>>>
>>>>>> This is on 9.3.5.
>>>>>>
>>>>>> The below is reproduced at the following URL if it's not formatted
>>>>>> correctly in the email.
>>>>>> https://gist.githubusercontent.com/joevandyk/cb8f4afdb6c1b178c606/raw/9940bbe033ebd56d38caa46e33c1dd...
>>>>>>
>>>>>> explain select
>>>>>> e1.product_id,
>>>>>> e2.site_id,
>>>>>> e2.product_id,
>>>>>> count(nullif(e2.type='viewed', false)) view_count,
>>>>>> count(nullif(e2.type='purchased', false)) purchase_count
>>>>>> from events e1
>>>>>> join events e2 on e1.session_id = e2.session_id and e1.type = e2.type
>>>>>> where
>>>>>> e1.product_id = '82503' and
>>>>>> e1.product_id != e2.product_id
>>>>>> group by e1.product_id, e2.product_id, e2.site_id;
>>>>>> QUERY PLAN
>>>>>> ----------------------------------------------------------------------------------------------------------------------------
>>>>>> GroupAggregate (cost=828395.67..945838.90 rows=22110 width=19)
>>>>>> -> Sort (cost=828395.67..840117.89 rows=4688885 width=19)
>>>>>> Sort Key: e1.product_id, e2.product_id, e2.site_id
>>>>>> -> Nested Loop (cost=11.85..20371.14 rows=4688885 width=19)
>>>>>> -> Bitmap Heap Scan on events e1 (cost=11.29..1404.31 rows=369 width=49)
>>>>>> Recheck Cond: (product_id = '82503'::citext)
>>>>>> -> Bitmap Index Scan on events_product_id_site_id_idx (cost=0.00..11.20 rows=369 width=0)
>>>>>> Index Cond: (product_id = '82503'::citext)
>>>>>> -> Index Scan using events_session_id_type_product_id_idx on events e2 (cost=0.56..51.28 rows=12 width=51)
>>>>>> Index Cond: ((session_id = e1.session_id) AND (type = e1.type))
>>>>>> Filter: (e1.product_id <> product_id)
>>>>>> (11 rows)
>>>>>>
>>>>>> recommender_production=> \d events
>>>>>> Table "public.events"
>>>>>> Column | Type | Modifiers
>>>>>> -------------+--------------------------+-----------------------------------------------------
>>>>>> id | bigint | not null default nextval('events_id_seq'::regclass)
>>>>>> user_id | citext |
>>>>>> session_id | citext | not null
>>>>>> product_id | citext | not null
>>>>>> site_id | citext | not null
>>>>>> type | text | not null
>>>>>> happened_at | timestamp with time zone | not null
>>>>>> created_at | timestamp with time zone | not null
>>>>>> Indexes:
>>>>>> "events_pkey" PRIMARY KEY, btree (id)
>>>>>> "events_product_id_site_id_idx" btree (product_id, site_id)
>>>>>> "events_session_id_type_product_id_idx" btree (session_id, type, product_id)
>>>>>> Check constraints:
>>>>>> "events_session_id_check" CHECK (length(session_id::text) < 255)
>>>>>> "events_type_check" CHECK (type = ANY (ARRAY['purchased'::text, 'viewed'::text]))
>>>>>> "events_user_id_check" CHECK (length(user_id::text) < 255)
>>>>>>
>>>>>>
>>>>>>
>>>>>>
>>>>> After removing the session with 400k events, I was able to do an
>>>>> explain analyze, here is one of them:
>>>>> http://explain.depesz.com/s/PFNk
>>>>>
>>>>
>>>>
>>>
>>
>
^ permalink raw reply [nested|flat] 48+ messages in thread
* Re: Query performance
2015-01-25 05:41 Query performance Joe Van Dyk <joe@tanga.com>
2015-01-25 05:43 ` Re: Query performance Joe Van Dyk <joe@tanga.com>
2015-01-25 05:45 ` Re: Query performance Joe Van Dyk <joe@tanga.com>
2015-01-25 06:12 ` Re: Query performance Pavel Stehule <pavel.stehule@gmail.com>
2015-01-25 06:38 ` Re: Query performance Joe Van Dyk <joe@tanga.com>
2015-01-25 07:14 ` Re: Query performance Pavel Stehule <pavel.stehule@gmail.com>
2015-01-25 07:20 ` Re: Query performance Joe Van Dyk <joe@tanga.com>
@ 2015-01-25 08:03 ` Pavel Stehule <pavel.stehule@gmail.com>
2015-01-31 01:40 ` Re: Query performance Jim Nasby <Jim.Nasby@BlueTreble.com>
0 siblings, 1 reply; 48+ messages in thread
From: Pavel Stehule @ 2015-01-25 08:03 UTC (permalink / raw)
To: Joe Van Dyk <joe@tanga.com>; +Cc: pgsql-performance
2015-01-25 8:20 GMT+01:00 Joe Van Dyk <joe@tanga.com>:
> On Sat, Jan 24, 2015 at 11:14 PM, Pavel Stehule <pavel.stehule@gmail.com>
> wrote:
>
>>
>>
>> 2015-01-25 7:38 GMT+01:00 Joe Van Dyk <joe@tanga.com>:
>>
>>>
>>>
>>> On Sat, Jan 24, 2015 at 10:12 PM, Pavel Stehule <pavel.stehule@gmail.com
>>> > wrote:
>>>
>>>> Hi
>>>>
>>>> this plan looks well
>>>>
>>>> Regards
>>>>
>>>> Pavel
>>>>
>>>
>>> Here's one that's not quite as well: http://explain.depesz.com/s/SgT
>>>
>>
>> I see a possible issue
>>
>> (product_id <> '81716'::citext) .. this operation is CPU expensive and
>> maybe nonsense
>>
>> product_id should be integer -- and if it isn't - it should not be on 4M
>> rows extremly fast - mainly on citext
>>
>> try to force a opposite cast - you will safe a case insensitive text
>> comparation
>>
>> product_id::int <> 81716
>>
>
> It might not always be an integer, just happens to be so here. Should I
> try text instead? I don't have to have the case-insensitive matching.
>
text can be better
this design is unhappy, but you cannot to change ot probably
>
> Joe
>
>
>>
>> Regards
>>
>> Pavel
>>
>>
>>
>>
>>>
>>> Joe
>>>
>>>
>>>>
>>>> 2015-01-25 6:45 GMT+01:00 Joe Van Dyk <joe@tanga.com>:
>>>>
>>>>> Oops, didn't run vacuum analyze after deleting the events. Here is
>>>>> another 'explain analyze': http://explain.depesz.com/s/AviN
>>>>>
>>>>> On Sat, Jan 24, 2015 at 9:43 PM, Joe Van Dyk <joe@tanga.com> wrote:
>>>>>
>>>>>> On Sat, Jan 24, 2015 at 9:41 PM, Joe Van Dyk <joe@tanga.com> wrote:
>>>>>>
>>>>>>> I have an events table that records page views and purchases (type =
>>>>>>> 'viewed' or type='purchased'). I have a query that figures out "people who
>>>>>>> bought/viewed this also bought/viewed that".
>>>>>>>
>>>>>>> It worked fine, taking about 0.1 seconds to complete, until a few
>>>>>>> hours ago when it started taking hours to complete. Vacuum/analyze didn't
>>>>>>> help. Turned out there was one session_id that had 400k rows in the
>>>>>>> system. Deleting that made the query performant again.
>>>>>>>
>>>>>>> Is there anything I can do to make the query work better in cases
>>>>>>> like that? Missing index, or better query?
>>>>>>>
>>>>>>> This is on 9.3.5.
>>>>>>>
>>>>>>> The below is reproduced at the following URL if it's not formatted
>>>>>>> correctly in the email.
>>>>>>> https://gist.githubusercontent.com/joevandyk/cb8f4afdb6c1b178c606/raw/9940bbe033ebd56d38caa46e33c1dd...
>>>>>>>
>>>>>>> explain select
>>>>>>> e1.product_id,
>>>>>>> e2.site_id,
>>>>>>> e2.product_id,
>>>>>>> count(nullif(e2.type='viewed', false)) view_count,
>>>>>>> count(nullif(e2.type='purchased', false)) purchase_count
>>>>>>> from events e1
>>>>>>> join events e2 on e1.session_id = e2.session_id and e1.type = e2.type
>>>>>>> where
>>>>>>> e1.product_id = '82503' and
>>>>>>> e1.product_id != e2.product_id
>>>>>>> group by e1.product_id, e2.product_id, e2.site_id;
>>>>>>> QUERY PLAN
>>>>>>> ----------------------------------------------------------------------------------------------------------------------------
>>>>>>> GroupAggregate (cost=828395.67..945838.90 rows=22110 width=19)
>>>>>>> -> Sort (cost=828395.67..840117.89 rows=4688885 width=19)
>>>>>>> Sort Key: e1.product_id, e2.product_id, e2.site_id
>>>>>>> -> Nested Loop (cost=11.85..20371.14 rows=4688885 width=19)
>>>>>>> -> Bitmap Heap Scan on events e1 (cost=11.29..1404.31 rows=369 width=49)
>>>>>>> Recheck Cond: (product_id = '82503'::citext)
>>>>>>> -> Bitmap Index Scan on events_product_id_site_id_idx (cost=0.00..11.20 rows=369 width=0)
>>>>>>> Index Cond: (product_id = '82503'::citext)
>>>>>>> -> Index Scan using events_session_id_type_product_id_idx on events e2 (cost=0.56..51.28 rows=12 width=51)
>>>>>>> Index Cond: ((session_id = e1.session_id) AND (type = e1.type))
>>>>>>> Filter: (e1.product_id <> product_id)
>>>>>>> (11 rows)
>>>>>>>
>>>>>>> recommender_production=> \d events
>>>>>>> Table "public.events"
>>>>>>> Column | Type | Modifiers
>>>>>>> -------------+--------------------------+-----------------------------------------------------
>>>>>>> id | bigint | not null default nextval('events_id_seq'::regclass)
>>>>>>> user_id | citext |
>>>>>>> session_id | citext | not null
>>>>>>> product_id | citext | not null
>>>>>>> site_id | citext | not null
>>>>>>> type | text | not null
>>>>>>> happened_at | timestamp with time zone | not null
>>>>>>> created_at | timestamp with time zone | not null
>>>>>>> Indexes:
>>>>>>> "events_pkey" PRIMARY KEY, btree (id)
>>>>>>> "events_product_id_site_id_idx" btree (product_id, site_id)
>>>>>>> "events_session_id_type_product_id_idx" btree (session_id, type, product_id)
>>>>>>> Check constraints:
>>>>>>> "events_session_id_check" CHECK (length(session_id::text) < 255)
>>>>>>> "events_type_check" CHECK (type = ANY (ARRAY['purchased'::text, 'viewed'::text]))
>>>>>>> "events_user_id_check" CHECK (length(user_id::text) < 255)
>>>>>>>
>>>>>>>
>>>>>>>
>>>>>>>
>>>>>> After removing the session with 400k events, I was able to do an
>>>>>> explain analyze, here is one of them:
>>>>>> http://explain.depesz.com/s/PFNk
>>>>>>
>>>>>
>>>>>
>>>>
>>>
>>
>
^ permalink raw reply [nested|flat] 48+ messages in thread
* Re: Query performance
2015-01-25 05:41 Query performance Joe Van Dyk <joe@tanga.com>
2015-01-25 05:43 ` Re: Query performance Joe Van Dyk <joe@tanga.com>
2015-01-25 05:45 ` Re: Query performance Joe Van Dyk <joe@tanga.com>
2015-01-25 06:12 ` Re: Query performance Pavel Stehule <pavel.stehule@gmail.com>
2015-01-25 06:38 ` Re: Query performance Joe Van Dyk <joe@tanga.com>
2015-01-25 07:14 ` Re: Query performance Pavel Stehule <pavel.stehule@gmail.com>
2015-01-25 07:20 ` Re: Query performance Joe Van Dyk <joe@tanga.com>
2015-01-25 08:03 ` Re: Query performance Pavel Stehule <pavel.stehule@gmail.com>
@ 2015-01-31 01:40 ` Jim Nasby <Jim.Nasby@BlueTreble.com>
2015-01-31 06:28 ` Re: Query performance Pavel Stehule <pavel.stehule@gmail.com>
0 siblings, 1 reply; 48+ messages in thread
From: Jim Nasby @ 2015-01-31 01:40 UTC (permalink / raw)
To: Pavel Stehule <pavel.stehule@gmail.com>; Joe Van Dyk <joe@tanga.com>; +Cc: pgsql-performance
On 1/25/15 2:03 AM, Pavel Stehule wrote:
> It might not always be an integer, just happens to be so here.
> Should I try text instead? I don't have to have the case-insensitive
> matching.
>
>
> text can be better
bytea would be even better yet, because that will always be a straight
binary comparison. text will worry about conversion and what not
(though, perhaps there's a way to force that to use C or SQL instead of
something like UTF8, short of changing the encoding of the whole database).
--
Jim Nasby, Data Architect, Blue Treble Consulting
Data in Trouble? Get it in Treble! http://BlueTreble.com
--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
^ permalink raw reply [nested|flat] 48+ messages in thread
* Re: Query performance
2015-01-25 05:41 Query performance Joe Van Dyk <joe@tanga.com>
2015-01-25 05:43 ` Re: Query performance Joe Van Dyk <joe@tanga.com>
2015-01-25 05:45 ` Re: Query performance Joe Van Dyk <joe@tanga.com>
2015-01-25 06:12 ` Re: Query performance Pavel Stehule <pavel.stehule@gmail.com>
2015-01-25 06:38 ` Re: Query performance Joe Van Dyk <joe@tanga.com>
2015-01-25 07:14 ` Re: Query performance Pavel Stehule <pavel.stehule@gmail.com>
2015-01-25 07:20 ` Re: Query performance Joe Van Dyk <joe@tanga.com>
2015-01-25 08:03 ` Re: Query performance Pavel Stehule <pavel.stehule@gmail.com>
2015-01-31 01:40 ` Re: Query performance Jim Nasby <Jim.Nasby@BlueTreble.com>
@ 2015-01-31 06:28 ` Pavel Stehule <pavel.stehule@gmail.com>
0 siblings, 0 replies; 48+ messages in thread
From: Pavel Stehule @ 2015-01-31 06:28 UTC (permalink / raw)
To: Jim Nasby <Jim.Nasby@bluetreble.com>; +Cc: Joe Van Dyk <joe@tanga.com>; pgsql-performance
2015-01-31 2:40 GMT+01:00 Jim Nasby <Jim.Nasby@bluetreble.com>:
> On 1/25/15 2:03 AM, Pavel Stehule wrote:
>
>> It might not always be an integer, just happens to be so here.
>> Should I try text instead? I don't have to have the case-insensitive
>> matching.
>>
>>
>> text can be better
>>
>
> bytea would be even better yet, because that will always be a straight
> binary comparison. text will worry about conversion and what not (though,
> perhaps there's a way to force that to use C or SQL instead of something
> like UTF8, short of changing the encoding of the whole database).
>
true,
good idea
Regards
Pavel
> --
> Jim Nasby, Data Architect, Blue Treble Consulting
> Data in Trouble? Get it in Treble! http://BlueTreble.com
>
^ permalink raw reply [nested|flat] 48+ messages in thread
* Re: Query performance
2015-01-25 05:41 Query performance Joe Van Dyk <joe@tanga.com>
2015-01-25 05:43 ` Re: Query performance Joe Van Dyk <joe@tanga.com>
2015-01-25 05:45 ` Re: Query performance Joe Van Dyk <joe@tanga.com>
2015-01-25 06:12 ` Re: Query performance Pavel Stehule <pavel.stehule@gmail.com>
2015-01-25 06:38 ` Re: Query performance Joe Van Dyk <joe@tanga.com>
@ 2015-01-25 16:57 ` Tomas Vondra <tomas.vondra@2ndquadrant.com>
1 sibling, 0 replies; 48+ messages in thread
From: Tomas Vondra @ 2015-01-25 16:57 UTC (permalink / raw)
To: pgsql-performance
Hi,
On 25.1.2015 07:38, Joe Van Dyk wrote:
>
> Here's one that's not quite as well: http://explain.depesz.com/s/SgT
As Pavel already pointed out, the first problem is this part of the plan:
Seq Scan on events e2 (cost=0.00..120,179.60 rows=4,450,241 width=51)
(actual time=0.014..33,773.370 rows=4,450,865 loops=1)
Filter: (product_id <> '81716'::citext)
Consuming ~33 seconds of the runtime. If you can make this faster
somehow (e.g. by getting rid of the citext cast), that'd be nice.
Another issue is that the hashjoin is batched:
Buckets: 65536 Batches: 8 Memory Usage: 46085kB
The hash preparation takes ~40 seconds, so maybe try to give it a bit
more memory - I assume you have work_mem=64MB, so try doubling that
(ISTM 512MB should work with a single batch). Maybe this won't really
improve the performance, though. It still has to process ~4.5M rows.
Increasing the work mem could also result in switching to hash
aggregate, making the sort (~30 seconds) unnecessary.
Anyway, ISTM this works as expected, i.e.
(a) with rare product_id values the queries are fast
(b) with common product_id values the queries are slow
That's expected, because (b) needs to process much more data. I don't
think you can magically make it run as fast as (a). The best solution
might be to keep a pre-aggregated results - I don't think you really
need exact answers when recommending "similar" products.
I also wonder if you really need to join the tables? I mean, what if you
do something like this:
CREATE TABLE events_aggregated AS SELECT
site_id,
array_agg(product_id) AS product_ids,
count(nullif(e2.type='viewed', false)) view_count,
count(nullif(e2.type='purchased', false)) purchase_count
FROM events
GROUP BY 1;
and then using intarray with GIN indexes to query this table?
Something like this:
CREATE products_agg_idx ON aggregated
USING GIN (product_ids gin__int_ops);
SELECT * FROM events_aggregated WHERE product_ids @> ARRAY['82503'];
regards
--
Tomas Vondra http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
^ permalink raw reply [nested|flat] 48+ messages in thread
* Re: Query performance
2015-01-25 05:41 Query performance Joe Van Dyk <joe@tanga.com>
@ 2015-01-25 21:07 ` Marc Mamin <M.Mamin@intershop.de>
1 sibling, 0 replies; 48+ messages in thread
From: Marc Mamin @ 2015-01-25 21:07 UTC (permalink / raw)
To: Joe Van Dyk <joe@tanga.com>; pgsql-performance
>I have an events table that records page views and purchases (type = 'viewed' or type='purchased'). I have a query that figures out "people who bought/viewed this also bought/viewed that".
>
>It worked fine, taking about 0.1 seconds to complete, until a few hours ago when it started taking hours to complete. Vacuum/analyze didn't help. Turned out there was one session_id that had 400k rows in the system. Deleting that made the query performant again.
>
>Is there anything I can do to make the query work better in cases like that? Missing index, or better query?
>
>This is on 9.3.5.
>
>The below is reproduced at the following URL if it's not formatted correctly in the email. https://gist.githubusercontent.com/joevandyk/cb8f4afdb6c1b178c606/raw/9940bbe033ebd56d38caa46e33c1dd...
Hello,
here are 2 variations that should be somewhat faster.
It seems you may have duplicate (site_id,session_id,product_id)
which would false the result. In that case you'll need some more logic in the query.
select
'82503' as product_id,
e2.site_id,
e2.product_id,
count(nullif(e2.type='viewed', false)) view_count,
count(nullif(e2.type='purchased', false)) purchase_count
from events e1
join events e2 on e1.session_id = e2.session_id and e1.type = e2.type
where
e1.product_id = '82503' and
e2.product_id != '82503'
group by e2.product_id, e2.site_id;
OR:
WITH SALL as(
select
e2.site_id,
e2.product_id,
count(nullif(e2.type='viewed', false)) view_count,
count(nullif(e2.type='purchased', false)) purchase_count
from events e1
join events e2 on e1.session_id = e2.session_id and e1.type = e2.type
where
e1.product_id = '82503'
group by e2.product_id, e2.site_id
)
SELECT
'82503' as product_id_1,
site_id,
product_id,
view_count,
purchase_count
FROM SALL
WHERE product_id != '82503';
regards,
Marc Mamin
>explain select
> e1.product_id,
> e2.site_id,
> e2.product_id,
> count(nullif(e2.type='viewed', false)) view_count,
> count(nullif(e2.type='purchased', false)) purchase_count
> from events e1
> join events e2 on e1.session_id = e2.session_id and e1.type = e2.type
> where
> e1.product_id = '82503' and
> e1.product_id != e2.product_id
> group by e1.product_id, e2.product_id, e2.site_id;
> QUERY PLAN
>----------------------------------------------------------------------------------------------------------------------------
> GroupAggregate (cost=828395.67..945838.90 rows=22110 width=19)
> -> Sort (cost=828395.67..840117.89 rows=4688885 width=19)
> Sort Key: e1.product_id, e2.product_id, e2.site_id
> -> Nested Loop (cost=11.85..20371.14 rows=4688885 width=19)
> -> Bitmap Heap Scan on events e1 (cost=11.29..1404.31 rows=369 width=49)
> Recheck Cond: (product_id = '82503'::citext)
> -> Bitmap Index Scan on events_product_id_site_id_idx (cost=0.00..11.20 rows=369 width=0)
> Index Cond: (product_id = '82503'::citext)
> -> Index Scan using events_session_id_type_product_id_idx on events e2 (cost=0.56..51.28 rows=12 width=51)
> Index Cond: ((session_id = e1.session_id) AND (type = e1.type))
> Filter: (e1.product_id <> product_id)
>(11 rows)
>
>recommender_production=> \d events
> Table "public.events"
> Column | Type | Modifiers
>-------------+--------------------------+-----------------------------------------------------
> id | bigint | not null default nextval('events_id_seq'::regclass)
> user_id | citext |
> session_id | citext | not null
> product_id | citext | not null
> site_id | citext | not null
> type | text | not null
> happened_at | timestamp with time zone | not null
> created_at | timestamp with time zone | not null
>Indexes:
> "events_pkey" PRIMARY KEY, btree (id)
> "events_product_id_site_id_idx" btree (product_id, site_id)
> "events_session_id_type_product_id_idx" btree (session_id, type, product_id)
>Check constraints:
> "events_session_id_check" CHECK (length(session_id::text) < 255)
> "events_type_check" CHECK (type = ANY (ARRAY['purchased'::text, 'viewed'::text]))
> "events_user_id_check" CHECK (length(user_id::text) < 255)
>
>
>
>
^ permalink raw reply [nested|flat] 48+ messages in thread
* Query Performance
@ 2017-02-20 21:39 Diego Vargas <diegov@propaas.com>
0 siblings, 0 replies; 48+ messages in thread
From: Diego Vargas @ 2017-02-20 21:39 UTC (permalink / raw)
To: pgsql-performance
Hi All,
I'm having some trouble improving the timing of a set of queries to a
partitioned table.
Basically, I'm trying to find an index that would be used instead of a
bitmap heap scan by when the data is taken from disk. Or in any case,
something that would make the process of retrieving the data from disk
faster.
I've installed postgreSQL compiling the source: PostgreSQL 9.2.20 on
x86_64-unknown-linux-gnu, compiled by gcc (GCC) 4.4.7 20120313 (Red Hat
4.4.7-17), 64-bit
And these are the current changes on the configuration file:
name | current_setting | source
----------------------------+--------------------+----------------------
application_name | psql | client
client_encoding | UTF8 | client
DateStyle | ISO, MDY | configuration file
default_text_search_config | pg_catalog.english | configuration file
lc_messages | en_US.UTF-8 | configuration file
lc_monetary | en_US.UTF-8 | configuration file
lc_numeric | en_US.UTF-8 | configuration file
lc_time | en_US.UTF-8 | configuration file
log_destination | stderr | configuration file
log_directory | pg_log | configuration file
log_filename | postgresql-%a.log | configuration file
log_rotation_age | 1d | configuration file
log_rotation_size | 0 | configuration file
log_timezone | UTC | configuration file
log_truncate_on_rotation | on | configuration file
logging_collector | on | configuration file
max_connections | 100 | configuration file
max_stack_depth | 2MB | environment variable
shared_buffers | 6GB | configuration file
TimeZone | UTC | configuration file
work_mem | 50MB | configuration file
I'm running on CentOS 6.8, and all the tests are being done through psql.
Now, this is the table in question:
lportal=# \d+ data_jsons_partition
Table "data_jsons_partition"
Column | Type | Modifiers | Storage |
Stats target | Description
-----------------+-----------------------------+-----------+
----------+--------------+-------------
id | integer | | plain
| |
site_id | integer | | plain
| |
site_name | character varying(255) | | extended
| |
measured_on | date | | plain
| |
protocol | text | | extended
| |
data | json | | extended
| |
created_at | timestamp without time zone | | plain
| |
updated_at | timestamp without time zone | | plain
| |
org_name | character varying | | extended
| |
org_id | integer | | plain
| |
lat | double precision | | plain
| |
long | double precision | | plain
| |
elev | double precision | | plain
| |
Triggers:
insert_measurement_trigger BEFORE INSERT ON data_jsons_partition FOR
EACH ROW EXECUTE PROCEDURE data_insert_trigger()
Child tables: partitions.partition_a_data_jsons_part,
partitions.partition_b_data_jsons_part,
...
partitions.partition_aa_data_jsons_part,
partitions.partition_ab_data_jsons_part
The child tables exists based on the protocol column. Now, each partition
looks like this:
lportal=# \d+ partitions.partition_ab_data_jsons_part
Table "partitions.partition_ab_data_jsons_part"
Column | Type | Modifiers | Storage |
Stats target | Description
-----------------+-----------------------------+-----------+
----------+--------------+-------------
id | integer | not null | plain
| |
site_id | integer | | plain
| |
site_name | character varying(255) | | extended
| |
measured_on | date | | plain
| |
protocol | text | | extended
| |
data | json | | extended
| |
created_at | timestamp without time zone | | plain
| |
updated_at | timestamp without time zone | | plain
| |
org_name | character varying | | extended
| |
organization_id | integer | | plain
| |
latitude | double precision | | plain
| |
longitude | double precision | | plain
| |
elevation | double precision | | plain
| |
Indexes:
"partition_ab_data_jsons_part_pkey" PRIMARY KEY, btree (id)
"partition_ab_data_jsons_part_spm_key" UNIQUE CONSTRAINT, btree
(site_id, protocol, measured_on)
"partition_ab_data_jsons_part_mo" btree (measured_on)
"partition_ab_data_jsons_part_org" btree (org_name)
"partition_ab_data_jsons_part_org_id" btree (organization_id)
"partition_ab_data_jsons_part_sid" btree (site_id) CLUSTER
"partition_ab_data_jsons_part_sm" btree (site_id, measured_on)
Check constraints:
"partition_ab_data_jsons_part_protocol_check" CHECK (protocol = '
partition_ab'::text)
Inherits: data_jsons_partition
Now, I have this query that I've executed with a clean cache:
lportal=# explain analyze SELECT org_name, site_name, latitude, longitude,
elevation, measured_on, data FROM data_jsons_partition where protocol in
('aerosols','precipitations') and site_id in (... around 1000 site_id-s
...) and (measured_on >= '2013-09-24' and measured_on <= '2016-10-10')
order by org_name, site_name, measured_on limit 1000000;
And I get the following:
Limit (cost=149414.00..149518.52 rows=41806 width=110) (actual
time=25827.893..26012.065 rows=126543 loops=1)
-> Sort (cost=149414.00..149518.52 rows=41806 width=110) (actual
time=25827.889..25970.671 rows=126543 loops=1)
Sort Key: data_jsons_partition.org_name,
data_jsons_partition.site_name,
data_jsons_partition.measured_on
Sort Method: external merge Disk: 70616kB
-> Result (cost=0.00..146205.09 rows=41806 width=110) (actual
time=38.533..20810.204 rows=126543 loops=1)
-> Append (cost=0.00..146205.09 rows=41806 width=110)
(actual time=38.530..20739.245 rows=126543 loops=1)
-> Seq Scan on data_jsons_partition (cost=0.00..0.00
rows=1 width=608) (actual time=0.002..0.002 rows=0 loops=1)
Filter: ((protocol = ANY
('{partition_a,partition_b}'::text[])) AND (measured_on >=
'2013-09-24'::date) AND (measured_on <= '2016-10-10'::date) AND (site_id =
ANY ('{... 1000 site_id-s ...}'::integer[])))
-> Bitmap Heap Scan on partition_a_data_jsons_part
data_jsons_partition (cost=70.92..5209.38 rows=2132 width=114) (actual
time=38.526..812.397 rows=3017 loops=1)
Recheck Cond: ((measured_on >=
'2013-09-24'::date) AND (measured_on <= '2016-10-10'::date))
Filter: ((protocol = ANY ('{partition_a,
partition_b}'::text[])) AND (site_id = ANY ('{ ... }'::integer[])))
-> Bitmap Index Scan on partition_a
_data_jsons_part_mo (cost=0.00..70.39 rows=3014 width=0) (actual
time=2.974..2.974 rows=3017 loops=1)
Index Cond: ((measured_on >=
'2013-09-24'::date) AND (measured_on <= '2016-10-10'::date))
-> Bitmap Heap Scan on partition_b_data_jsons_part
data_jsons_partition (cost=4582.19..140995.72 rows=39673 width=110)
(actual time=738.486..19871.141 rows=123526 loops=1)
Recheck Cond: ((site_id = ANY ('{...
...}'::integer[])))
Filter: (protocol = ANY ('{partition_a,
partition_b}'::text[]))
-> Bitmap Index Scan on partition_b
_data_jsons_part_sm (cost=0.00..4572.27 rows=39673 width=0) (actual
time=715.684..715.684 rows=123526 loops=1)
Index Cond: ((site_id = ANY ('{...
...}'::integer[])))
Total runtime: 26049.062 ms
^ permalink raw reply [nested|flat] 48+ messages in thread
* Query performance
@ 2020-10-22 00:32 Nagaraj Raj <nagaraj.sf@yahoo.com>
2020-10-22 01:09 ` Re: Query performance Justin Pryzby <pryzby@telsasoft.com>
2020-10-22 01:11 ` Re: Query performance David G. Johnston <david.g.johnston@gmail.com>
0 siblings, 2 replies; 48+ messages in thread
From: Nagaraj Raj @ 2020-10-22 00:32 UTC (permalink / raw)
To: Pgsql Performance <pgsql-performance@lists.postgresql.org>
Hi, I have long running query which running for long time and its planner always performing sequnce scan the table2.My gole is to reduce Read IO on the disk cause, this query runns more oftenly ( using this in funtion for ETL).
table1: transfer_order_header(records 2782678)table2: transfer_order_item ( records: 15995697)here is the query:
set work_mem = '688552kB';explain (analyze,buffers)select COALESCE(itm.serialnumber,'') AS SERIAL_NO, COALESCE(itm.ITEM_SKU,'') AS SKU, COALESCE(itm.receivingplant,'') AS RECEIVINGPLANT, COALESCE(itm.STO_ID,'') AS STO, supplyingplant, COALESCE(itm.deliveryitem,'') AS DELIVERYITEM, min(eventtime) as eventtime FROM sor_t.transfer_order_header hed,sor_t.transfer_order_item itm where hed.eventid=itm.eventid group by 1,2,3,4,5,6
Query Planner[2]:
"Finalize GroupAggregate (cost=1930380.06..4063262.11 rows=16004137 width=172) (actual time=56050.500..83268.566 rows=15891873 loops=1)"" Group Key: (COALESCE(itm.serialnumber, ''::character varying)), (COALESCE(itm.item_sku, ''::character varying)), (COALESCE(itm.receivingplant, ''::character varying)), (COALESCE(itm.sto_id, ''::character varying)), hed.supplyingplant, (COALESCE(itm.deliveryitem, ''::character varying))"" Buffers: shared hit=712191 read=3, temp read=38232 written=38233"" -> Gather Merge (cost=1930380.06..3669827.09 rows=13336780 width=172) (actual time=56050.488..77106.993 rows=15948520 loops=1)"" Workers Planned: 2"" Workers Launched: 2"" Buffers: shared hit=2213081 read=12, temp read=142840 written=142843"" -> Partial GroupAggregate (cost=1929380.04..2129431.74 rows=6668390 width=172) (actual time=50031.458..54888.828 rows=5316173 loops=3)"" Group Key: (COALESCE(itm.serialnumber, ''::character varying)), (COALESCE(itm.item_sku, ''::character varying)), (COALESCE(itm.receivingplant, ''::character varying)), (COALESCE(itm.sto_id, ''::character varying)), hed.supplyingplant, (COALESCE(itm.deliveryitem, ''::character varying))"" Buffers: shared hit=2213081 read=12, temp read=142840 written=142843"" -> Sort (cost=1929380.04..1946051.01 rows=6668390 width=172) (actual time=50031.446..52823.352 rows=5332010 loops=3)"" Sort Key: (COALESCE(itm.serialnumber, ''::character varying)), (COALESCE(itm.item_sku, ''::character varying)), (COALESCE(itm.receivingplant, ''::character varying)), (COALESCE(itm.sto_id, ''::character varying)), hed.supplyingplant, (COALESCE(itm.deliveryitem, ''::character varying))"" Sort Method: external merge Disk: 305856kB"" Worker 0: Sort Method: external merge Disk: 436816kB"" Worker 1: Sort Method: external merge Disk: 400048kB"" Buffers: shared hit=2213081 read=12, temp read=142840 written=142843"" -> Parallel Hash Join (cost=133229.66..603743.97 rows=6668390 width=172) (actual time=762.925..3901.133 rows=5332010 loops=3)"" Hash Cond: ((itm.eventid)::text = (hed.eventid)::text)"" Buffers: shared hit=2213027 read=12"" -> Parallel Seq Scan on transfer_order_item itm (cost=0.00..417722.90 rows=6668390 width=68) (actual time=0.005..524.359 rows=5332010 loops=3)"" Buffers: shared hit=351039"" -> Parallel Hash (cost=118545.68..118545.68 rows=1174718 width=35) (actual time=755.590..755.590 rows=926782 loops=3)"" Buckets: 4194304 Batches: 1 Memory Usage: 243808kB"" Buffers: shared hit=1861964 read=12"" -> Parallel Index Only Scan using transfer_order_header_eventid_supplyingplant_eventtime_idx1 on transfer_order_header hed (cost=0.56..118545.68 rows=1174718 width=35) (actual time=0.128..388.436 rows=926782 loops=3)"" Heap Fetches: 18322"" Buffers: shared hit=1861964 read=12""Planning Time: 1.068 ms""Execution Time: 84274.004 ms"
Tables[1] created ddls in dbfiddle.
PG Server: PostgreSQL 11.7 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.9.3, 64-bit.RAM: 456Mem Settings: "maintenance_work_mem" "8563712" "kB"
"work_mem" "688552" "kB"
"wal_buffers" "2048" "8kB"
"shared_buffers" "44388442" "8kB"
Any suggestions would greatly appretiated.
Thanks,Rj
^ permalink raw reply [nested|flat] 48+ messages in thread
* Re: Query performance
2020-10-22 00:32 Query performance Nagaraj Raj <nagaraj.sf@yahoo.com>
@ 2020-10-22 01:09 ` Justin Pryzby <pryzby@telsasoft.com>
1 sibling, 0 replies; 48+ messages in thread
From: Justin Pryzby @ 2020-10-22 01:09 UTC (permalink / raw)
To: Nagaraj Raj <nagaraj.sf@yahoo.com>; +Cc: pgsql-performance@lists.postgresql.org
On Thu, Oct 22, 2020 at 12:32:29AM +0000, Nagaraj Raj wrote:
> Hi, I have long running query which running for long time and its planner always performing sequnce scan the table2.My gole is to reduce Read IO on the disk cause, this query runns more oftenly ( using this in funtion for ETL).
>
> table1: transfer_order_header(records 2782678)table2: transfer_order_item ( records: 15995697)here is the query:
>
> set work_mem = '688552kB';explain (analyze,buffers)select COALESCE(itm.serialnumber,'') AS SERIAL_NO, COALESCE(itm.ITEM_SKU,'') AS SKU, COALESCE(itm.receivingplant,'') AS RECEIVINGPLANT, COALESCE(itm.STO_ID,'') AS STO, supplyingplant, COALESCE(itm.deliveryitem,'') AS DELIVERYITEM, min(eventtime) as eventtime FROM sor_t.transfer_order_header hed,sor_t.transfer_order_item itm where hed.eventid=itm.eventid group by 1,2,3,4,5,6
It spends most its time writing tempfiles for sorting, so it (still) seems to
be starved for work_mem.
|Sort (cost=1929380.04..1946051.01 rows=6668390 width=172) (actual time=50031.446..52823.352 rows=5332010 loops=3)
First, can you get a better plan with 2GB work_mem or with enable_sort=off ?
If so, maybe you could make it less expensive by moving all the coalesce()
into a subquery, like
| SELECT COALESCE(a,''), COALESCE(b,''), .. FROM (SELECT a,b, .. GROUP BY 1,2,..)x;
Or, if you have a faster disks available, use them for temp_tablespace.
--
Justin
^ permalink raw reply [nested|flat] 48+ messages in thread
* Re: Query performance
2020-10-22 00:32 Query performance Nagaraj Raj <nagaraj.sf@yahoo.com>
@ 2020-10-22 01:11 ` David G. Johnston <david.g.johnston@gmail.com>
1 sibling, 0 replies; 48+ messages in thread
From: David G. Johnston @ 2020-10-22 01:11 UTC (permalink / raw)
To: Nagaraj Raj <nagaraj.sf@yahoo.com>; +Cc: Pgsql Performance <pgsql-performance@lists.postgresql.org>
On Wed, Oct 21, 2020 at 5:32 PM Nagaraj Raj <nagaraj.sf@yahoo.com> wrote:
> Hi, I have long running query which running for long time and its planner
> always performing sequnce scan the table2.
>
FROM sor_t.transfer_order_header hed,sor_t.transfer_order_item itm
> where hed.eventid=itm.eventid group by 1,2,3,4,5,6
>
> Any suggestions would greatly appretiated.
>
You aren't filtering out any rows so it is unsurprising that a sequential
scan was chosen to fulfil the request that the entire detail table be
consulted. The good news is you have access to parallelism - see if you
can increase that factor.
Any other suggestions probably requires more knowledge of your problem
domain than you've provided here.
Finding a way to add a where clause or compute your desired result during
record insertion or updating are two other potential avenues of
consideration.
David J.
^ permalink raw reply [nested|flat] 48+ messages in thread
* Query Performance
@ 2021-07-21 17:13 Dirschel, Steve <steve.dirschel@thomsonreuters.com>
2021-07-21 18:03 ` Re: Query Performance Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 48+ messages in thread
From: Dirschel, Steve @ 2021-07-21 17:13 UTC (permalink / raw)
To: pgsql-performance@lists.postgresql.org <pgsql-performance@lists.postgresql.org>
New to Postgres, Oracle background. With Oracle the amount of work a query does is tracked via logical reads. Oracle tracks logical and physical reads differently than Postgres. With Oracle a physical read is always considered a logical read. So if a query reads 5 blocks are all 5 are read from disk the query would do 5 logical reads, 5 physical reads. It appears with Postgres Buffers shared hit are reads from memory and Buffer shared read is off disk. To get total reads one would need to add up shared hits + shared reads.
I have a sample query that is doing more work if some of the reads are physical reads and I'm trying to understand why. If you look at attached QueryWithPhyReads.txt it shows the query did Buffers: shared hit=171 read=880. So it did 171 + 880 = 1051 total block reads (some logical, some physical). QueryWithNoPhyReads.txt shows execution statistics of the execution of the exact same query with same data point. The only difference is the first execution loaded blocks into memory so this execution had all shared hits. In this case the query did this much work: Buffers: shared hit=581.
With Oracle that would not happen. If the 2nd execution of the query did all reads from memory the shared hits would be 1051, not 581.
So it appears to me that with Postgres when a query does physical reads it not only has the expense of doing those disk reads but there is also extra work done to increase overall block reads for a query. But I don't understand why that would be the case. Could someone explain why this is happening?
Thanks
This e-mail is for the sole use of the intended recipient and contains information that may be privileged and/or confidential. If you are not an intended recipient, please notify the sender by return e-mail and delete this e-mail and any attachments. Certain required legal entity disclosures can be accessed on our website: https://www.thomsonreuters.com/en/resources/disclosures.html
explain (analyze, verbose, costs, settings, buffers, timing, summary, hashes)
(/*+ IndexScan(historyeve0_ history_event_idx02) */ select distinct historyeve0_.EVENT_TYPE as junk123
from HISTORY_EVENT historyeve0_
where historyeve0_.PRISM_GUID='i0acc051c00000133f57f4be2bbbdc5ef'
and historyeve0_.IS_DELETED=0
and (historyeve0_.EVENT_TYPE not in ('versionsSearchWithinQueryEvent','notesOfDecisionsSearchWithinQueryEvent','citingReferencesSearchWithinQueryEvent','tocSearchWithinQueryEvent','searchWithinDocumentEvent' ))
and (historyeve0_.PRODUCT_VIEW in ('DefaultProductView','TNPPlus','PLCUS','Indigo','INDIGOCA' )
or historyeve0_.PRODUCT_VIEW is null)
and historyeve0_.PRODUCT_SID='WestlawNext'
order by historyeve0_.EVENT_TYPE asc);
Sort (cost=12350.30..12350.80 rows=200 width=13) (actual time=11.686..11.693 rows=6 loops=1)
Output: historyeve0_.event_type
Sort Key: historyeve0_.event_type
Sort Method: quicksort Memory: 25kB
Buffers: shared hit=581
-> HashAggregate (cost=12340.65..12342.65 rows=200 width=13) (actual time=11.676..11.683 rows=6 loops=1)
Output: historyeve0_.event_type
Group Key: historyeve0_.event_type
Buffers: shared hit=581
-> Append (cost=0.55..12334.42 rows=2492 width=13) (actual time=0.035..10.759 rows=5566 loops=1)
Buffers: shared hit=581
-> Index Scan using history_event_display_timesta_prism_guid_display_timestamp__idx on hist2.history_event_display_timestamp_20200601 historyeve0_ (cost=0.55..1758.76 rows=362 width=13) (actual time=0.035..1.857 rows=1020 loops=1)
Output: historyeve0_.event_type
Index Cond: (((historyeve0_.prism_guid)::text = 'i0acc051c00000133f57f4be2bbbdc5ef'::text) AND ((historyeve0_.product_sid)::text = 'WestlawNext'::text) AND (historyeve0_.is_deleted = '0'::numeric))
Filter: (((historyeve0_.event_type)::text <> ALL ('{versionsSearchWithinQueryEvent,notesOfDecisionsSearchWithinQueryEvent,citingReferencesSearchWithinQueryEvent,tocSearchWithinQueryEvent,searchWithinDocumentEvent}'::text[])) AND (((historyeve0_.product_view)::text = ANY ('{DefaultProductView,TNPPlus,PLCUS,Indigo,INDIGOCA}'::text[])) OR (historyeve0_.product_view IS NULL)))
Rows Removed by Filter: 5
Buffers: shared hit=85
-> Index Scan using history_event_display_timesta_prism_guid_display_timestamp_idx1 on hist2.history_event_display_timestamp_20200701 historyeve0__1 (cost=0.55..1351.59 rows=276 width=13) (actual time=0.029..1.435 rows=800 loops=1)
Output: historyeve0__1.event_type
Index Cond: (((historyeve0__1.prism_guid)::text = 'i0acc051c00000133f57f4be2bbbdc5ef'::text) AND ((historyeve0__1.product_sid)::text = 'WestlawNext'::text) AND (historyeve0__1.is_deleted = '0'::numeric))
Filter: (((historyeve0__1.event_type)::text <> ALL ('{versionsSearchWithinQueryEvent,notesOfDecisionsSearchWithinQueryEvent,citingReferencesSearchWithinQueryEvent,tocSearchWithinQueryEvent,searchWithinDocumentEvent}'::text[])) AND (((historyeve0__1.product_view)::text = ANY ('{DefaultProductView,TNPPlus,PLCUS,Indigo,INDIGOCA}'::text[])) OR (historyeve0__1.product_view IS NULL)))
Rows Removed by Filter: 4
Buffers: shared hit=70
-> Index Scan using history_event_display_timesta_prism_guid_display_timestamp_idx2 on hist2.history_event_display_timestamp_20200801 historyeve0__2 (cost=0.55..468.34 rows=97 width=13) (actual time=0.031..0.331 rows=173 loops=1)
Output: historyeve0__2.event_type
Index Cond: (((historyeve0__2.prism_guid)::text = 'i0acc051c00000133f57f4be2bbbdc5ef'::text) AND ((historyeve0__2.product_sid)::text = 'WestlawNext'::text) AND (historyeve0__2.is_deleted = '0'::numeric))
Filter: (((historyeve0__2.event_type)::text <> ALL ('{versionsSearchWithinQueryEvent,notesOfDecisionsSearchWithinQueryEvent,citingReferencesSearchWithinQueryEvent,tocSearchWithinQueryEvent,searchWithinDocumentEvent}'::text[])) AND (((historyeve0__2.product_view)::text = ANY ('{DefaultProductView,TNPPlus,PLCUS,Indigo,INDIGOCA}'::text[])) OR (historyeve0__2.product_view IS NULL)))
Buffers: shared hit=26
-> Index Scan using history_event_display_timesta_prism_guid_display_timestamp_idx3 on hist2.history_event_display_timestamp_20200901 historyeve0__3 (cost=0.14..8.17 rows=1 width=118) (actual time=0.010..0.010 rows=0 loops=1)
Output: historyeve0__3.event_type
Index Cond: (((historyeve0__3.prism_guid)::text = 'i0acc051c00000133f57f4be2bbbdc5ef'::text) AND ((historyeve0__3.product_sid)::text = 'WestlawNext'::text) AND (historyeve0__3.is_deleted = '0'::numeric))
Filter: (((historyeve0__3.event_type)::text <> ALL ('{versionsSearchWithinQueryEvent,notesOfDecisionsSearchWithinQueryEvent,citingReferencesSearchWithinQueryEvent,tocSearchWithinQueryEvent,searchWithinDocumentEvent}'::text[])) AND (((historyeve0__3.product_view)::text = ANY ('{DefaultProductView,TNPPlus,PLCUS,Indigo,INDIGOCA}'::text[])) OR (historyeve0__3.product_view IS NULL)))
Buffers: shared hit=2
-> Index Scan using history_event_display_timesta_prism_guid_display_timestamp_idx4 on hist2.history_event_display_timestamp_20201001 historyeve0__4 (cost=0.14..8.17 rows=1 width=118) (actual time=0.014..0.014 rows=0 loops=1)
Output: historyeve0__4.event_type
Index Cond: (((historyeve0__4.prism_guid)::text = 'i0acc051c00000133f57f4be2bbbdc5ef'::text) AND ((historyeve0__4.product_sid)::text = 'WestlawNext'::text) AND (historyeve0__4.is_deleted = '0'::numeric))
Filter: (((historyeve0__4.event_type)::text <> ALL ('{versionsSearchWithinQueryEvent,notesOfDecisionsSearchWithinQueryEvent,citingReferencesSearchWithinQueryEvent,tocSearchWithinQueryEvent,searchWithinDocumentEvent}'::text[])) AND (((historyeve0__4.product_view)::text = ANY ('{DefaultProductView,TNPPlus,PLCUS,Indigo,INDIGOCA}'::text[])) OR (historyeve0__4.product_view IS NULL)))
Buffers: shared hit=2
-> Index Scan using history_event_display_timesta_prism_guid_display_timestamp_idx5 on hist2.history_event_display_timestamp_20201101 historyeve0__5 (cost=0.14..8.17 rows=1 width=118) (actual time=0.008..0.008 rows=0 loops=1)
Output: historyeve0__5.event_type
Index Cond: (((historyeve0__5.prism_guid)::text = 'i0acc051c00000133f57f4be2bbbdc5ef'::text) AND ((historyeve0__5.product_sid)::text = 'WestlawNext'::text) AND (historyeve0__5.is_deleted = '0'::numeric))
Filter: (((historyeve0__5.event_type)::text <> ALL ('{versionsSearchWithinQueryEvent,notesOfDecisionsSearchWithinQueryEvent,citingReferencesSearchWithinQueryEvent,tocSearchWithinQueryEvent,searchWithinDocumentEvent}'::text[])) AND (((historyeve0__5.product_view)::text = ANY ('{DefaultProductView,TNPPlus,PLCUS,Indigo,INDIGOCA}'::text[])) OR (historyeve0__5.product_view IS NULL)))
Buffers: shared hit=2
-> Index Scan using history_event_display_timesta_prism_guid_display_timestamp_idx6 on hist2.history_event_display_timestamp_20201201 historyeve0__6 (cost=0.55..766.81 rows=155 width=13) (actual time=0.030..0.600 rows=329 loops=1)
Output: historyeve0__6.event_type
Index Cond: (((historyeve0__6.prism_guid)::text = 'i0acc051c00000133f57f4be2bbbdc5ef'::text) AND ((historyeve0__6.product_sid)::text = 'WestlawNext'::text) AND (historyeve0__6.is_deleted = '0'::numeric))
Filter: (((historyeve0__6.event_type)::text <> ALL ('{versionsSearchWithinQueryEvent,notesOfDecisionsSearchWithinQueryEvent,citingReferencesSearchWithinQueryEvent,tocSearchWithinQueryEvent,searchWithinDocumentEvent}'::text[])) AND (((historyeve0__6.product_view)::text = ANY ('{DefaultProductView,TNPPlus,PLCUS,Indigo,INDIGOCA}'::text[])) OR (historyeve0__6.product_view IS NULL)))
Rows Removed by Filter: 1
Buffers: shared hit=36
-> Index Scan using history_event_display_timesta_prism_guid_display_timestamp_idx7 on hist2.history_event_display_timestamp_20210101 historyeve0__7 (cost=0.55..371.57 rows=76 width=13) (actual time=0.028..0.271 rows=138 loops=1)
Output: historyeve0__7.event_type
Index Cond: (((historyeve0__7.prism_guid)::text = 'i0acc051c00000133f57f4be2bbbdc5ef'::text) AND ((historyeve0__7.product_sid)::text = 'WestlawNext'::text) AND (historyeve0__7.is_deleted = '0'::numeric))
Filter: (((historyeve0__7.event_type)::text <> ALL ('{versionsSearchWithinQueryEvent,notesOfDecisionsSearchWithinQueryEvent,citingReferencesSearchWithinQueryEvent,tocSearchWithinQueryEvent,searchWithinDocumentEvent}'::text[])) AND (((historyeve0__7.product_view)::text = ANY ('{DefaultProductView,TNPPlus,PLCUS,Indigo,INDIGOCA}'::text[])) OR (historyeve0__7.product_view IS NULL)))
Buffers: shared hit=23
-> Index Scan using history_event_display_timesta_prism_guid_display_timestamp_idx8 on hist2.history_event_display_timestamp_20210201 historyeve0__8 (cost=0.55..1960.35 rows=403 width=13) (actual time=0.029..1.281 rows=716 loops=1)
Output: historyeve0__8.event_type
Index Cond: (((historyeve0__8.prism_guid)::text = 'i0acc051c00000133f57f4be2bbbdc5ef'::text) AND ((historyeve0__8.product_sid)::text = 'WestlawNext'::text) AND (historyeve0__8.is_deleted = '0'::numeric))
Filter: (((historyeve0__8.event_type)::text <> ALL ('{versionsSearchWithinQueryEvent,notesOfDecisionsSearchWithinQueryEvent,citingReferencesSearchWithinQueryEvent,tocSearchWithinQueryEvent,searchWithinDocumentEvent}'::text[])) AND (((historyeve0__8.product_view)::text = ANY ('{DefaultProductView,TNPPlus,PLCUS,Indigo,INDIGOCA}'::text[])) OR (historyeve0__8.product_view IS NULL)))
Rows Removed by Filter: 1
Buffers: shared hit=66
-> Index Scan using history_event_display_timesta_prism_guid_display_timestamp_idx9 on hist2.history_event_display_timestamp_20210301 historyeve0__9 (cost=0.55..1371.73 rows=279 width=13) (actual time=0.029..0.877 rows=488 loops=1)
Output: historyeve0__9.event_type
Index Cond: (((historyeve0__9.prism_guid)::text = 'i0acc051c00000133f57f4be2bbbdc5ef'::text) AND ((historyeve0__9.product_sid)::text = 'WestlawNext'::text) AND (historyeve0__9.is_deleted = '0'::numeric))
Filter: (((historyeve0__9.event_type)::text <> ALL ('{versionsSearchWithinQueryEvent,notesOfDecisionsSearchWithinQueryEvent,citingReferencesSearchWithinQueryEvent,tocSearchWithinQueryEvent,searchWithinDocumentEvent}'::text[])) AND (((historyeve0__9.product_view)::text = ANY ('{DefaultProductView,TNPPlus,PLCUS,Indigo,INDIGOCA}'::text[])) OR (historyeve0__9.product_view IS NULL)))
Rows Removed by Filter: 3
Buffers: shared hit=50
-> Index Scan using history_event_display_timest_prism_guid_display_timestamp_idx10 on hist2.history_event_display_timestamp_20210401 historyeve0__10 (cost=0.55..1625.74 rows=325 width=13) (actual time=0.032..1.248 rows=701 loops=1)
Output: historyeve0__10.event_type
Index Cond: (((historyeve0__10.prism_guid)::text = 'i0acc051c00000133f57f4be2bbbdc5ef'::text) AND ((historyeve0__10.product_sid)::text = 'WestlawNext'::text) AND (historyeve0__10.is_deleted = '0'::numeric))
Filter: (((historyeve0__10.event_type)::text <> ALL ('{versionsSearchWithinQueryEvent,notesOfDecisionsSearchWithinQueryEvent,citingReferencesSearchWithinQueryEvent,tocSearchWithinQueryEvent,searchWithinDocumentEvent}'::text[])) AND (((historyeve0__10.product_view)::text = ANY ('{DefaultProductView,TNPPlus,PLCUS,Indigo,INDIGOCA}'::text[])) OR (historyeve0__10.product_view IS NULL)))
Rows Removed by Filter: 2
Buffers: shared hit=64
-> Index Scan using history_event_display_timest_prism_guid_display_timestamp_idx11 on hist2.history_event_display_timestamp_20210501 historyeve0__11 (cost=0.55..1738.67 rows=351 width=13) (actual time=0.029..1.175 rows=646 loops=1)
Output: historyeve0__11.event_type
Index Cond: (((historyeve0__11.prism_guid)::text = 'i0acc051c00000133f57f4be2bbbdc5ef'::text) AND ((historyeve0__11.product_sid)::text = 'WestlawNext'::text) AND (historyeve0__11.is_deleted = '0'::numeric))
Filter: (((historyeve0__11.event_type)::text <> ALL ('{versionsSearchWithinQueryEvent,notesOfDecisionsSearchWithinQueryEvent,citingReferencesSearchWithinQueryEvent,tocSearchWithinQueryEvent,searchWithinDocumentEvent}'::text[])) AND (((historyeve0__11.product_view)::text = ANY ('{DefaultProductView,TNPPlus,PLCUS,Indigo,INDIGOCA}'::text[])) OR (historyeve0__11.product_view IS NULL)))
Rows Removed by Filter: 4
Buffers: shared hit=64
-> Index Scan using history_event_display_timest_prism_guid_display_timestamp_idx12 on hist2.history_event_display_timestamp_20210601 historyeve0__12 (cost=0.55..794.55 rows=152 width=13) (actual time=0.032..0.784 rows=429 loops=1)
Output: historyeve0__12.event_type
Index Cond: (((historyeve0__12.prism_guid)::text = 'i0acc051c00000133f57f4be2bbbdc5ef'::text) AND ((historyeve0__12.product_sid)::text = 'WestlawNext'::text) AND (historyeve0__12.is_deleted = '0'::numeric))
Filter: (((historyeve0__12.event_type)::text <> ALL ('{versionsSearchWithinQueryEvent,notesOfDecisionsSearchWithinQueryEvent,citingReferencesSearchWithinQueryEvent,tocSearchWithinQueryEvent,searchWithinDocumentEvent}'::text[])) AND (((historyeve0__12.product_view)::text = ANY ('{DefaultProductView,TNPPlus,PLCUS,Indigo,INDIGOCA}'::text[])) OR (historyeve0__12.product_view IS NULL)))
Rows Removed by Filter: 3
Buffers: shared hit=51
-> Index Scan using history_event_display_timest_prism_guid_display_timestamp_idx13 on hist2.history_event_display_timestamp_20210701 historyeve0__13 (cost=0.55..23.56 rows=5 width=12) (actual time=0.028..0.245 rows=126loops=1)
Output: historyeve0__13.event_type
Index Cond: (((historyeve0__13.prism_guid)::text = 'i0acc051c00000133f57f4be2bbbdc5ef'::text) AND ((historyeve0__13.product_sid)::text = 'WestlawNext'::text) AND (historyeve0__13.is_deleted = '0'::numeric))
Filter: (((historyeve0__13.event_type)::text <> ALL ('{versionsSearchWithinQueryEvent,notesOfDecisionsSearchWithinQueryEvent,citingReferencesSearchWithinQueryEvent,tocSearchWithinQueryEvent,searchWithinDocumentEvent}'::text[])) AND (((historyeve0__13.product_view)::text = ANY ('{DefaultProductView,TNPPlus,PLCUS,Indigo,INDIGOCA}'::text[])) OR (historyeve0__13.product_view IS NULL)))
Buffers: shared hit=22
-> Index Scan using history_event_display_timest_prism_guid_display_timestamp_idx14 on hist2.history_event_display_timestamp_20210801 historyeve0__14 (cost=0.55..8.58 rows=1 width=12) (actual time=0.013..0.014 rows=0 loops=1)
Output: historyeve0__14.event_type
Index Cond: (((historyeve0__14.prism_guid)::text = 'i0acc051c00000133f57f4be2bbbdc5ef'::text) AND ((historyeve0__14.product_sid)::text = 'WestlawNext'::text) AND (historyeve0__14.is_deleted = '0'::numeric))
Filter: (((historyeve0__14.event_type)::text <> ALL ('{versionsSearchWithinQueryEvent,notesOfDecisionsSearchWithinQueryEvent,citingReferencesSearchWithinQueryEvent,tocSearchWithinQueryEvent,searchWithinDocumentEvent}'::text[])) AND (((historyeve0__14.product_view)::text = ANY ('{DefaultProductView,TNPPlus,PLCUS,Indigo,INDIGOCA}'::text[])) OR (historyeve0__14.product_view IS NULL)))
Buffers: shared hit=4
-> Index Scan using history_event_display_timest_prism_guid_display_timestamp_idx15 on hist2.history_event_display_timestamp_20210901 historyeve0__15 (cost=0.14..8.17 rows=1 width=118) (actual time=0.011..0.011 rows=0 loops=1)
Output: historyeve0__15.event_type
Index Cond: (((historyeve0__15.prism_guid)::text = 'i0acc051c00000133f57f4be2bbbdc5ef'::text) AND ((historyeve0__15.product_sid)::text = 'WestlawNext'::text) AND (historyeve0__15.is_deleted = '0'::numeric))
Filter: (((historyeve0__15.event_type)::text <> ALL ('{versionsSearchWithinQueryEvent,notesOfDecisionsSearchWithinQueryEvent,citingReferencesSearchWithinQueryEvent,tocSearchWithinQueryEvent,searchWithinDocumentEvent}'::text[])) AND (((historyeve0__15.product_view)::text = ANY ('{DefaultProductView,TNPPlus,PLCUS,Indigo,INDIGOCA}'::text[])) OR (historyeve0__15.product_view IS NULL)))
Buffers: shared hit=2
-> Index Scan using history_event_display_timest_prism_guid_display_timestamp_idx32 on hist2.history_event_display_timestamp_20211001 historyeve0__16 (cost=0.14..8.17 rows=1 width=118) (actual time=0.011..0.011 rows=0 loops=1)
Output: historyeve0__16.event_type
Index Cond: (((historyeve0__16.prism_guid)::text = 'i0acc051c00000133f57f4be2bbbdc5ef'::text) AND ((historyeve0__16.product_sid)::text = 'WestlawNext'::text) AND (historyeve0__16.is_deleted = '0'::numeric))
Filter: (((historyeve0__16.event_type)::text <> ALL ('{versionsSearchWithinQueryEvent,notesOfDecisionsSearchWithinQueryEvent,citingReferencesSearchWithinQueryEvent,tocSearchWithinQueryEvent,searchWithinDocumentEvent}'::text[])) AND (((historyeve0__16.product_view)::text = ANY ('{DefaultProductView,TNPPlus,PLCUS,Indigo,INDIGOCA}'::text[])) OR (historyeve0__16.product_view IS NULL)))
Buffers: shared hit=2
-> Index Scan using history_event_display_timest_prism_guid_display_timestamp_idx34 on hist2.history_event_display_timestamp_20211101 historyeve0__17 (cost=0.14..8.17 rows=1 width=118) (actual time=0.011..0.011 rows=0 loops=1)
Output: historyeve0__17.event_type
Index Cond: (((historyeve0__17.prism_guid)::text = 'i0acc051c00000133f57f4be2bbbdc5ef'::text) AND ((historyeve0__17.product_sid)::text = 'WestlawNext'::text) AND (historyeve0__17.is_deleted = '0'::numeric))
Filter: (((historyeve0__17.event_type)::text <> ALL ('{versionsSearchWithinQueryEvent,notesOfDecisionsSearchWithinQueryEvent,citingReferencesSearchWithinQueryEvent,tocSearchWithinQueryEvent,searchWithinDocumentEvent}'::text[])) AND (((historyeve0__17.product_view)::text = ANY ('{DefaultProductView,TNPPlus,PLCUS,Indigo,INDIGOCA}'::text[])) OR (historyeve0__17.product_view IS NULL)))
Buffers: shared hit=2
-> Index Scan using history_event_display_timest_prism_guid_display_timestamp_idx36 on hist2.history_event_display_timestamp_20211201 historyeve0__18 (cost=0.14..8.17 rows=1 width=118) (actual time=0.013..0.013 rows=0 loops=1)
Output: historyeve0__18.event_type
Index Cond: (((historyeve0__18.prism_guid)::text = 'i0acc051c00000133f57f4be2bbbdc5ef'::text) AND ((historyeve0__18.product_sid)::text = 'WestlawNext'::text) AND (historyeve0__18.is_deleted = '0'::numeric))
Filter: (((historyeve0__18.event_type)::text <> ALL ('{versionsSearchWithinQueryEvent,notesOfDecisionsSearchWithinQueryEvent,citingReferencesSearchWithinQueryEvent,tocSearchWithinQueryEvent,searchWithinDocumentEvent}'::text[])) AND (((historyeve0__18.product_view)::text = ANY ('{DefaultProductView,TNPPlus,PLCUS,Indigo,INDIGOCA}'::text[])) OR (historyeve0__18.product_view IS NULL)))
Buffers: shared hit=2
-> Index Scan using history_event_display_timest_prism_guid_display_timestamp_idx38 on hist2.history_event_display_timestamp_20220101 historyeve0__19 (cost=0.14..8.17 rows=1 width=118) (actual time=0.013..0.013 rows=0 loops=1)
Output: historyeve0__19.event_type
Index Cond: (((historyeve0__19.prism_guid)::text = 'i0acc051c00000133f57f4be2bbbdc5ef'::text) AND ((historyeve0__19.product_sid)::text = 'WestlawNext'::text) AND (historyeve0__19.is_deleted = '0'::numeric))
Filter: (((historyeve0__19.event_type)::text <> ALL ('{versionsSearchWithinQueryEvent,notesOfDecisionsSearchWithinQueryEvent,citingReferencesSearchWithinQueryEvent,tocSearchWithinQueryEvent,searchWithinDocumentEvent}'::text[])) AND (((historyeve0__19.product_view)::text = ANY ('{DefaultProductView,TNPPlus,PLCUS,Indigo,INDIGOCA}'::text[])) OR (historyeve0__19.product_view IS NULL)))
Buffers: shared hit=2
-> Index Scan using history_event_display_timest_prism_guid_display_timestamp_idx40 on hist2.history_event_display_timestamp_20220201 historyeve0__20 (cost=0.14..8.17 rows=1 width=118) (actual time=0.013..0.013 rows=0 loops=1)
Output: historyeve0__20.event_type
Index Cond: (((historyeve0__20.prism_guid)::text = 'i0acc051c00000133f57f4be2bbbdc5ef'::text) AND ((historyeve0__20.product_sid)::text = 'WestlawNext'::text) AND (historyeve0__20.is_deleted = '0'::numeric))
Filter: (((historyeve0__20.event_type)::text <> ALL ('{versionsSearchWithinQueryEvent,notesOfDecisionsSearchWithinQueryEvent,citingReferencesSearchWithinQueryEvent,tocSearchWithinQueryEvent,searchWithinDocumentEvent}'::text[])) AND (((historyeve0__20.product_view)::text = ANY ('{DefaultProductView,TNPPlus,PLCUS,Indigo,INDIGOCA}'::text[])) OR (historyeve0__20.product_view IS NULL)))
Buffers: shared hit=2
-> Index Scan using history_event_display_timest_prism_guid_display_timestamp_idx42 on hist2.history_event_display_timestamp_20220301 historyeve0__21 (cost=0.14..8.17 rows=1 width=118) (actual time=0.010..0.010 rows=0 loops=1)
Output: historyeve0__21.event_type
Index Cond: (((historyeve0__21.prism_guid)::text = 'i0acc051c00000133f57f4be2bbbdc5ef'::text) AND ((historyeve0__21.product_sid)::text = 'WestlawNext'::text) AND (historyeve0__21.is_deleted = '0'::numeric))
Filter: (((historyeve0__21.event_type)::text <> ALL ('{versionsSearchWithinQueryEvent,notesOfDecisionsSearchWithinQueryEvent,citingReferencesSearchWithinQueryEvent,tocSearchWithinQueryEvent,searchWithinDocumentEvent}'::text[])) AND (((historyeve0__21.product_view)::text = ANY ('{DefaultProductView,TNPPlus,PLCUS,Indigo,INDIGOCA}'::text[])) OR (historyeve0__21.product_view IS NULL)))
Buffers: shared hit=2
Settings: effective_cache_size = '88283528kB', enable_bitmapscan = 'off', search_path = 'hist2'
Planning Time: 3.762 ms
Execution Time: 11.863 ms
SQL Hash: 901113095, Plan Hash: -1815601721
(133 rows)
explain (analyze, verbose, costs, settings, buffers, timing, summary, hashes)
(/*+ IndexScan(historyeve0_ history_event_idx02) */ select distinct historyeve0_.EVENT_TYPE as junk123
from HISTORY_EVENT historyeve0_
where historyeve0_.PRISM_GUID='i0acc051c00000133f57f4be2bbbdc5ef'
and historyeve0_.IS_DELETED=0
and (historyeve0_.EVENT_TYPE not in ('versionsSearchWithinQueryEvent','notesOfDecisionsSearchWithinQueryEvent','citingReferencesSearchWithinQueryEvent','tocSearchWithinQueryEvent','searchWithinDocumentEvent' ))
and (historyeve0_.PRODUCT_VIEW in ('DefaultProductView','TNPPlus','PLCUS','Indigo','INDIGOCA' )
or historyeve0_.PRODUCT_VIEW is null)
and historyeve0_.PRODUCT_SID='WestlawNext'
order by historyeve0_.EVENT_TYPE asc);
Sort (cost=12350.30..12350.80 rows=200 width=13) (actual time=646.880..646.889 rows=6 loops=1)
Output: historyeve0_.event_type
Sort Key: historyeve0_.event_type
Sort Method: quicksort Memory: 25kB
Buffers: shared hit=171 read=880
I/O Timings: read=333.505
-> HashAggregate (cost=12340.65..12342.65 rows=200 width=13) (actual time=646.868..646.878 rows=6 loops=1)
Output: historyeve0_.event_type
Group Key: historyeve0_.event_type
Buffers: shared hit=171 read=880
I/O Timings: read=333.505
-> Append (cost=0.55..12334.42 rows=2492 width=13) (actual time=1.915..645.743 rows=5566 loops=1)
Buffers: shared hit=171 read=880
I/O Timings: read=333.505
-> Index Scan using history_event_display_timesta_prism_guid_display_timestamp__idx on hist2.history_event_display_timestamp_20200601 historyeve0_ (cost=0.55..1758.76 rows=362 width=13) (actual time=1.914..122.776 rows=1020 loops=1)
Output: historyeve0_.event_type
Index Cond: (((historyeve0_.prism_guid)::text = 'i0acc051c00000133f57f4be2bbbdc5ef'::text) AND ((historyeve0_.product_sid)::text = 'WestlawNext'::text) AND (historyeve0_.is_deleted = '0'::numeric))
Filter: (((historyeve0_.event_type)::text <> ALL ('{versionsSearchWithinQueryEvent,notesOfDecisionsSearchWithinQueryEvent,citingReferencesSearchWithinQueryEvent,tocSearchWithinQueryEvent,searchWithinDocumentEvent}'::text[])) AND (((historyeve0_.product_view)::text = ANY ('{DefaultProductView,TNPPlus,PLCUS,Indigo,INDIGOCA}'::text[])) OR (historyeve0_.product_view IS NULL)))
Rows Removed by Filter: 5
Buffers: shared hit=15 read=125
I/O Timings: read=76.762
-> Index Scan using history_event_display_timesta_prism_guid_display_timestamp_idx1 on hist2.history_event_display_timestamp_20200701 historyeve0__1 (cost=0.55..1351.59 rows=276 width=13) (actual time=1.869..87.808 rows=800 loops=1)
Output: historyeve0__1.event_type
Index Cond: (((historyeve0__1.prism_guid)::text = 'i0acc051c00000133f57f4be2bbbdc5ef'::text) AND ((historyeve0__1.product_sid)::text = 'WestlawNext'::text) AND (historyeve0__1.is_deleted = '0'::numeric))
Filter: (((historyeve0__1.event_type)::text <> ALL ('{versionsSearchWithinQueryEvent,notesOfDecisionsSearchWithinQueryEvent,citingReferencesSearchWithinQueryEvent,tocSearchWithinQueryEvent,searchWithinDocumentEvent}'::text[])) AND (((historyeve0__1.product_view)::text = ANY ('{DefaultProductView,TNPPlus,PLCUS,Indigo,INDIGOCA}'::text[])) OR (historyeve0__1.product_view IS NULL)))
Rows Removed by Filter: 4
Buffers: shared hit=7 read=85
I/O Timings: read=60.467
-> Index Scan using history_event_display_timesta_prism_guid_display_timestamp_idx2 on hist2.history_event_display_timestamp_20200801 historyeve0__2 (cost=0.55..468.34 rows=97 width=13) (actual time=1.479..12.984 rows=173 loops=1)
Output: historyeve0__2.event_type
Index Cond: (((historyeve0__2.prism_guid)::text = 'i0acc051c00000133f57f4be2bbbdc5ef'::text) AND ((historyeve0__2.product_sid)::text = 'WestlawNext'::text) AND (historyeve0__2.is_deleted = '0'::numeric))
Filter: (((historyeve0__2.event_type)::text <> ALL ('{versionsSearchWithinQueryEvent,notesOfDecisionsSearchWithinQueryEvent,citingReferencesSearchWithinQueryEvent,tocSearchWithinQueryEvent,searchWithinDocumentEvent}'::text[])) AND (((historyeve0__2.product_view)::text = ANY ('{DefaultProductView,TNPPlus,PLCUS,Indigo,INDIGOCA}'::text[])) OR (historyeve0__2.product_view IS NULL)))
Buffers: shared hit=15 read=13
I/O Timings: read=8.322
-> Index Scan using history_event_display_timesta_prism_guid_display_timestamp_idx3 on hist2.history_event_display_timestamp_20200901 historyeve0__3 (cost=0.14..8.17 rows=1 width=118) (actual time=0.012..0.012 rows=0 loops=1)
Output: historyeve0__3.event_type
Index Cond: (((historyeve0__3.prism_guid)::text = 'i0acc051c00000133f57f4be2bbbdc5ef'::text) AND ((historyeve0__3.product_sid)::text = 'WestlawNext'::text) AND (historyeve0__3.is_deleted = '0'::numeric))
Filter: (((historyeve0__3.event_type)::text <> ALL ('{versionsSearchWithinQueryEvent,notesOfDecisionsSearchWithinQueryEvent,citingReferencesSearchWithinQueryEvent,tocSearchWithinQueryEvent,searchWithinDocumentEvent}'::text[])) AND (((historyeve0__3.product_view)::text = ANY ('{DefaultProductView,TNPPlus,PLCUS,Indigo,INDIGOCA}'::text[])) OR (historyeve0__3.product_view IS NULL)))
Buffers: shared hit=2
-> Index Scan using history_event_display_timesta_prism_guid_display_timestamp_idx4 on hist2.history_event_display_timestamp_20201001 historyeve0__4 (cost=0.14..8.17 rows=1 width=118) (actual time=0.014..0.014 rows=0 loops=1)
Output: historyeve0__4.event_type
Index Cond: (((historyeve0__4.prism_guid)::text = 'i0acc051c00000133f57f4be2bbbdc5ef'::text) AND ((historyeve0__4.product_sid)::text = 'WestlawNext'::text) AND (historyeve0__4.is_deleted = '0'::numeric))
Filter: (((historyeve0__4.event_type)::text <> ALL ('{versionsSearchWithinQueryEvent,notesOfDecisionsSearchWithinQueryEvent,citingReferencesSearchWithinQueryEvent,tocSearchWithinQueryEvent,searchWithinDocumentEvent}'::text[])) AND (((historyeve0__4.product_view)::text = ANY ('{DefaultProductView,TNPPlus,PLCUS,Indigo,INDIGOCA}'::text[])) OR (historyeve0__4.product_view IS NULL)))
Buffers: shared hit=2
-> Index Scan using history_event_display_timesta_prism_guid_display_timestamp_idx5 on hist2.history_event_display_timestamp_20201101 historyeve0__5 (cost=0.14..8.17 rows=1 width=118) (actual time=0.011..0.011 rows=0 loops=1)
Output: historyeve0__5.event_type
Index Cond: (((historyeve0__5.prism_guid)::text = 'i0acc051c00000133f57f4be2bbbdc5ef'::text) AND ((historyeve0__5.product_sid)::text = 'WestlawNext'::text) AND (historyeve0__5.is_deleted = '0'::numeric))
Filter: (((historyeve0__5.event_type)::text <> ALL ('{versionsSearchWithinQueryEvent,notesOfDecisionsSearchWithinQueryEvent,citingReferencesSearchWithinQueryEvent,tocSearchWithinQueryEvent,searchWithinDocumentEvent}'::text[])) AND (((historyeve0__5.product_view)::text = ANY ('{DefaultProductView,TNPPlus,PLCUS,Indigo,INDIGOCA}'::text[])) OR (historyeve0__5.product_view IS NULL)))
Buffers: shared hit=2
-> Index Scan using history_event_display_timesta_prism_guid_display_timestamp_idx6 on hist2.history_event_display_timestamp_20201201 historyeve0__6 (cost=0.55..766.81 rows=155 width=13) (actual time=1.172..31.168 rows=329 loops=1)
Output: historyeve0__6.event_type
Index Cond: (((historyeve0__6.prism_guid)::text = 'i0acc051c00000133f57f4be2bbbdc5ef'::text) AND ((historyeve0__6.product_sid)::text = 'WestlawNext'::text) AND (historyeve0__6.is_deleted = '0'::numeric))
Filter: (((historyeve0__6.event_type)::text <> ALL ('{versionsSearchWithinQueryEvent,notesOfDecisionsSearchWithinQueryEvent,citingReferencesSearchWithinQueryEvent,tocSearchWithinQueryEvent,searchWithinDocumentEvent}'::text[])) AND (((historyeve0__6.product_view)::text = ANY ('{DefaultProductView,TNPPlus,PLCUS,Indigo,INDIGOCA}'::text[])) OR (historyeve0__6.product_view IS NULL)))
Rows Removed by Filter: 1
Buffers: shared hit=11 read=34
I/O Timings: read=20.077
-> Index Scan using history_event_display_timesta_prism_guid_display_timestamp_idx7 on hist2.history_event_display_timestamp_20210101 historyeve0__7 (cost=0.55..371.57 rows=76 width=13) (actual time=1.467..15.204 rows=138 loops=1)
Output: historyeve0__7.event_type
Index Cond: (((historyeve0__7.prism_guid)::text = 'i0acc051c00000133f57f4be2bbbdc5ef'::text) AND ((historyeve0__7.product_sid)::text = 'WestlawNext'::text) AND (historyeve0__7.is_deleted = '0'::numeric))
Filter: (((historyeve0__7.event_type)::text <> ALL ('{versionsSearchWithinQueryEvent,notesOfDecisionsSearchWithinQueryEvent,citingReferencesSearchWithinQueryEvent,tocSearchWithinQueryEvent,searchWithinDocumentEvent}'::text[])) AND (((historyeve0__7.product_view)::text = ANY ('{DefaultProductView,TNPPlus,PLCUS,Indigo,INDIGOCA}'::text[])) OR (historyeve0__7.product_view IS NULL)))
Buffers: shared hit=12 read=17
I/O Timings: read=8.436
-> Index Scan using history_event_display_timesta_prism_guid_display_timestamp_idx8 on hist2.history_event_display_timestamp_20210201 historyeve0__8 (cost=0.55..1960.35 rows=403 width=13) (actual time=2.678..87.188 rows=716 loops=1)
Output: historyeve0__8.event_type
Index Cond: (((historyeve0__8.prism_guid)::text = 'i0acc051c00000133f57f4be2bbbdc5ef'::text) AND ((historyeve0__8.product_sid)::text = 'WestlawNext'::text) AND (historyeve0__8.is_deleted = '0'::numeric))
Filter: (((historyeve0__8.event_type)::text <> ALL ('{versionsSearchWithinQueryEvent,notesOfDecisionsSearchWithinQueryEvent,citingReferencesSearchWithinQueryEvent,tocSearchWithinQueryEvent,searchWithinDocumentEvent}'::text[])) AND (((historyeve0__8.product_view)::text = ANY ('{DefaultProductView,TNPPlus,PLCUS,Indigo,INDIGOCA}'::text[])) OR (historyeve0__8.product_view IS NULL)))
Rows Removed by Filter: 1
Buffers: shared hit=12 read=113
I/O Timings: read=51.819
-> Index Scan using history_event_display_timesta_prism_guid_display_timestamp_idx9 on hist2.history_event_display_timestamp_20210301 historyeve0__9 (cost=0.55..1371.73 rows=279 width=13) (actual time=1.837..68.129 rows=488 loops=1)
Output: historyeve0__9.event_type
Index Cond: (((historyeve0__9.prism_guid)::text = 'i0acc051c00000133f57f4be2bbbdc5ef'::text) AND ((historyeve0__9.product_sid)::text = 'WestlawNext'::text) AND (historyeve0__9.is_deleted = '0'::numeric))
Filter: (((historyeve0__9.event_type)::text <> ALL ('{versionsSearchWithinQueryEvent,notesOfDecisionsSearchWithinQueryEvent,citingReferencesSearchWithinQueryEvent,tocSearchWithinQueryEvent,searchWithinDocumentEvent}'::text[])) AND (((historyeve0__9.product_view)::text = ANY ('{DefaultProductView,TNPPlus,PLCUS,Indigo,INDIGOCA}'::text[])) OR (historyeve0__9.product_view IS NULL)))
Rows Removed by Filter: 3
Buffers: shared hit=14 read=112
I/O Timings: read=33.044
-> Index Scan using history_event_display_timest_prism_guid_display_timestamp_idx10 on hist2.history_event_display_timestamp_20210401 historyeve0__10 (cost=0.55..1625.74 rows=325 width=13) (actual time=0.947..103.137 rows=701 loops=1)
Output: historyeve0__10.event_type
Index Cond: (((historyeve0__10.prism_guid)::text = 'i0acc051c00000133f57f4be2bbbdc5ef'::text) AND ((historyeve0__10.product_sid)::text = 'WestlawNext'::text) AND (historyeve0__10.is_deleted = '0'::numeric))
Filter: (((historyeve0__10.event_type)::text <> ALL ('{versionsSearchWithinQueryEvent,notesOfDecisionsSearchWithinQueryEvent,citingReferencesSearchWithinQueryEvent,tocSearchWithinQueryEvent,searchWithinDocumentEvent}'::text[])) AND (((historyeve0__10.product_view)::text = ANY ('{DefaultProductView,TNPPlus,PLCUS,Indigo,INDIGOCA}'::text[])) OR (historyeve0__10.product_view IS NULL)))
Rows Removed by Filter: 2
Buffers: shared hit=15 read=139
I/O Timings: read=51.782
-> Index Scan using history_event_display_timest_prism_guid_display_timestamp_idx11 on hist2.history_event_display_timestamp_20210501 historyeve0__11 (cost=0.55..1738.67 rows=351 width=13) (actual time=1.056..53.731 rows=646 loops=1)
Output: historyeve0__11.event_type
Index Cond: (((historyeve0__11.prism_guid)::text = 'i0acc051c00000133f57f4be2bbbdc5ef'::text) AND ((historyeve0__11.product_sid)::text = 'WestlawNext'::text) AND (historyeve0__11.is_deleted = '0'::numeric))
Filter: (((historyeve0__11.event_type)::text <> ALL ('{versionsSearchWithinQueryEvent,notesOfDecisionsSearchWithinQueryEvent,citingReferencesSearchWithinQueryEvent,tocSearchWithinQueryEvent,searchWithinDocumentEvent}'::text[])) AND (((historyeve0__11.product_view)::text = ANY ('{DefaultProductView,TNPPlus,PLCUS,Indigo,INDIGOCA}'::text[])) OR (historyeve0__11.product_view IS NULL)))
Rows Removed by Filter: 4
Buffers: shared hit=19 read=118
I/O Timings: read=6.834
-> Index Scan using history_event_display_timest_prism_guid_display_timestamp_idx12 on hist2.history_event_display_timestamp_20210601 historyeve0__12 (cost=0.55..794.55 rows=152 width=13) (actual time=1.576..45.888 rows=429 loops=1)
Output: historyeve0__12.event_type
Index Cond: (((historyeve0__12.prism_guid)::text = 'i0acc051c00000133f57f4be2bbbdc5ef'::text) AND ((historyeve0__12.product_sid)::text = 'WestlawNext'::text) AND (historyeve0__12.is_deleted = '0'::numeric))
Filter: (((historyeve0__12.event_type)::text <> ALL ('{versionsSearchWithinQueryEvent,notesOfDecisionsSearchWithinQueryEvent,citingReferencesSearchWithinQueryEvent,tocSearchWithinQueryEvent,searchWithinDocumentEvent}'::text[])) AND (((historyeve0__12.product_view)::text = ANY ('{DefaultProductView,TNPPlus,PLCUS,Indigo,INDIGOCA}'::text[])) OR (historyeve0__12.product_view IS NULL)))
Rows Removed by Filter: 3
Buffers: shared hit=17 read=91
I/O Timings: read=8.620
-> Index Scan using history_event_display_timest_prism_guid_display_timestamp_idx13 on hist2.history_event_display_timestamp_20210701 historyeve0__13 (cost=0.55..23.56 rows=5 width=12) (actual time=1.128..16.989 rows=126 loops=1)
Output: historyeve0__13.event_type
Index Cond: (((historyeve0__13.prism_guid)::text = 'i0acc051c00000133f57f4be2bbbdc5ef'::text) AND ((historyeve0__13.product_sid)::text = 'WestlawNext'::text) AND (historyeve0__13.is_deleted = '0'::numeric))
Filter: (((historyeve0__13.event_type)::text <> ALL ('{versionsSearchWithinQueryEvent,notesOfDecisionsSearchWithinQueryEvent,citingReferencesSearchWithinQueryEvent,tocSearchWithinQueryEvent,searchWithinDocumentEvent}'::text[])) AND (((historyeve0__13.product_view)::text = ANY ('{DefaultProductView,TNPPlus,PLCUS,Indigo,INDIGOCA}'::text[])) OR (historyeve0__13.product_view IS NULL)))
Buffers: shared hit=10 read=33
I/O Timings: read=7.341
-> Index Scan using history_event_display_timest_prism_guid_display_timestamp_idx14 on hist2.history_event_display_timestamp_20210801 historyeve0__14 (cost=0.55..8.58 rows=1 width=12) (actual time=0.024..0.025 rows=0 loops=1)
Output: historyeve0__14.event_type
Index Cond: (((historyeve0__14.prism_guid)::text = 'i0acc051c00000133f57f4be2bbbdc5ef'::text) AND ((historyeve0__14.product_sid)::text = 'WestlawNext'::text) AND (historyeve0__14.is_deleted = '0'::numeric))
Filter: (((historyeve0__14.event_type)::text <> ALL ('{versionsSearchWithinQueryEvent,notesOfDecisionsSearchWithinQueryEvent,citingReferencesSearchWithinQueryEvent,tocSearchWithinQueryEvent,searchWithinDocumentEvent}'::text[])) AND (((historyeve0__14.product_view)::text = ANY ('{DefaultProductView,TNPPlus,PLCUS,Indigo,INDIGOCA}'::text[])) OR (historyeve0__14.product_view IS NULL)))
Buffers: shared hit=4
-> Index Scan using history_event_display_timest_prism_guid_display_timestamp_idx15 on hist2.history_event_display_timestamp_20210901 historyeve0__15 (cost=0.14..8.17 rows=1 width=118) (actual time=0.015..0.015 rows=0 loops=1)
Output: historyeve0__15.event_type
Index Cond: (((historyeve0__15.prism_guid)::text = 'i0acc051c00000133f57f4be2bbbdc5ef'::text) AND ((historyeve0__15.product_sid)::text = 'WestlawNext'::text) AND (historyeve0__15.is_deleted = '0'::numeric))
Filter: (((historyeve0__15.event_type)::text <> ALL ('{versionsSearchWithinQueryEvent,notesOfDecisionsSearchWithinQueryEvent,citingReferencesSearchWithinQueryEvent,tocSearchWithinQueryEvent,searchWithinDocumentEvent}'::text[])) AND (((historyeve0__15.product_view)::text = ANY ('{DefaultProductView,TNPPlus,PLCUS,Indigo,INDIGOCA}'::text[])) OR (historyeve0__15.product_view IS NULL)))
Buffers: shared hit=2
-> Index Scan using history_event_display_timest_prism_guid_display_timestamp_idx32 on hist2.history_event_display_timestamp_20211001 historyeve0__16 (cost=0.14..8.17 rows=1 width=118) (actual time=0.013..0.013 rows=0 loops=1)
Output: historyeve0__16.event_type
Index Cond: (((historyeve0__16.prism_guid)::text = 'i0acc051c00000133f57f4be2bbbdc5ef'::text) AND ((historyeve0__16.product_sid)::text = 'WestlawNext'::text) AND (historyeve0__16.is_deleted = '0'::numeric))
Filter: (((historyeve0__16.event_type)::text <> ALL ('{versionsSearchWithinQueryEvent,notesOfDecisionsSearchWithinQueryEvent,citingReferencesSearchWithinQueryEvent,tocSearchWithinQueryEvent,searchWithinDocumentEvent}'::text[])) AND (((historyeve0__16.product_view)::text = ANY ('{DefaultProductView,TNPPlus,PLCUS,Indigo,INDIGOCA}'::text[])) OR (historyeve0__16.product_view IS NULL)))
Buffers: shared hit=2
-> Index Scan using history_event_display_timest_prism_guid_display_timestamp_idx34 on hist2.history_event_display_timestamp_20211101 historyeve0__17 (cost=0.14..8.17 rows=1 width=118) (actual time=0.015..0.015 rows=0 loops=1)
Output: historyeve0__17.event_type
Index Cond: (((historyeve0__17.prism_guid)::text = 'i0acc051c00000133f57f4be2bbbdc5ef'::text) AND ((historyeve0__17.product_sid)::text = 'WestlawNext'::text) AND (historyeve0__17.is_deleted = '0'::numeric))
Filter: (((historyeve0__17.event_type)::text <> ALL ('{versionsSearchWithinQueryEvent,notesOfDecisionsSearchWithinQueryEvent,citingReferencesSearchWithinQueryEvent,tocSearchWithinQueryEvent,searchWithinDocumentEvent}'::text[])) AND (((historyeve0__17.product_view)::text = ANY ('{DefaultProductView,TNPPlus,PLCUS,Indigo,INDIGOCA}'::text[])) OR (historyeve0__17.product_view IS NULL)))
Buffers: shared hit=2
-> Index Scan using history_event_display_timest_prism_guid_display_timestamp_idx36 on hist2.history_event_display_timestamp_20211201 historyeve0__18 (cost=0.14..8.17 rows=1 width=118) (actual time=0.013..0.013 rows=0 loops=1)
Output: historyeve0__18.event_type
Index Cond: (((historyeve0__18.prism_guid)::text = 'i0acc051c00000133f57f4be2bbbdc5ef'::text) AND ((historyeve0__18.product_sid)::text = 'WestlawNext'::text) AND (historyeve0__18.is_deleted = '0'::numeric))
Filter: (((historyeve0__18.event_type)::text <> ALL ('{versionsSearchWithinQueryEvent,notesOfDecisionsSearchWithinQueryEvent,citingReferencesSearchWithinQueryEvent,tocSearchWithinQueryEvent,searchWithinDocumentEvent}'::text[])) AND (((historyeve0__18.product_view)::text = ANY ('{DefaultProductView,TNPPlus,PLCUS,Indigo,INDIGOCA}'::text[])) OR (historyeve0__18.product_view IS NULL)))
Buffers: shared hit=2
-> Index Scan using history_event_display_timest_prism_guid_display_timestamp_idx38 on hist2.history_event_display_timestamp_20220101 historyeve0__19 (cost=0.14..8.17 rows=1 width=118) (actual time=0.015..0.015 rows=0 loops=1)
Output: historyeve0__19.event_type
Index Cond: (((historyeve0__19.prism_guid)::text = 'i0acc051c00000133f57f4be2bbbdc5ef'::text) AND ((historyeve0__19.product_sid)::text = 'WestlawNext'::text) AND (historyeve0__19.is_deleted = '0'::numeric))
Filter: (((historyeve0__19.event_type)::text <> ALL ('{versionsSearchWithinQueryEvent,notesOfDecisionsSearchWithinQueryEvent,citingReferencesSearchWithinQueryEvent,tocSearchWithinQueryEvent,searchWithinDocumentEvent}'::text[])) AND (((historyeve0__19.product_view)::text = ANY ('{DefaultProductView,TNPPlus,PLCUS,Indigo,INDIGOCA}'::text[])) OR (historyeve0__19.product_view IS NULL)))
Buffers: shared hit=2
-> Index Scan using history_event_display_timest_prism_guid_display_timestamp_idx40 on hist2.history_event_display_timestamp_20220201 historyeve0__20 (cost=0.14..8.17 rows=1 width=118) (actual time=0.015..0.015 rows=0 loops=1)
Output: historyeve0__20.event_type
Index Cond: (((historyeve0__20.prism_guid)::text = 'i0acc051c00000133f57f4be2bbbdc5ef'::text) AND ((historyeve0__20.product_sid)::text = 'WestlawNext'::text) AND (historyeve0__20.is_deleted = '0'::numeric))
Filter: (((historyeve0__20.event_type)::text <> ALL ('{versionsSearchWithinQueryEvent,notesOfDecisionsSearchWithinQueryEvent,citingReferencesSearchWithinQueryEvent,tocSearchWithinQueryEvent,searchWithinDocumentEvent}'::text[])) AND (((historyeve0__20.product_view)::text = ANY ('{DefaultProductView,TNPPlus,PLCUS,Indigo,INDIGOCA}'::text[])) OR (historyeve0__20.product_view IS NULL)))
Buffers: shared hit=2
-> Index Scan using history_event_display_timest_prism_guid_display_timestamp_idx42 on hist2.history_event_display_timestamp_20220301 historyeve0__21 (cost=0.14..8.17 rows=1 width=118) (actual time=0.013..0.014 rows=0 loops=1)
Output: historyeve0__21.event_type
Index Cond: (((historyeve0__21.prism_guid)::text = 'i0acc051c00000133f57f4be2bbbdc5ef'::text) AND ((historyeve0__21.product_sid)::text = 'WestlawNext'::text) AND (historyeve0__21.is_deleted = '0'::numeric))
Filter: (((historyeve0__21.event_type)::text <> ALL ('{versionsSearchWithinQueryEvent,notesOfDecisionsSearchWithinQueryEvent,citingReferencesSearchWithinQueryEvent,tocSearchWithinQueryEvent,searchWithinDocumentEvent}'::text[])) AND (((historyeve0__21.product_view)::text = ANY ('{DefaultProductView,TNPPlus,PLCUS,Indigo,INDIGOCA}'::text[])) OR (historyeve0__21.product_view IS NULL)))
Buffers: shared hit=2
Settings: effective_cache_size = '88283528kB', enable_bitmapscan = 'off', search_path = 'hist2'
Planning Time: 3.774 ms
Execution Time: 647.086 ms
SQL Hash: 901113095, Plan Hash: -1815601721
(147 rows)
Attachments:
[text/plain] QueryWithNoPhyReads.txt (23.0K, ../../DM6PR03MB4332CC5841DDD299A01EF189FAE39@DM6PR03MB4332.namprd03.prod.outlook.com/3-QueryWithNoPhyReads.txt)
download | inline:
explain (analyze, verbose, costs, settings, buffers, timing, summary, hashes)
(/*+ IndexScan(historyeve0_ history_event_idx02) */ select distinct historyeve0_.EVENT_TYPE as junk123
from HISTORY_EVENT historyeve0_
where historyeve0_.PRISM_GUID='i0acc051c00000133f57f4be2bbbdc5ef'
and historyeve0_.IS_DELETED=0
and (historyeve0_.EVENT_TYPE not in ('versionsSearchWithinQueryEvent','notesOfDecisionsSearchWithinQueryEvent','citingReferencesSearchWithinQueryEvent','tocSearchWithinQueryEvent','searchWithinDocumentEvent' ))
and (historyeve0_.PRODUCT_VIEW in ('DefaultProductView','TNPPlus','PLCUS','Indigo','INDIGOCA' )
or historyeve0_.PRODUCT_VIEW is null)
and historyeve0_.PRODUCT_SID='WestlawNext'
order by historyeve0_.EVENT_TYPE asc);
Sort (cost=12350.30..12350.80 rows=200 width=13) (actual time=11.686..11.693 rows=6 loops=1)
Output: historyeve0_.event_type
Sort Key: historyeve0_.event_type
Sort Method: quicksort Memory: 25kB
Buffers: shared hit=581
-> HashAggregate (cost=12340.65..12342.65 rows=200 width=13) (actual time=11.676..11.683 rows=6 loops=1)
Output: historyeve0_.event_type
Group Key: historyeve0_.event_type
Buffers: shared hit=581
-> Append (cost=0.55..12334.42 rows=2492 width=13) (actual time=0.035..10.759 rows=5566 loops=1)
Buffers: shared hit=581
-> Index Scan using history_event_display_timesta_prism_guid_display_timestamp__idx on hist2.history_event_display_timestamp_20200601 historyeve0_ (cost=0.55..1758.76 rows=362 width=13) (actual time=0.035..1.857 rows=1020 loops=1)
Output: historyeve0_.event_type
Index Cond: (((historyeve0_.prism_guid)::text = 'i0acc051c00000133f57f4be2bbbdc5ef'::text) AND ((historyeve0_.product_sid)::text = 'WestlawNext'::text) AND (historyeve0_.is_deleted = '0'::numeric))
Filter: (((historyeve0_.event_type)::text <> ALL ('{versionsSearchWithinQueryEvent,notesOfDecisionsSearchWithinQueryEvent,citingReferencesSearchWithinQueryEvent,tocSearchWithinQueryEvent,searchWithinDocumentEvent}'::text[])) AND (((historyeve0_.product_view)::text = ANY ('{DefaultProductView,TNPPlus,PLCUS,Indigo,INDIGOCA}'::text[])) OR (historyeve0_.product_view IS NULL)))
Rows Removed by Filter: 5
Buffers: shared hit=85
-> Index Scan using history_event_display_timesta_prism_guid_display_timestamp_idx1 on hist2.history_event_display_timestamp_20200701 historyeve0__1 (cost=0.55..1351.59 rows=276 width=13) (actual time=0.029..1.435 rows=800 loops=1)
Output: historyeve0__1.event_type
Index Cond: (((historyeve0__1.prism_guid)::text = 'i0acc051c00000133f57f4be2bbbdc5ef'::text) AND ((historyeve0__1.product_sid)::text = 'WestlawNext'::text) AND (historyeve0__1.is_deleted = '0'::numeric))
Filter: (((historyeve0__1.event_type)::text <> ALL ('{versionsSearchWithinQueryEvent,notesOfDecisionsSearchWithinQueryEvent,citingReferencesSearchWithinQueryEvent,tocSearchWithinQueryEvent,searchWithinDocumentEvent}'::text[])) AND (((historyeve0__1.product_view)::text = ANY ('{DefaultProductView,TNPPlus,PLCUS,Indigo,INDIGOCA}'::text[])) OR (historyeve0__1.product_view IS NULL)))
Rows Removed by Filter: 4
Buffers: shared hit=70
-> Index Scan using history_event_display_timesta_prism_guid_display_timestamp_idx2 on hist2.history_event_display_timestamp_20200801 historyeve0__2 (cost=0.55..468.34 rows=97 width=13) (actual time=0.031..0.331 rows=173 loops=1)
Output: historyeve0__2.event_type
Index Cond: (((historyeve0__2.prism_guid)::text = 'i0acc051c00000133f57f4be2bbbdc5ef'::text) AND ((historyeve0__2.product_sid)::text = 'WestlawNext'::text) AND (historyeve0__2.is_deleted = '0'::numeric))
Filter: (((historyeve0__2.event_type)::text <> ALL ('{versionsSearchWithinQueryEvent,notesOfDecisionsSearchWithinQueryEvent,citingReferencesSearchWithinQueryEvent,tocSearchWithinQueryEvent,searchWithinDocumentEvent}'::text[])) AND (((historyeve0__2.product_view)::text = ANY ('{DefaultProductView,TNPPlus,PLCUS,Indigo,INDIGOCA}'::text[])) OR (historyeve0__2.product_view IS NULL)))
Buffers: shared hit=26
-> Index Scan using history_event_display_timesta_prism_guid_display_timestamp_idx3 on hist2.history_event_display_timestamp_20200901 historyeve0__3 (cost=0.14..8.17 rows=1 width=118) (actual time=0.010..0.010 rows=0 loops=1)
Output: historyeve0__3.event_type
Index Cond: (((historyeve0__3.prism_guid)::text = 'i0acc051c00000133f57f4be2bbbdc5ef'::text) AND ((historyeve0__3.product_sid)::text = 'WestlawNext'::text) AND (historyeve0__3.is_deleted = '0'::numeric))
Filter: (((historyeve0__3.event_type)::text <> ALL ('{versionsSearchWithinQueryEvent,notesOfDecisionsSearchWithinQueryEvent,citingReferencesSearchWithinQueryEvent,tocSearchWithinQueryEvent,searchWithinDocumentEvent}'::text[])) AND (((historyeve0__3.product_view)::text = ANY ('{DefaultProductView,TNPPlus,PLCUS,Indigo,INDIGOCA}'::text[])) OR (historyeve0__3.product_view IS NULL)))
Buffers: shared hit=2
-> Index Scan using history_event_display_timesta_prism_guid_display_timestamp_idx4 on hist2.history_event_display_timestamp_20201001 historyeve0__4 (cost=0.14..8.17 rows=1 width=118) (actual time=0.014..0.014 rows=0 loops=1)
Output: historyeve0__4.event_type
Index Cond: (((historyeve0__4.prism_guid)::text = 'i0acc051c00000133f57f4be2bbbdc5ef'::text) AND ((historyeve0__4.product_sid)::text = 'WestlawNext'::text) AND (historyeve0__4.is_deleted = '0'::numeric))
Filter: (((historyeve0__4.event_type)::text <> ALL ('{versionsSearchWithinQueryEvent,notesOfDecisionsSearchWithinQueryEvent,citingReferencesSearchWithinQueryEvent,tocSearchWithinQueryEvent,searchWithinDocumentEvent}'::text[])) AND (((historyeve0__4.product_view)::text = ANY ('{DefaultProductView,TNPPlus,PLCUS,Indigo,INDIGOCA}'::text[])) OR (historyeve0__4.product_view IS NULL)))
Buffers: shared hit=2
-> Index Scan using history_event_display_timesta_prism_guid_display_timestamp_idx5 on hist2.history_event_display_timestamp_20201101 historyeve0__5 (cost=0.14..8.17 rows=1 width=118) (actual time=0.008..0.008 rows=0 loops=1)
Output: historyeve0__5.event_type
Index Cond: (((historyeve0__5.prism_guid)::text = 'i0acc051c00000133f57f4be2bbbdc5ef'::text) AND ((historyeve0__5.product_sid)::text = 'WestlawNext'::text) AND (historyeve0__5.is_deleted = '0'::numeric))
Filter: (((historyeve0__5.event_type)::text <> ALL ('{versionsSearchWithinQueryEvent,notesOfDecisionsSearchWithinQueryEvent,citingReferencesSearchWithinQueryEvent,tocSearchWithinQueryEvent,searchWithinDocumentEvent}'::text[])) AND (((historyeve0__5.product_view)::text = ANY ('{DefaultProductView,TNPPlus,PLCUS,Indigo,INDIGOCA}'::text[])) OR (historyeve0__5.product_view IS NULL)))
Buffers: shared hit=2
-> Index Scan using history_event_display_timesta_prism_guid_display_timestamp_idx6 on hist2.history_event_display_timestamp_20201201 historyeve0__6 (cost=0.55..766.81 rows=155 width=13) (actual time=0.030..0.600 rows=329 loops=1)
Output: historyeve0__6.event_type
Index Cond: (((historyeve0__6.prism_guid)::text = 'i0acc051c00000133f57f4be2bbbdc5ef'::text) AND ((historyeve0__6.product_sid)::text = 'WestlawNext'::text) AND (historyeve0__6.is_deleted = '0'::numeric))
Filter: (((historyeve0__6.event_type)::text <> ALL ('{versionsSearchWithinQueryEvent,notesOfDecisionsSearchWithinQueryEvent,citingReferencesSearchWithinQueryEvent,tocSearchWithinQueryEvent,searchWithinDocumentEvent}'::text[])) AND (((historyeve0__6.product_view)::text = ANY ('{DefaultProductView,TNPPlus,PLCUS,Indigo,INDIGOCA}'::text[])) OR (historyeve0__6.product_view IS NULL)))
Rows Removed by Filter: 1
Buffers: shared hit=36
-> Index Scan using history_event_display_timesta_prism_guid_display_timestamp_idx7 on hist2.history_event_display_timestamp_20210101 historyeve0__7 (cost=0.55..371.57 rows=76 width=13) (actual time=0.028..0.271 rows=138 loops=1)
Output: historyeve0__7.event_type
Index Cond: (((historyeve0__7.prism_guid)::text = 'i0acc051c00000133f57f4be2bbbdc5ef'::text) AND ((historyeve0__7.product_sid)::text = 'WestlawNext'::text) AND (historyeve0__7.is_deleted = '0'::numeric))
Filter: (((historyeve0__7.event_type)::text <> ALL ('{versionsSearchWithinQueryEvent,notesOfDecisionsSearchWithinQueryEvent,citingReferencesSearchWithinQueryEvent,tocSearchWithinQueryEvent,searchWithinDocumentEvent}'::text[])) AND (((historyeve0__7.product_view)::text = ANY ('{DefaultProductView,TNPPlus,PLCUS,Indigo,INDIGOCA}'::text[])) OR (historyeve0__7.product_view IS NULL)))
Buffers: shared hit=23
-> Index Scan using history_event_display_timesta_prism_guid_display_timestamp_idx8 on hist2.history_event_display_timestamp_20210201 historyeve0__8 (cost=0.55..1960.35 rows=403 width=13) (actual time=0.029..1.281 rows=716 loops=1)
Output: historyeve0__8.event_type
Index Cond: (((historyeve0__8.prism_guid)::text = 'i0acc051c00000133f57f4be2bbbdc5ef'::text) AND ((historyeve0__8.product_sid)::text = 'WestlawNext'::text) AND (historyeve0__8.is_deleted = '0'::numeric))
Filter: (((historyeve0__8.event_type)::text <> ALL ('{versionsSearchWithinQueryEvent,notesOfDecisionsSearchWithinQueryEvent,citingReferencesSearchWithinQueryEvent,tocSearchWithinQueryEvent,searchWithinDocumentEvent}'::text[])) AND (((historyeve0__8.product_view)::text = ANY ('{DefaultProductView,TNPPlus,PLCUS,Indigo,INDIGOCA}'::text[])) OR (historyeve0__8.product_view IS NULL)))
Rows Removed by Filter: 1
Buffers: shared hit=66
-> Index Scan using history_event_display_timesta_prism_guid_display_timestamp_idx9 on hist2.history_event_display_timestamp_20210301 historyeve0__9 (cost=0.55..1371.73 rows=279 width=13) (actual time=0.029..0.877 rows=488 loops=1)
Output: historyeve0__9.event_type
Index Cond: (((historyeve0__9.prism_guid)::text = 'i0acc051c00000133f57f4be2bbbdc5ef'::text) AND ((historyeve0__9.product_sid)::text = 'WestlawNext'::text) AND (historyeve0__9.is_deleted = '0'::numeric))
Filter: (((historyeve0__9.event_type)::text <> ALL ('{versionsSearchWithinQueryEvent,notesOfDecisionsSearchWithinQueryEvent,citingReferencesSearchWithinQueryEvent,tocSearchWithinQueryEvent,searchWithinDocumentEvent}'::text[])) AND (((historyeve0__9.product_view)::text = ANY ('{DefaultProductView,TNPPlus,PLCUS,Indigo,INDIGOCA}'::text[])) OR (historyeve0__9.product_view IS NULL)))
Rows Removed by Filter: 3
Buffers: shared hit=50
-> Index Scan using history_event_display_timest_prism_guid_display_timestamp_idx10 on hist2.history_event_display_timestamp_20210401 historyeve0__10 (cost=0.55..1625.74 rows=325 width=13) (actual time=0.032..1.248 rows=701 loops=1)
Output: historyeve0__10.event_type
Index Cond: (((historyeve0__10.prism_guid)::text = 'i0acc051c00000133f57f4be2bbbdc5ef'::text) AND ((historyeve0__10.product_sid)::text = 'WestlawNext'::text) AND (historyeve0__10.is_deleted = '0'::numeric))
Filter: (((historyeve0__10.event_type)::text <> ALL ('{versionsSearchWithinQueryEvent,notesOfDecisionsSearchWithinQueryEvent,citingReferencesSearchWithinQueryEvent,tocSearchWithinQueryEvent,searchWithinDocumentEvent}'::text[])) AND (((historyeve0__10.product_view)::text = ANY ('{DefaultProductView,TNPPlus,PLCUS,Indigo,INDIGOCA}'::text[])) OR (historyeve0__10.product_view IS NULL)))
Rows Removed by Filter: 2
Buffers: shared hit=64
-> Index Scan using history_event_display_timest_prism_guid_display_timestamp_idx11 on hist2.history_event_display_timestamp_20210501 historyeve0__11 (cost=0.55..1738.67 rows=351 width=13) (actual time=0.029..1.175 rows=646 loops=1)
Output: historyeve0__11.event_type
Index Cond: (((historyeve0__11.prism_guid)::text = 'i0acc051c00000133f57f4be2bbbdc5ef'::text) AND ((historyeve0__11.product_sid)::text = 'WestlawNext'::text) AND (historyeve0__11.is_deleted = '0'::numeric))
Filter: (((historyeve0__11.event_type)::text <> ALL ('{versionsSearchWithinQueryEvent,notesOfDecisionsSearchWithinQueryEvent,citingReferencesSearchWithinQueryEvent,tocSearchWithinQueryEvent,searchWithinDocumentEvent}'::text[])) AND (((historyeve0__11.product_view)::text = ANY ('{DefaultProductView,TNPPlus,PLCUS,Indigo,INDIGOCA}'::text[])) OR (historyeve0__11.product_view IS NULL)))
Rows Removed by Filter: 4
Buffers: shared hit=64
-> Index Scan using history_event_display_timest_prism_guid_display_timestamp_idx12 on hist2.history_event_display_timestamp_20210601 historyeve0__12 (cost=0.55..794.55 rows=152 width=13) (actual time=0.032..0.784 rows=429 loops=1)
Output: historyeve0__12.event_type
Index Cond: (((historyeve0__12.prism_guid)::text = 'i0acc051c00000133f57f4be2bbbdc5ef'::text) AND ((historyeve0__12.product_sid)::text = 'WestlawNext'::text) AND (historyeve0__12.is_deleted = '0'::numeric))
Filter: (((historyeve0__12.event_type)::text <> ALL ('{versionsSearchWithinQueryEvent,notesOfDecisionsSearchWithinQueryEvent,citingReferencesSearchWithinQueryEvent,tocSearchWithinQueryEvent,searchWithinDocumentEvent}'::text[])) AND (((historyeve0__12.product_view)::text = ANY ('{DefaultProductView,TNPPlus,PLCUS,Indigo,INDIGOCA}'::text[])) OR (historyeve0__12.product_view IS NULL)))
Rows Removed by Filter: 3
Buffers: shared hit=51
-> Index Scan using history_event_display_timest_prism_guid_display_timestamp_idx13 on hist2.history_event_display_timestamp_20210701 historyeve0__13 (cost=0.55..23.56 rows=5 width=12) (actual time=0.028..0.245 rows=126loops=1)
Output: historyeve0__13.event_type
Index Cond: (((historyeve0__13.prism_guid)::text = 'i0acc051c00000133f57f4be2bbbdc5ef'::text) AND ((historyeve0__13.product_sid)::text = 'WestlawNext'::text) AND (historyeve0__13.is_deleted = '0'::numeric))
Filter: (((historyeve0__13.event_type)::text <> ALL ('{versionsSearchWithinQueryEvent,notesOfDecisionsSearchWithinQueryEvent,citingReferencesSearchWithinQueryEvent,tocSearchWithinQueryEvent,searchWithinDocumentEvent}'::text[])) AND (((historyeve0__13.product_view)::text = ANY ('{DefaultProductView,TNPPlus,PLCUS,Indigo,INDIGOCA}'::text[])) OR (historyeve0__13.product_view IS NULL)))
Buffers: shared hit=22
-> Index Scan using history_event_display_timest_prism_guid_display_timestamp_idx14 on hist2.history_event_display_timestamp_20210801 historyeve0__14 (cost=0.55..8.58 rows=1 width=12) (actual time=0.013..0.014 rows=0 loops=1)
Output: historyeve0__14.event_type
Index Cond: (((historyeve0__14.prism_guid)::text = 'i0acc051c00000133f57f4be2bbbdc5ef'::text) AND ((historyeve0__14.product_sid)::text = 'WestlawNext'::text) AND (historyeve0__14.is_deleted = '0'::numeric))
Filter: (((historyeve0__14.event_type)::text <> ALL ('{versionsSearchWithinQueryEvent,notesOfDecisionsSearchWithinQueryEvent,citingReferencesSearchWithinQueryEvent,tocSearchWithinQueryEvent,searchWithinDocumentEvent}'::text[])) AND (((historyeve0__14.product_view)::text = ANY ('{DefaultProductView,TNPPlus,PLCUS,Indigo,INDIGOCA}'::text[])) OR (historyeve0__14.product_view IS NULL)))
Buffers: shared hit=4
-> Index Scan using history_event_display_timest_prism_guid_display_timestamp_idx15 on hist2.history_event_display_timestamp_20210901 historyeve0__15 (cost=0.14..8.17 rows=1 width=118) (actual time=0.011..0.011 rows=0 loops=1)
Output: historyeve0__15.event_type
Index Cond: (((historyeve0__15.prism_guid)::text = 'i0acc051c00000133f57f4be2bbbdc5ef'::text) AND ((historyeve0__15.product_sid)::text = 'WestlawNext'::text) AND (historyeve0__15.is_deleted = '0'::numeric))
Filter: (((historyeve0__15.event_type)::text <> ALL ('{versionsSearchWithinQueryEvent,notesOfDecisionsSearchWithinQueryEvent,citingReferencesSearchWithinQueryEvent,tocSearchWithinQueryEvent,searchWithinDocumentEvent}'::text[])) AND (((historyeve0__15.product_view)::text = ANY ('{DefaultProductView,TNPPlus,PLCUS,Indigo,INDIGOCA}'::text[])) OR (historyeve0__15.product_view IS NULL)))
Buffers: shared hit=2
-> Index Scan using history_event_display_timest_prism_guid_display_timestamp_idx32 on hist2.history_event_display_timestamp_20211001 historyeve0__16 (cost=0.14..8.17 rows=1 width=118) (actual time=0.011..0.011 rows=0 loops=1)
Output: historyeve0__16.event_type
Index Cond: (((historyeve0__16.prism_guid)::text = 'i0acc051c00000133f57f4be2bbbdc5ef'::text) AND ((historyeve0__16.product_sid)::text = 'WestlawNext'::text) AND (historyeve0__16.is_deleted = '0'::numeric))
Filter: (((historyeve0__16.event_type)::text <> ALL ('{versionsSearchWithinQueryEvent,notesOfDecisionsSearchWithinQueryEvent,citingReferencesSearchWithinQueryEvent,tocSearchWithinQueryEvent,searchWithinDocumentEvent}'::text[])) AND (((historyeve0__16.product_view)::text = ANY ('{DefaultProductView,TNPPlus,PLCUS,Indigo,INDIGOCA}'::text[])) OR (historyeve0__16.product_view IS NULL)))
Buffers: shared hit=2
-> Index Scan using history_event_display_timest_prism_guid_display_timestamp_idx34 on hist2.history_event_display_timestamp_20211101 historyeve0__17 (cost=0.14..8.17 rows=1 width=118) (actual time=0.011..0.011 rows=0 loops=1)
Output: historyeve0__17.event_type
Index Cond: (((historyeve0__17.prism_guid)::text = 'i0acc051c00000133f57f4be2bbbdc5ef'::text) AND ((historyeve0__17.product_sid)::text = 'WestlawNext'::text) AND (historyeve0__17.is_deleted = '0'::numeric))
Filter: (((historyeve0__17.event_type)::text <> ALL ('{versionsSearchWithinQueryEvent,notesOfDecisionsSearchWithinQueryEvent,citingReferencesSearchWithinQueryEvent,tocSearchWithinQueryEvent,searchWithinDocumentEvent}'::text[])) AND (((historyeve0__17.product_view)::text = ANY ('{DefaultProductView,TNPPlus,PLCUS,Indigo,INDIGOCA}'::text[])) OR (historyeve0__17.product_view IS NULL)))
Buffers: shared hit=2
-> Index Scan using history_event_display_timest_prism_guid_display_timestamp_idx36 on hist2.history_event_display_timestamp_20211201 historyeve0__18 (cost=0.14..8.17 rows=1 width=118) (actual time=0.013..0.013 rows=0 loops=1)
Output: historyeve0__18.event_type
Index Cond: (((historyeve0__18.prism_guid)::text = 'i0acc051c00000133f57f4be2bbbdc5ef'::text) AND ((historyeve0__18.product_sid)::text = 'WestlawNext'::text) AND (historyeve0__18.is_deleted = '0'::numeric))
Filter: (((historyeve0__18.event_type)::text <> ALL ('{versionsSearchWithinQueryEvent,notesOfDecisionsSearchWithinQueryEvent,citingReferencesSearchWithinQueryEvent,tocSearchWithinQueryEvent,searchWithinDocumentEvent}'::text[])) AND (((historyeve0__18.product_view)::text = ANY ('{DefaultProductView,TNPPlus,PLCUS,Indigo,INDIGOCA}'::text[])) OR (historyeve0__18.product_view IS NULL)))
Buffers: shared hit=2
-> Index Scan using history_event_display_timest_prism_guid_display_timestamp_idx38 on hist2.history_event_display_timestamp_20220101 historyeve0__19 (cost=0.14..8.17 rows=1 width=118) (actual time=0.013..0.013 rows=0 loops=1)
Output: historyeve0__19.event_type
Index Cond: (((historyeve0__19.prism_guid)::text = 'i0acc051c00000133f57f4be2bbbdc5ef'::text) AND ((historyeve0__19.product_sid)::text = 'WestlawNext'::text) AND (historyeve0__19.is_deleted = '0'::numeric))
Filter: (((historyeve0__19.event_type)::text <> ALL ('{versionsSearchWithinQueryEvent,notesOfDecisionsSearchWithinQueryEvent,citingReferencesSearchWithinQueryEvent,tocSearchWithinQueryEvent,searchWithinDocumentEvent}'::text[])) AND (((historyeve0__19.product_view)::text = ANY ('{DefaultProductView,TNPPlus,PLCUS,Indigo,INDIGOCA}'::text[])) OR (historyeve0__19.product_view IS NULL)))
Buffers: shared hit=2
-> Index Scan using history_event_display_timest_prism_guid_display_timestamp_idx40 on hist2.history_event_display_timestamp_20220201 historyeve0__20 (cost=0.14..8.17 rows=1 width=118) (actual time=0.013..0.013 rows=0 loops=1)
Output: historyeve0__20.event_type
Index Cond: (((historyeve0__20.prism_guid)::text = 'i0acc051c00000133f57f4be2bbbdc5ef'::text) AND ((historyeve0__20.product_sid)::text = 'WestlawNext'::text) AND (historyeve0__20.is_deleted = '0'::numeric))
Filter: (((historyeve0__20.event_type)::text <> ALL ('{versionsSearchWithinQueryEvent,notesOfDecisionsSearchWithinQueryEvent,citingReferencesSearchWithinQueryEvent,tocSearchWithinQueryEvent,searchWithinDocumentEvent}'::text[])) AND (((historyeve0__20.product_view)::text = ANY ('{DefaultProductView,TNPPlus,PLCUS,Indigo,INDIGOCA}'::text[])) OR (historyeve0__20.product_view IS NULL)))
Buffers: shared hit=2
-> Index Scan using history_event_display_timest_prism_guid_display_timestamp_idx42 on hist2.history_event_display_timestamp_20220301 historyeve0__21 (cost=0.14..8.17 rows=1 width=118) (actual time=0.010..0.010 rows=0 loops=1)
Output: historyeve0__21.event_type
Index Cond: (((historyeve0__21.prism_guid)::text = 'i0acc051c00000133f57f4be2bbbdc5ef'::text) AND ((historyeve0__21.product_sid)::text = 'WestlawNext'::text) AND (historyeve0__21.is_deleted = '0'::numeric))
Filter: (((historyeve0__21.event_type)::text <> ALL ('{versionsSearchWithinQueryEvent,notesOfDecisionsSearchWithinQueryEvent,citingReferencesSearchWithinQueryEvent,tocSearchWithinQueryEvent,searchWithinDocumentEvent}'::text[])) AND (((historyeve0__21.product_view)::text = ANY ('{DefaultProductView,TNPPlus,PLCUS,Indigo,INDIGOCA}'::text[])) OR (historyeve0__21.product_view IS NULL)))
Buffers: shared hit=2
Settings: effective_cache_size = '88283528kB', enable_bitmapscan = 'off', search_path = 'hist2'
Planning Time: 3.762 ms
Execution Time: 11.863 ms
SQL Hash: 901113095, Plan Hash: -1815601721
(133 rows)
[text/plain] QueryWithPhyReads.txt (23.7K, ../../DM6PR03MB4332CC5841DDD299A01EF189FAE39@DM6PR03MB4332.namprd03.prod.outlook.com/4-QueryWithPhyReads.txt)
download | inline:
explain (analyze, verbose, costs, settings, buffers, timing, summary, hashes)
(/*+ IndexScan(historyeve0_ history_event_idx02) */ select distinct historyeve0_.EVENT_TYPE as junk123
from HISTORY_EVENT historyeve0_
where historyeve0_.PRISM_GUID='i0acc051c00000133f57f4be2bbbdc5ef'
and historyeve0_.IS_DELETED=0
and (historyeve0_.EVENT_TYPE not in ('versionsSearchWithinQueryEvent','notesOfDecisionsSearchWithinQueryEvent','citingReferencesSearchWithinQueryEvent','tocSearchWithinQueryEvent','searchWithinDocumentEvent' ))
and (historyeve0_.PRODUCT_VIEW in ('DefaultProductView','TNPPlus','PLCUS','Indigo','INDIGOCA' )
or historyeve0_.PRODUCT_VIEW is null)
and historyeve0_.PRODUCT_SID='WestlawNext'
order by historyeve0_.EVENT_TYPE asc);
Sort (cost=12350.30..12350.80 rows=200 width=13) (actual time=646.880..646.889 rows=6 loops=1)
Output: historyeve0_.event_type
Sort Key: historyeve0_.event_type
Sort Method: quicksort Memory: 25kB
Buffers: shared hit=171 read=880
I/O Timings: read=333.505
-> HashAggregate (cost=12340.65..12342.65 rows=200 width=13) (actual time=646.868..646.878 rows=6 loops=1)
Output: historyeve0_.event_type
Group Key: historyeve0_.event_type
Buffers: shared hit=171 read=880
I/O Timings: read=333.505
-> Append (cost=0.55..12334.42 rows=2492 width=13) (actual time=1.915..645.743 rows=5566 loops=1)
Buffers: shared hit=171 read=880
I/O Timings: read=333.505
-> Index Scan using history_event_display_timesta_prism_guid_display_timestamp__idx on hist2.history_event_display_timestamp_20200601 historyeve0_ (cost=0.55..1758.76 rows=362 width=13) (actual time=1.914..122.776 rows=1020 loops=1)
Output: historyeve0_.event_type
Index Cond: (((historyeve0_.prism_guid)::text = 'i0acc051c00000133f57f4be2bbbdc5ef'::text) AND ((historyeve0_.product_sid)::text = 'WestlawNext'::text) AND (historyeve0_.is_deleted = '0'::numeric))
Filter: (((historyeve0_.event_type)::text <> ALL ('{versionsSearchWithinQueryEvent,notesOfDecisionsSearchWithinQueryEvent,citingReferencesSearchWithinQueryEvent,tocSearchWithinQueryEvent,searchWithinDocumentEvent}'::text[])) AND (((historyeve0_.product_view)::text = ANY ('{DefaultProductView,TNPPlus,PLCUS,Indigo,INDIGOCA}'::text[])) OR (historyeve0_.product_view IS NULL)))
Rows Removed by Filter: 5
Buffers: shared hit=15 read=125
I/O Timings: read=76.762
-> Index Scan using history_event_display_timesta_prism_guid_display_timestamp_idx1 on hist2.history_event_display_timestamp_20200701 historyeve0__1 (cost=0.55..1351.59 rows=276 width=13) (actual time=1.869..87.808 rows=800 loops=1)
Output: historyeve0__1.event_type
Index Cond: (((historyeve0__1.prism_guid)::text = 'i0acc051c00000133f57f4be2bbbdc5ef'::text) AND ((historyeve0__1.product_sid)::text = 'WestlawNext'::text) AND (historyeve0__1.is_deleted = '0'::numeric))
Filter: (((historyeve0__1.event_type)::text <> ALL ('{versionsSearchWithinQueryEvent,notesOfDecisionsSearchWithinQueryEvent,citingReferencesSearchWithinQueryEvent,tocSearchWithinQueryEvent,searchWithinDocumentEvent}'::text[])) AND (((historyeve0__1.product_view)::text = ANY ('{DefaultProductView,TNPPlus,PLCUS,Indigo,INDIGOCA}'::text[])) OR (historyeve0__1.product_view IS NULL)))
Rows Removed by Filter: 4
Buffers: shared hit=7 read=85
I/O Timings: read=60.467
-> Index Scan using history_event_display_timesta_prism_guid_display_timestamp_idx2 on hist2.history_event_display_timestamp_20200801 historyeve0__2 (cost=0.55..468.34 rows=97 width=13) (actual time=1.479..12.984 rows=173 loops=1)
Output: historyeve0__2.event_type
Index Cond: (((historyeve0__2.prism_guid)::text = 'i0acc051c00000133f57f4be2bbbdc5ef'::text) AND ((historyeve0__2.product_sid)::text = 'WestlawNext'::text) AND (historyeve0__2.is_deleted = '0'::numeric))
Filter: (((historyeve0__2.event_type)::text <> ALL ('{versionsSearchWithinQueryEvent,notesOfDecisionsSearchWithinQueryEvent,citingReferencesSearchWithinQueryEvent,tocSearchWithinQueryEvent,searchWithinDocumentEvent}'::text[])) AND (((historyeve0__2.product_view)::text = ANY ('{DefaultProductView,TNPPlus,PLCUS,Indigo,INDIGOCA}'::text[])) OR (historyeve0__2.product_view IS NULL)))
Buffers: shared hit=15 read=13
I/O Timings: read=8.322
-> Index Scan using history_event_display_timesta_prism_guid_display_timestamp_idx3 on hist2.history_event_display_timestamp_20200901 historyeve0__3 (cost=0.14..8.17 rows=1 width=118) (actual time=0.012..0.012 rows=0 loops=1)
Output: historyeve0__3.event_type
Index Cond: (((historyeve0__3.prism_guid)::text = 'i0acc051c00000133f57f4be2bbbdc5ef'::text) AND ((historyeve0__3.product_sid)::text = 'WestlawNext'::text) AND (historyeve0__3.is_deleted = '0'::numeric))
Filter: (((historyeve0__3.event_type)::text <> ALL ('{versionsSearchWithinQueryEvent,notesOfDecisionsSearchWithinQueryEvent,citingReferencesSearchWithinQueryEvent,tocSearchWithinQueryEvent,searchWithinDocumentEvent}'::text[])) AND (((historyeve0__3.product_view)::text = ANY ('{DefaultProductView,TNPPlus,PLCUS,Indigo,INDIGOCA}'::text[])) OR (historyeve0__3.product_view IS NULL)))
Buffers: shared hit=2
-> Index Scan using history_event_display_timesta_prism_guid_display_timestamp_idx4 on hist2.history_event_display_timestamp_20201001 historyeve0__4 (cost=0.14..8.17 rows=1 width=118) (actual time=0.014..0.014 rows=0 loops=1)
Output: historyeve0__4.event_type
Index Cond: (((historyeve0__4.prism_guid)::text = 'i0acc051c00000133f57f4be2bbbdc5ef'::text) AND ((historyeve0__4.product_sid)::text = 'WestlawNext'::text) AND (historyeve0__4.is_deleted = '0'::numeric))
Filter: (((historyeve0__4.event_type)::text <> ALL ('{versionsSearchWithinQueryEvent,notesOfDecisionsSearchWithinQueryEvent,citingReferencesSearchWithinQueryEvent,tocSearchWithinQueryEvent,searchWithinDocumentEvent}'::text[])) AND (((historyeve0__4.product_view)::text = ANY ('{DefaultProductView,TNPPlus,PLCUS,Indigo,INDIGOCA}'::text[])) OR (historyeve0__4.product_view IS NULL)))
Buffers: shared hit=2
-> Index Scan using history_event_display_timesta_prism_guid_display_timestamp_idx5 on hist2.history_event_display_timestamp_20201101 historyeve0__5 (cost=0.14..8.17 rows=1 width=118) (actual time=0.011..0.011 rows=0 loops=1)
Output: historyeve0__5.event_type
Index Cond: (((historyeve0__5.prism_guid)::text = 'i0acc051c00000133f57f4be2bbbdc5ef'::text) AND ((historyeve0__5.product_sid)::text = 'WestlawNext'::text) AND (historyeve0__5.is_deleted = '0'::numeric))
Filter: (((historyeve0__5.event_type)::text <> ALL ('{versionsSearchWithinQueryEvent,notesOfDecisionsSearchWithinQueryEvent,citingReferencesSearchWithinQueryEvent,tocSearchWithinQueryEvent,searchWithinDocumentEvent}'::text[])) AND (((historyeve0__5.product_view)::text = ANY ('{DefaultProductView,TNPPlus,PLCUS,Indigo,INDIGOCA}'::text[])) OR (historyeve0__5.product_view IS NULL)))
Buffers: shared hit=2
-> Index Scan using history_event_display_timesta_prism_guid_display_timestamp_idx6 on hist2.history_event_display_timestamp_20201201 historyeve0__6 (cost=0.55..766.81 rows=155 width=13) (actual time=1.172..31.168 rows=329 loops=1)
Output: historyeve0__6.event_type
Index Cond: (((historyeve0__6.prism_guid)::text = 'i0acc051c00000133f57f4be2bbbdc5ef'::text) AND ((historyeve0__6.product_sid)::text = 'WestlawNext'::text) AND (historyeve0__6.is_deleted = '0'::numeric))
Filter: (((historyeve0__6.event_type)::text <> ALL ('{versionsSearchWithinQueryEvent,notesOfDecisionsSearchWithinQueryEvent,citingReferencesSearchWithinQueryEvent,tocSearchWithinQueryEvent,searchWithinDocumentEvent}'::text[])) AND (((historyeve0__6.product_view)::text = ANY ('{DefaultProductView,TNPPlus,PLCUS,Indigo,INDIGOCA}'::text[])) OR (historyeve0__6.product_view IS NULL)))
Rows Removed by Filter: 1
Buffers: shared hit=11 read=34
I/O Timings: read=20.077
-> Index Scan using history_event_display_timesta_prism_guid_display_timestamp_idx7 on hist2.history_event_display_timestamp_20210101 historyeve0__7 (cost=0.55..371.57 rows=76 width=13) (actual time=1.467..15.204 rows=138 loops=1)
Output: historyeve0__7.event_type
Index Cond: (((historyeve0__7.prism_guid)::text = 'i0acc051c00000133f57f4be2bbbdc5ef'::text) AND ((historyeve0__7.product_sid)::text = 'WestlawNext'::text) AND (historyeve0__7.is_deleted = '0'::numeric))
Filter: (((historyeve0__7.event_type)::text <> ALL ('{versionsSearchWithinQueryEvent,notesOfDecisionsSearchWithinQueryEvent,citingReferencesSearchWithinQueryEvent,tocSearchWithinQueryEvent,searchWithinDocumentEvent}'::text[])) AND (((historyeve0__7.product_view)::text = ANY ('{DefaultProductView,TNPPlus,PLCUS,Indigo,INDIGOCA}'::text[])) OR (historyeve0__7.product_view IS NULL)))
Buffers: shared hit=12 read=17
I/O Timings: read=8.436
-> Index Scan using history_event_display_timesta_prism_guid_display_timestamp_idx8 on hist2.history_event_display_timestamp_20210201 historyeve0__8 (cost=0.55..1960.35 rows=403 width=13) (actual time=2.678..87.188 rows=716 loops=1)
Output: historyeve0__8.event_type
Index Cond: (((historyeve0__8.prism_guid)::text = 'i0acc051c00000133f57f4be2bbbdc5ef'::text) AND ((historyeve0__8.product_sid)::text = 'WestlawNext'::text) AND (historyeve0__8.is_deleted = '0'::numeric))
Filter: (((historyeve0__8.event_type)::text <> ALL ('{versionsSearchWithinQueryEvent,notesOfDecisionsSearchWithinQueryEvent,citingReferencesSearchWithinQueryEvent,tocSearchWithinQueryEvent,searchWithinDocumentEvent}'::text[])) AND (((historyeve0__8.product_view)::text = ANY ('{DefaultProductView,TNPPlus,PLCUS,Indigo,INDIGOCA}'::text[])) OR (historyeve0__8.product_view IS NULL)))
Rows Removed by Filter: 1
Buffers: shared hit=12 read=113
I/O Timings: read=51.819
-> Index Scan using history_event_display_timesta_prism_guid_display_timestamp_idx9 on hist2.history_event_display_timestamp_20210301 historyeve0__9 (cost=0.55..1371.73 rows=279 width=13) (actual time=1.837..68.129 rows=488 loops=1)
Output: historyeve0__9.event_type
Index Cond: (((historyeve0__9.prism_guid)::text = 'i0acc051c00000133f57f4be2bbbdc5ef'::text) AND ((historyeve0__9.product_sid)::text = 'WestlawNext'::text) AND (historyeve0__9.is_deleted = '0'::numeric))
Filter: (((historyeve0__9.event_type)::text <> ALL ('{versionsSearchWithinQueryEvent,notesOfDecisionsSearchWithinQueryEvent,citingReferencesSearchWithinQueryEvent,tocSearchWithinQueryEvent,searchWithinDocumentEvent}'::text[])) AND (((historyeve0__9.product_view)::text = ANY ('{DefaultProductView,TNPPlus,PLCUS,Indigo,INDIGOCA}'::text[])) OR (historyeve0__9.product_view IS NULL)))
Rows Removed by Filter: 3
Buffers: shared hit=14 read=112
I/O Timings: read=33.044
-> Index Scan using history_event_display_timest_prism_guid_display_timestamp_idx10 on hist2.history_event_display_timestamp_20210401 historyeve0__10 (cost=0.55..1625.74 rows=325 width=13) (actual time=0.947..103.137 rows=701 loops=1)
Output: historyeve0__10.event_type
Index Cond: (((historyeve0__10.prism_guid)::text = 'i0acc051c00000133f57f4be2bbbdc5ef'::text) AND ((historyeve0__10.product_sid)::text = 'WestlawNext'::text) AND (historyeve0__10.is_deleted = '0'::numeric))
Filter: (((historyeve0__10.event_type)::text <> ALL ('{versionsSearchWithinQueryEvent,notesOfDecisionsSearchWithinQueryEvent,citingReferencesSearchWithinQueryEvent,tocSearchWithinQueryEvent,searchWithinDocumentEvent}'::text[])) AND (((historyeve0__10.product_view)::text = ANY ('{DefaultProductView,TNPPlus,PLCUS,Indigo,INDIGOCA}'::text[])) OR (historyeve0__10.product_view IS NULL)))
Rows Removed by Filter: 2
Buffers: shared hit=15 read=139
I/O Timings: read=51.782
-> Index Scan using history_event_display_timest_prism_guid_display_timestamp_idx11 on hist2.history_event_display_timestamp_20210501 historyeve0__11 (cost=0.55..1738.67 rows=351 width=13) (actual time=1.056..53.731 rows=646 loops=1)
Output: historyeve0__11.event_type
Index Cond: (((historyeve0__11.prism_guid)::text = 'i0acc051c00000133f57f4be2bbbdc5ef'::text) AND ((historyeve0__11.product_sid)::text = 'WestlawNext'::text) AND (historyeve0__11.is_deleted = '0'::numeric))
Filter: (((historyeve0__11.event_type)::text <> ALL ('{versionsSearchWithinQueryEvent,notesOfDecisionsSearchWithinQueryEvent,citingReferencesSearchWithinQueryEvent,tocSearchWithinQueryEvent,searchWithinDocumentEvent}'::text[])) AND (((historyeve0__11.product_view)::text = ANY ('{DefaultProductView,TNPPlus,PLCUS,Indigo,INDIGOCA}'::text[])) OR (historyeve0__11.product_view IS NULL)))
Rows Removed by Filter: 4
Buffers: shared hit=19 read=118
I/O Timings: read=6.834
-> Index Scan using history_event_display_timest_prism_guid_display_timestamp_idx12 on hist2.history_event_display_timestamp_20210601 historyeve0__12 (cost=0.55..794.55 rows=152 width=13) (actual time=1.576..45.888 rows=429 loops=1)
Output: historyeve0__12.event_type
Index Cond: (((historyeve0__12.prism_guid)::text = 'i0acc051c00000133f57f4be2bbbdc5ef'::text) AND ((historyeve0__12.product_sid)::text = 'WestlawNext'::text) AND (historyeve0__12.is_deleted = '0'::numeric))
Filter: (((historyeve0__12.event_type)::text <> ALL ('{versionsSearchWithinQueryEvent,notesOfDecisionsSearchWithinQueryEvent,citingReferencesSearchWithinQueryEvent,tocSearchWithinQueryEvent,searchWithinDocumentEvent}'::text[])) AND (((historyeve0__12.product_view)::text = ANY ('{DefaultProductView,TNPPlus,PLCUS,Indigo,INDIGOCA}'::text[])) OR (historyeve0__12.product_view IS NULL)))
Rows Removed by Filter: 3
Buffers: shared hit=17 read=91
I/O Timings: read=8.620
-> Index Scan using history_event_display_timest_prism_guid_display_timestamp_idx13 on hist2.history_event_display_timestamp_20210701 historyeve0__13 (cost=0.55..23.56 rows=5 width=12) (actual time=1.128..16.989 rows=126 loops=1)
Output: historyeve0__13.event_type
Index Cond: (((historyeve0__13.prism_guid)::text = 'i0acc051c00000133f57f4be2bbbdc5ef'::text) AND ((historyeve0__13.product_sid)::text = 'WestlawNext'::text) AND (historyeve0__13.is_deleted = '0'::numeric))
Filter: (((historyeve0__13.event_type)::text <> ALL ('{versionsSearchWithinQueryEvent,notesOfDecisionsSearchWithinQueryEvent,citingReferencesSearchWithinQueryEvent,tocSearchWithinQueryEvent,searchWithinDocumentEvent}'::text[])) AND (((historyeve0__13.product_view)::text = ANY ('{DefaultProductView,TNPPlus,PLCUS,Indigo,INDIGOCA}'::text[])) OR (historyeve0__13.product_view IS NULL)))
Buffers: shared hit=10 read=33
I/O Timings: read=7.341
-> Index Scan using history_event_display_timest_prism_guid_display_timestamp_idx14 on hist2.history_event_display_timestamp_20210801 historyeve0__14 (cost=0.55..8.58 rows=1 width=12) (actual time=0.024..0.025 rows=0 loops=1)
Output: historyeve0__14.event_type
Index Cond: (((historyeve0__14.prism_guid)::text = 'i0acc051c00000133f57f4be2bbbdc5ef'::text) AND ((historyeve0__14.product_sid)::text = 'WestlawNext'::text) AND (historyeve0__14.is_deleted = '0'::numeric))
Filter: (((historyeve0__14.event_type)::text <> ALL ('{versionsSearchWithinQueryEvent,notesOfDecisionsSearchWithinQueryEvent,citingReferencesSearchWithinQueryEvent,tocSearchWithinQueryEvent,searchWithinDocumentEvent}'::text[])) AND (((historyeve0__14.product_view)::text = ANY ('{DefaultProductView,TNPPlus,PLCUS,Indigo,INDIGOCA}'::text[])) OR (historyeve0__14.product_view IS NULL)))
Buffers: shared hit=4
-> Index Scan using history_event_display_timest_prism_guid_display_timestamp_idx15 on hist2.history_event_display_timestamp_20210901 historyeve0__15 (cost=0.14..8.17 rows=1 width=118) (actual time=0.015..0.015 rows=0 loops=1)
Output: historyeve0__15.event_type
Index Cond: (((historyeve0__15.prism_guid)::text = 'i0acc051c00000133f57f4be2bbbdc5ef'::text) AND ((historyeve0__15.product_sid)::text = 'WestlawNext'::text) AND (historyeve0__15.is_deleted = '0'::numeric))
Filter: (((historyeve0__15.event_type)::text <> ALL ('{versionsSearchWithinQueryEvent,notesOfDecisionsSearchWithinQueryEvent,citingReferencesSearchWithinQueryEvent,tocSearchWithinQueryEvent,searchWithinDocumentEvent}'::text[])) AND (((historyeve0__15.product_view)::text = ANY ('{DefaultProductView,TNPPlus,PLCUS,Indigo,INDIGOCA}'::text[])) OR (historyeve0__15.product_view IS NULL)))
Buffers: shared hit=2
-> Index Scan using history_event_display_timest_prism_guid_display_timestamp_idx32 on hist2.history_event_display_timestamp_20211001 historyeve0__16 (cost=0.14..8.17 rows=1 width=118) (actual time=0.013..0.013 rows=0 loops=1)
Output: historyeve0__16.event_type
Index Cond: (((historyeve0__16.prism_guid)::text = 'i0acc051c00000133f57f4be2bbbdc5ef'::text) AND ((historyeve0__16.product_sid)::text = 'WestlawNext'::text) AND (historyeve0__16.is_deleted = '0'::numeric))
Filter: (((historyeve0__16.event_type)::text <> ALL ('{versionsSearchWithinQueryEvent,notesOfDecisionsSearchWithinQueryEvent,citingReferencesSearchWithinQueryEvent,tocSearchWithinQueryEvent,searchWithinDocumentEvent}'::text[])) AND (((historyeve0__16.product_view)::text = ANY ('{DefaultProductView,TNPPlus,PLCUS,Indigo,INDIGOCA}'::text[])) OR (historyeve0__16.product_view IS NULL)))
Buffers: shared hit=2
-> Index Scan using history_event_display_timest_prism_guid_display_timestamp_idx34 on hist2.history_event_display_timestamp_20211101 historyeve0__17 (cost=0.14..8.17 rows=1 width=118) (actual time=0.015..0.015 rows=0 loops=1)
Output: historyeve0__17.event_type
Index Cond: (((historyeve0__17.prism_guid)::text = 'i0acc051c00000133f57f4be2bbbdc5ef'::text) AND ((historyeve0__17.product_sid)::text = 'WestlawNext'::text) AND (historyeve0__17.is_deleted = '0'::numeric))
Filter: (((historyeve0__17.event_type)::text <> ALL ('{versionsSearchWithinQueryEvent,notesOfDecisionsSearchWithinQueryEvent,citingReferencesSearchWithinQueryEvent,tocSearchWithinQueryEvent,searchWithinDocumentEvent}'::text[])) AND (((historyeve0__17.product_view)::text = ANY ('{DefaultProductView,TNPPlus,PLCUS,Indigo,INDIGOCA}'::text[])) OR (historyeve0__17.product_view IS NULL)))
Buffers: shared hit=2
-> Index Scan using history_event_display_timest_prism_guid_display_timestamp_idx36 on hist2.history_event_display_timestamp_20211201 historyeve0__18 (cost=0.14..8.17 rows=1 width=118) (actual time=0.013..0.013 rows=0 loops=1)
Output: historyeve0__18.event_type
Index Cond: (((historyeve0__18.prism_guid)::text = 'i0acc051c00000133f57f4be2bbbdc5ef'::text) AND ((historyeve0__18.product_sid)::text = 'WestlawNext'::text) AND (historyeve0__18.is_deleted = '0'::numeric))
Filter: (((historyeve0__18.event_type)::text <> ALL ('{versionsSearchWithinQueryEvent,notesOfDecisionsSearchWithinQueryEvent,citingReferencesSearchWithinQueryEvent,tocSearchWithinQueryEvent,searchWithinDocumentEvent}'::text[])) AND (((historyeve0__18.product_view)::text = ANY ('{DefaultProductView,TNPPlus,PLCUS,Indigo,INDIGOCA}'::text[])) OR (historyeve0__18.product_view IS NULL)))
Buffers: shared hit=2
-> Index Scan using history_event_display_timest_prism_guid_display_timestamp_idx38 on hist2.history_event_display_timestamp_20220101 historyeve0__19 (cost=0.14..8.17 rows=1 width=118) (actual time=0.015..0.015 rows=0 loops=1)
Output: historyeve0__19.event_type
Index Cond: (((historyeve0__19.prism_guid)::text = 'i0acc051c00000133f57f4be2bbbdc5ef'::text) AND ((historyeve0__19.product_sid)::text = 'WestlawNext'::text) AND (historyeve0__19.is_deleted = '0'::numeric))
Filter: (((historyeve0__19.event_type)::text <> ALL ('{versionsSearchWithinQueryEvent,notesOfDecisionsSearchWithinQueryEvent,citingReferencesSearchWithinQueryEvent,tocSearchWithinQueryEvent,searchWithinDocumentEvent}'::text[])) AND (((historyeve0__19.product_view)::text = ANY ('{DefaultProductView,TNPPlus,PLCUS,Indigo,INDIGOCA}'::text[])) OR (historyeve0__19.product_view IS NULL)))
Buffers: shared hit=2
-> Index Scan using history_event_display_timest_prism_guid_display_timestamp_idx40 on hist2.history_event_display_timestamp_20220201 historyeve0__20 (cost=0.14..8.17 rows=1 width=118) (actual time=0.015..0.015 rows=0 loops=1)
Output: historyeve0__20.event_type
Index Cond: (((historyeve0__20.prism_guid)::text = 'i0acc051c00000133f57f4be2bbbdc5ef'::text) AND ((historyeve0__20.product_sid)::text = 'WestlawNext'::text) AND (historyeve0__20.is_deleted = '0'::numeric))
Filter: (((historyeve0__20.event_type)::text <> ALL ('{versionsSearchWithinQueryEvent,notesOfDecisionsSearchWithinQueryEvent,citingReferencesSearchWithinQueryEvent,tocSearchWithinQueryEvent,searchWithinDocumentEvent}'::text[])) AND (((historyeve0__20.product_view)::text = ANY ('{DefaultProductView,TNPPlus,PLCUS,Indigo,INDIGOCA}'::text[])) OR (historyeve0__20.product_view IS NULL)))
Buffers: shared hit=2
-> Index Scan using history_event_display_timest_prism_guid_display_timestamp_idx42 on hist2.history_event_display_timestamp_20220301 historyeve0__21 (cost=0.14..8.17 rows=1 width=118) (actual time=0.013..0.014 rows=0 loops=1)
Output: historyeve0__21.event_type
Index Cond: (((historyeve0__21.prism_guid)::text = 'i0acc051c00000133f57f4be2bbbdc5ef'::text) AND ((historyeve0__21.product_sid)::text = 'WestlawNext'::text) AND (historyeve0__21.is_deleted = '0'::numeric))
Filter: (((historyeve0__21.event_type)::text <> ALL ('{versionsSearchWithinQueryEvent,notesOfDecisionsSearchWithinQueryEvent,citingReferencesSearchWithinQueryEvent,tocSearchWithinQueryEvent,searchWithinDocumentEvent}'::text[])) AND (((historyeve0__21.product_view)::text = ANY ('{DefaultProductView,TNPPlus,PLCUS,Indigo,INDIGOCA}'::text[])) OR (historyeve0__21.product_view IS NULL)))
Buffers: shared hit=2
Settings: effective_cache_size = '88283528kB', enable_bitmapscan = 'off', search_path = 'hist2'
Planning Time: 3.774 ms
Execution Time: 647.086 ms
SQL Hash: 901113095, Plan Hash: -1815601721
(147 rows)
^ permalink raw reply [nested|flat] 48+ messages in thread
* Re: Query Performance
2021-07-21 17:13 Query Performance Dirschel, Steve <steve.dirschel@thomsonreuters.com>
@ 2021-07-21 18:03 ` Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 0 replies; 48+ messages in thread
From: Tom Lane @ 2021-07-21 18:03 UTC (permalink / raw)
To: Dirschel, Steve <steve.dirschel@thomsonreuters.com>; +Cc: pgsql-performance@lists.postgresql.org <pgsql-performance@lists.postgresql.org>
"Dirschel, Steve" <steve.dirschel@thomsonreuters.com> writes:
> I have a sample query that is doing more work if some of the reads are physical reads and I'm trying to understand why. If you look at attached QueryWithPhyReads.txt it shows the query did Buffers: shared hit=171 read=880. So it did 171 + 880 = 1051 total block reads (some logical, some physical). QueryWithNoPhyReads.txt shows execution statistics of the execution of the exact same query with same data point. The only difference is the first execution loaded blocks into memory so this execution had all shared hits. In this case the query did this much work: Buffers: shared hit=581.
You haven't provided a lot of context for this observation, but I can
think of at least one explanation for the discrepancy. If the first
query was the first access to these tables after a bunch of updates,
it would have been visiting a lot of now-dead row versions. It would
then have marked the corresponding index entries dead, resulting in the
second execution not having to visit as many heap pages.
regards, tom lane
^ permalink raw reply [nested|flat] 48+ messages in thread
* Query performance !
@ 2021-07-26 23:59 kenny a <kenny.pg18@gmail.com>
2021-07-27 17:14 ` Query performance ! kenny a <kenny.pg18@gmail.com>
2021-07-29 15:10 ` Re: Query performance ! Justin Pryzby <pryzby@telsasoft.com>
0 siblings, 2 replies; 48+ messages in thread
From: kenny a @ 2021-07-26 23:59 UTC (permalink / raw)
To: pgsql-sql@lists.postgresql.org; pgsql-performance@lists.postgresql.org; pgsql-general@lists.postgresql.org; pgsql-admin@lists.postgresql.org
Hi Experts,
The attached query is performing slow, this needs to be optimized to
improve the performance.
Could you help me with query rewrite (or) on new indexes to be created to
improve the performance?
Thanks a ton in advance for your support.
SELECT tab2.rulename,
tab2.totalexecuted,
tab2.uniqueorder,
tab1.des description,
tab2.max,
tab1.ruletype,
tab2.uniqueorder pertange
FROM (SELECT re.rule_name ruleName,
Count (*) totalExecuted,
Count (DISTINCT re.order_id) uniqueOrder,
Max (re.id)
FROM rule_execution re
WHERE ? = ?
AND re.status = ?
AND re. type IN ( ?, ? )
AND re.order_id IN ( ?, ?, ?, ?,
****************************
****************************
?, ? )
GROUP BY re.rule_name) tab2
INNER JOIN (SELECT re2.rule_name,
Max (re2.rule_description) des,
Max (re2. type) ruleType
FROM (SELECT re4.rule_name,
Max (re4.created_date)
FROM sample.rule_execution re4
WHERE ? = ?
AND re4.status = ?
AND re4. type IN ( ?, ? )
AND re4.order_id IN ( ?, ?, ?, ?,
****************************
****************************
?, ? )
GROUP BY re4.rule_name) re1
INNER JOIN rule_execution re2
ON re2.rule_name = re1.rule_name
AND re2.created_date = re1. max
GROUP BY re2.rule_name) tab1
ON tab1.rule_name = tab2.rulename
ORDER BY totalexecuted DESC,
rulename ASC
LIMIT ?
currrent indexes on rule_execution table :
sample_rule_execution_upper_sample_id_idx
sample_rule_execution_sample_id_idx
sample_rule_execution_order_id_idx
rule_pkey
rule_execution_migration_unique
Attachments:
[text/plain] Query_RExe.txt (2.1K, ../../CABfOcveprJXDODWgD+7-LtsYzRAKJq+1sftGo5=b7YzdMsEm9g@mail.gmail.com/3-Query_RExe.txt)
download | inline:
SELECT tab2.rulename,
tab2.totalexecuted,
tab2.uniqueorder,
tab1.des description,
tab2.max,
tab1.ruletype,
tab2.uniqueorder pertange
FROM (SELECT re.rule_name ruleName,
Count (*) totalExecuted,
Count (DISTINCT re.order_id) uniqueOrder,
Max (re.id)
FROM rule_execution re
WHERE ? = ?
AND re.status = ?
AND re. type IN ( ?, ? )
AND re.order_id IN ( ?, ?, ?, ?,
****************************
****************************
?, ? )
GROUP BY re.rule_name) tab2
INNER JOIN (SELECT re2.rule_name,
Max (re2.rule_description) des,
Max (re2. type) ruleType
FROM (SELECT re4.rule_name,
Max (re4.created_date)
FROM sample.rule_execution re4
WHERE ? = ?
AND re4.status = ?
AND re4. type IN ( ?, ? )
AND re4.order_id IN ( ?, ?, ?, ?,
****************************
****************************
?, ? )
GROUP BY re4.rule_name) re1
INNER JOIN rule_execution re2
ON re2.rule_name = re1.rule_name
AND re2.created_date = re1. max
GROUP BY re2.rule_name) tab1
ON tab1.rule_name = tab2.rulename
ORDER BY totalexecuted DESC,
rulename ASC
LIMIT ?
currrent indexes on rule_execution table :
sample_rule_execution_upper_sample_id_idx
sample_rule_execution_sample_id_idx
sample_rule_execution_order_id_idx
rule_pkey
rule_execution_migration_unique
^ permalink raw reply [nested|flat] 48+ messages in thread
* Query performance !
2021-07-26 23:59 Query performance ! kenny a <kenny.pg18@gmail.com>
@ 2021-07-27 17:14 ` kenny a <kenny.pg18@gmail.com>
2021-07-27 17:18 ` Re: Query performance ! Bruce Momjian <bruce@momjian.us>
1 sibling, 1 reply; 48+ messages in thread
From: kenny a @ 2021-07-27 17:14 UTC (permalink / raw)
To: pgsql-performance@lists.postgresql.org
>
> Hi Experts,
>
> The attached query is performing slow, this needs to be optimized to
> improve the performance.
>
> Could you help me with query rewrite (or) on new indexes to be created to
> improve the performance?
>
> Thanks a ton in advance for your support.
>
^ permalink raw reply [nested|flat] 48+ messages in thread
* Re: Query performance !
2021-07-26 23:59 Query performance ! kenny a <kenny.pg18@gmail.com>
2021-07-27 17:14 ` Query performance ! kenny a <kenny.pg18@gmail.com>
@ 2021-07-27 17:18 ` Bruce Momjian <bruce@momjian.us>
0 siblings, 0 replies; 48+ messages in thread
From: Bruce Momjian @ 2021-07-27 17:18 UTC (permalink / raw)
To: kenny a <kenny.pg18@gmail.com>; +Cc: pgsql-performance@lists.postgresql.org
On Tue, Jul 27, 2021 at 10:44:03PM +0530, kenny a wrote:
> Hi Experts,
>
> The attached query is performing slow, this needs to be optimized to
> improve the performance.
>
> Could you help me with query rewrite (or) on new indexes to be created to
> improve the performance?
>
> Thanks a ton in advance for your support.
Uh, there is no query, and I think you should read this:
https://wiki.postgresql.org/wiki/Slow_Query_Questions
--
Bruce Momjian <bruce@momjian.us> https://momjian.us
EDB https://enterprisedb.com
If only the physical world exists, free will is an illusion.
^ permalink raw reply [nested|flat] 48+ messages in thread
* Re: Query performance !
2021-07-26 23:59 Query performance ! kenny a <kenny.pg18@gmail.com>
@ 2021-07-29 15:10 ` Justin Pryzby <pryzby@telsasoft.com>
1 sibling, 0 replies; 48+ messages in thread
From: Justin Pryzby @ 2021-07-29 15:10 UTC (permalink / raw)
To: kenny a <kenny.pg18@gmail.com>; +Cc: pgsql-performance@lists.postgresql.org
Please don't cross post to multiple lists like this.
Cc: pgsql-sql@lists.postgresql.org, pgsql-performance@lists.postgresql.org,
pgsql-general@lists.postgresql.org,
pgsql-admin@lists.postgresql.org
If you're hoping for help on the -performance list, see this page and send the
"explain analyze" for this query.
https://wiki.postgresql.org/wiki/Slow_Query_Questions
On Tue, Jul 27, 2021 at 05:29:19AM +0530, kenny a wrote:
> Hi Experts,
>
> The attached query is performing slow, this needs to be optimized to
> improve the performance.
>
> Could you help me with query rewrite (or) on new indexes to be created to
> improve the performance?
>
> Thanks a ton in advance for your support.
^ permalink raw reply [nested|flat] 48+ messages in thread
* Query performance
@ 2026-04-27 15:52 Dirschel, Steve-CW <Steve.Dirschel@bestbuy.com>
2026-04-28 05:33 ` Re: Query performance Laurenz Albe <laurenz.albe@cybertec.at>
0 siblings, 1 reply; 48+ messages in thread
From: Dirschel, Steve-CW @ 2026-04-27 15:52 UTC (permalink / raw)
To: pgsql-performance@lists.postgresql.org <pgsql-performance@lists.postgresql.org>
Aurora Postgres version 17.4.
Table in question:
\d poslog_publisher_rms_stage
Table "public.poslog_publisher_rms_stage"
Column | Type | Collation | Nullable | Default
-------------------+--------------------------+-----------+----------+---------
stage_id | uuid | | not null |
status | character varying(100) | | |
message_body | text | | not null |
error_code | character varying(100) | | |
error_category | character varying(100) | | |
error_message | text | | |
error_retry_count | integer | | | 0
create_date | timestamp with time zone | | not null | now()
modified_date | timestamp with time zone | | not null | now()
Indexes:
"poslog_publisher_rms_stage_pkey" PRIMARY KEY, btree (stage_id)
"idx_poslog_publisher_stage_create_date_col" btree (create_date)
"idx_poslog_publisher_stage_status_error_retry_count_modi_date_c" btree (status, error_retry_count, modified_date)
Referenced by:
TABLE "poslog_publisher_rms_detail" CONSTRAINT "fk_poslog_publisher_detail_stage_id" FOREIGN KEY (stage_id) REFERENCES poslog_publisher_rms_stage(stage_id)
Publications:
“sashpsrms_publication"
The table is constantly getting loaded into. Rows are inserted with a status ready and then there is a different process looking for that status and will update to processed after processing the row. We have multiple tables like this.
Then every 2 hours a different process runs this query looking for failed or unprocessed rows:
select
ppse.stage_id as stageId,
ppse.status as status,
ppse.message_body as messageBody
from
poslog_publisher_rms_stage ppse
where
ppse.status in ('UNPROCESSED','FAILED')
and ppse.error_retry_count < 3
order by
ppse.create_date
limit 100;
If I run that query with explain it is doing a ton of work to find 0 rows. The index it uses seems appropriate for the query.
QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Limit (cost=10.92..10.93 rows=1 width=1019) (actual time=66566.823..66566.824 rows=0 loops=1)
Buffers: shared hit=1509768 read=2011479
I/O Timings: shared read=79792.017
-> Sort (cost=10.92..10.93 rows=1 width=1019) (actual time=66566.821..66566.821 rows=0 loops=1)
Sort Key: create_date
Sort Method: quicksort Memory: 25kB
Buffers: shared hit=1509768 read=2011479
I/O Timings: shared read=79792.017
-> Index Scan using idx_poslog_publisher_stage_status_error_retry_count_modi_date_c on poslog_publisher_rms_stage ppse (cost=0.57..10.91 rows=1 width=1019) (actual time=66566.761..66566.761 rows=0 loops=1)
Index Cond: (((status)::text = ANY ('{UNPROCESSED,FAILED}'::text[])) AND (error_retry_count < 3))
Buffers: shared hit=1509765 read=2011479
I/O Timings: shared read=79792.017
Planning:
Buffers: shared hit=195 read=1
I/O Timings: shared read=1.038
Planning Time: 2.909 ms
Execution Time: 66581.498 ms
The query did 3.5 million block reads when scanning the index of which 1.5 million were in memory and 2 million were from disk. 5 seconds later I ran the exact same query again:
QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Limit (cost=10.92..10.93 rows=1 width=1019) (actual time=23.589..23.591 rows=0 loops=1)
Buffers: shared hit=18736
-> Sort (cost=10.92..10.93 rows=1 width=1019) (actual time=23.588..23.589 rows=0 loops=1)
Sort Key: create_date
Sort Method: quicksort Memory: 25kB
Buffers: shared hit=18736
-> Index Scan using idx_poslog_publisher_stage_status_error_retry_count_modi_date_c on poslog_publisher_rms_stage ppse (cost=0.57..10.91 rows=1 width=1019) (actual time=23.583..23.584 rows=0 loops=1)
Index Cond: (((status)::text = ANY ('{UNPROCESSED,FAILED}'::text[])) AND (error_retry_count < 3))
Buffers: shared hit=18736
Planning Time: 0.118 ms
Execution Time: 23.628 ms
Now it only did 18k block reads all in memory. It used the same index, it also returned 0 rows. Between those 2 runs I looked at pg_stat_user_tables and could see the n_tup_ins increased by 13, n_tup_del increased by 13, n_live_tup increased by 13, and n_dead_tup increased by 13. n_live_tup was 96 million and n_dead_tup was 8.4 million. 18k logical reads to find 0 rows is still high but I believe that is most likely caused by the 8.4 million n_dead_tups.
15 minutes later I ran the query again:
QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Limit (cost=10.92..10.93 rows=1 width=1019) (actual time=59431.795..59431.796 rows=0 loops=1)
Buffers: shared hit=1286676 read=1734000
I/O Timings: shared read=70153.578
-> Sort (cost=10.92..10.93 rows=1 width=1019) (actual time=59431.794..59431.794 rows=0 loops=1)
Sort Key: create_date
Sort Method: quicksort Memory: 25kB
Buffers: shared hit=1286676 read=1734000
I/O Timings: shared read=70153.578
-> Index Scan using idx_poslog_publisher_stage_status_error_retry_count_modi_date_c on poslog_publisher_rms_stage ppse (cost=0.57..10.91 rows=1 width=1019) (actual time=59431.789..59431.790 rows=0 loops=1)
Index Cond: (((status)::text = ANY ('{UNPROCESSED,FAILED}'::text[])) AND (error_retry_count < 3))
Buffers: shared hit=1286676 read=1734000
I/O Timings: shared read=70153.578
Planning Time: 0.114 ms
Execution Time: 59431.839 ms
Total blocks reads increased from 18k to 3 million. n_tup_ins, n_tup_del, n_live_tup, and n_dead_tup all increased by 10,320 over that 15 minute period of time.
What is going on here where this query has to do 3+ million block reads to find 0 rows? And how is it possible when I run the query 2 times in a row the logical reads from the 2nd run comes down significantly? Is this somehow related to determining if rows are visible or something like that? When I waited 15 minutes between runs the inserted/updated rows only increased by 10.3k yet total block reads increased by 3 million.
Thanks
^ permalink raw reply [nested|flat] 48+ messages in thread
* Re: Query performance
2026-04-27 15:52 Query performance Dirschel, Steve-CW <Steve.Dirschel@bestbuy.com>
@ 2026-04-28 05:33 ` Laurenz Albe <laurenz.albe@cybertec.at>
0 siblings, 0 replies; 48+ messages in thread
From: Laurenz Albe @ 2026-04-28 05:33 UTC (permalink / raw)
To: Dirschel, Steve-CW <Steve.Dirschel@bestbuy.com>; pgsql-performance@lists.postgresql.org <pgsql-performance@lists.postgresql.org>
On Mon, 2026-04-27 at 15:52 +0000, Dirschel, Steve-CW wrote:
> Aurora Postgres version 17.4.
Aurora works quite differently, as far as storage is concerned, so you may be
suffering from some peculiarity of that closed source software.
> Table in question:
>
>
> \d poslog_publisher_rms_stage
> Table "public.poslog_publisher_rms_stage"
> Column | Type | Collation | Nullable | Default
> -------------------+--------------------------+-----------+----------+---------
> stage_id | uuid | | not null |
> status | character varying(100) | | |
> message_body | text | | not null |
> error_code | character varying(100) | | |
> error_category | character varying(100) | | |
> error_message | text | | |
> error_retry_count | integer | | | 0
> create_date | timestamp with time zone | | not null | now()
> modified_date | timestamp with time zone | | not null | now()
> Indexes:
> "poslog_publisher_rms_stage_pkey" PRIMARY KEY, btree (stage_id)
> "idx_poslog_publisher_stage_create_date_col" btree (create_date)
> "idx_poslog_publisher_stage_status_error_retry_count_modi_date_c" btree (status, error_retry_count, modified_date)
> Referenced by:
> TABLE "poslog_publisher_rms_detail" CONSTRAINT "fk_poslog_publisher_detail_stage_id" FOREIGN KEY (stage_id) REFERENCES poslog_publisher_rms_stage(stage_id)
> Publications:
> “sashpsrms_publication"
>
> The table is constantly getting loaded into. Rows are inserted with a status ready and
> then there is a different process looking for that status and will update to processed
> after processing the row. We have multiple tables like this.
>
> Then every 2 hours a different process runs this query looking for failed or unprocessed rows:
>
> select
> ppse.stage_id as stageId,
> ppse.status as status,
> ppse.message_body as messageBody
> from
> poslog_publisher_rms_stage ppse
> where
> ppse.status in ('UNPROCESSED','FAILED')
> and ppse.error_retry_count < 3
> order by
> ppse.create_date
> limit 100;
>
> If I run that query with explain it is doing a ton of work to find 0 rows. The index it
> uses seems appropriate for the query.
The index can be used, but it is far from perfect. The ideal index would be:
CREATE INDEX ON poslog_publisher_rms_stage (create_date)
WHERE status in ('UNPROCESSED','FAILED') AND error_retry_count < 3;
> QUERY PLAN
> -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
> Limit (cost=10.92..10.93 rows=1 width=1019) (actual time=66566.823..66566.824 rows=0 loops=1)
> Buffers: shared hit=1509768 read=2011479
> I/O Timings: shared read=79792.017
> -> Sort (cost=10.92..10.93 rows=1 width=1019) (actual time=66566.821..66566.821 rows=0 loops=1)
> Sort Key: create_date
> Sort Method: quicksort Memory: 25kB
> Buffers: shared hit=1509768 read=2011479
> I/O Timings: shared read=79792.017
> -> Index Scan using idx_poslog_publisher_stage_status_error_retry_count_modi_date_c on poslog_publisher_rms_stage ppse (cost=0.57..10.91 rows=1 width=1019) (actual time=66566.761..66566.761 rows=0 loops=1)
> Index Cond: (((status)::text = ANY ('{UNPROCESSED,FAILED}'::text[])) AND (error_retry_count < 3))
> Buffers: shared hit=1509765 read=2011479
> I/O Timings: shared read=79792.017
> Planning:
> Buffers: shared hit=195 read=1
> I/O Timings: shared read=1.038
> Planning Time: 2.909 ms
> Execution Time: 66581.498 ms
>
> The query did 3.5 million block reads when scanning the index of which 1.5 million were
> in memory and 2 million were from disk. 5 seconds later I ran the exact same query again:
>
> QUERY PLAN
> -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
> Limit (cost=10.92..10.93 rows=1 width=1019) (actual time=23.589..23.591 rows=0 loops=1)
> Buffers: shared hit=18736
> -> Sort (cost=10.92..10.93 rows=1 width=1019) (actual time=23.588..23.589 rows=0 loops=1)
> Sort Key: create_date
> Sort Method: quicksort Memory: 25kB
> Buffers: shared hit=18736
> -> Index Scan using idx_poslog_publisher_stage_status_error_retry_count_modi_date_c on poslog_publisher_rms_stage ppse (cost=0.57..10.91 rows=1 width=1019) (actual time=23.583..23.584 rows=0 loops=1)
> Index Cond: (((status)::text = ANY ('{UNPROCESSED,FAILED}'::text[])) AND (error_retry_count < 3))
> Buffers: shared hit=18736
> Planning Time: 0.118 ms
> Execution Time: 23.628 ms
>
> Now it only did 18k block reads all in memory. It used the same index, it also
> returned 0 rows. Between those 2 runs I looked at pg_stat_user_tables and could see the
> n_tup_ins increased by 13, n_tup_del increased by 13, n_live_tup increased by 13, and
> n_dead_tup increased by 13. n_live_tup was 96 million and n_dead_tup was 8.4 million.
> 18k logical reads to find 0 rows is still high but I believe that is most likely caused
> by the 8.4 million n_dead_tups.
>
> What is going on here where this query has to do 3+ million block reads to find 0 rows?
> And how is it possible when I run the query 2 times in a row the logical reads from the
> 2nd run comes down significantly? Is this somehow related to determining if rows are
> visible or something like that?
The only way I can imagine this happening in PostgreSQL is if the first execution
kills a lot of index tuples (https://www.cybertec-postgresql.com/en/killed-index-tuples/).
> When I waited 15 minutes between runs the inserted/updated rows only increased by
> 10.3k yet total block reads increased by 3 million.
Now that seems to speak against the above theory, so you are suffering from some Amazon-
specific behavior.
Yours,
Laurenz Albe
^ permalink raw reply [nested|flat] 48+ messages in thread
end of thread, other threads:[~2026-04-28 05:33 UTC | newest]
Thread overview: 48+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2004-06-28 03:26 Query performance Bill <bill@math.uchicago.edu>
2004-06-28 05:23 ` Mischa Sandberg <mischa_sandberg@telus.net>
2004-06-28 09:14 ` Richard Huxton <dev@archonet.com>
2004-06-28 17:02 ` Bill <bill@math.uchicago.edu>
2004-06-29 08:37 ` Richard Huxton <dev@archonet.com>
2004-06-29 17:33 ` Bill <bill@math.uchicago.edu>
2004-06-29 19:03 ` Richard Huxton <dev@archonet.com>
2004-06-29 19:51 ` Bruno Wolff III <bruno@wolff.to>
2004-06-30 13:47 ` Bill <bill@math.uchicago.edu>
2004-06-30 14:27 ` Rod Taylor <pg@rbt.ca>
2004-08-21 00:03 Query Performance Danilo Mota <dmota@nexen.com.br>
2004-08-21 01:02 ` Re: Query Performance Brad Bulger <brad@madfish.com>
2005-03-11 18:47 Query performance Lou O'Quin <loquin@talleyds.com>
2005-03-11 19:10 ` Tom Lane <tgl@sss.pgh.pa.us>
2005-03-11 19:38 Re: Query performance Lou O'Quin <loquin@talleyds.com>
2005-03-11 20:21 ` Tom Lane <tgl@sss.pgh.pa.us>
2005-03-11 20:35 Re: Query performance Lou O'Quin <loquin@talleyds.com>
2006-05-22 23:33 ` Steinar H. Gunderson <sgunderson@bigfoot.com>
2006-05-23 07:10 ` Antonio Batovanja <antonio.batovanja@humanomed.co.at>
2006-05-31 01:07 ` Christopher Kings-Lynne <chris.kings-lynne@calorieking.com>
2006-05-28 21:56 ` Erwin Brandstetter <brsaweda@gmail.com>
2006-05-28 22:38 ` Erwin Brandstetter <brsaweda@gmail.com>
2013-06-13 07:49 Query performance K P Manoj <kpmanojpg@gmail.com>
2013-06-13 09:03 ` Sergey Konoplev <gray.ru@gmail.com>
2015-01-25 05:41 Query performance Joe Van Dyk <joe@tanga.com>
2015-01-25 05:43 ` Joe Van Dyk <joe@tanga.com>
2015-01-25 05:45 ` Joe Van Dyk <joe@tanga.com>
2015-01-25 06:12 ` Pavel Stehule <pavel.stehule@gmail.com>
2015-01-25 06:38 ` Joe Van Dyk <joe@tanga.com>
2015-01-25 07:14 ` Pavel Stehule <pavel.stehule@gmail.com>
2015-01-25 07:20 ` Joe Van Dyk <joe@tanga.com>
2015-01-25 08:03 ` Pavel Stehule <pavel.stehule@gmail.com>
2015-01-31 01:40 ` Jim Nasby <Jim.Nasby@BlueTreble.com>
2015-01-31 06:28 ` Pavel Stehule <pavel.stehule@gmail.com>
2015-01-25 16:57 ` Tomas Vondra <tomas.vondra@2ndquadrant.com>
2015-01-25 21:07 ` Marc Mamin <M.Mamin@intershop.de>
2017-02-20 21:39 Query Performance Diego Vargas <diegov@propaas.com>
2020-10-22 00:32 Query performance Nagaraj Raj <nagaraj.sf@yahoo.com>
2020-10-22 01:09 ` Justin Pryzby <pryzby@telsasoft.com>
2020-10-22 01:11 ` David G. Johnston <david.g.johnston@gmail.com>
2021-07-21 17:13 Query Performance Dirschel, Steve <steve.dirschel@thomsonreuters.com>
2021-07-21 18:03 ` Re: Query Performance Tom Lane <tgl@sss.pgh.pa.us>
2021-07-26 23:59 Query performance ! kenny a <kenny.pg18@gmail.com>
2021-07-27 17:14 ` Query performance ! kenny a <kenny.pg18@gmail.com>
2021-07-27 17:18 ` Re: Query performance ! Bruce Momjian <bruce@momjian.us>
2021-07-29 15:10 ` Re: Query performance ! Justin Pryzby <pryzby@telsasoft.com>
2026-04-27 15:52 Query performance Dirschel, Steve-CW <Steve.Dirschel@bestbuy.com>
2026-04-28 05:33 ` Laurenz Albe <laurenz.albe@cybertec.at>
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox