,opening MONEY

,closing =20 MONEY

,PRIMARY KEY (stock, asof)

)

SELECT stock, AVG((closing-opening)/o= pening)=20 as ratio

FROM Prices

GROUP BY stock

ORDER BY ratio DESC LIMIT 10; -- top = 10=20 best-performing stocks.

""Bill"" <bill@math.uchicago.edu> wrote= in=20 message news:BILLSA1Xv= pFVjCRGryW00000002@bill.fefferman.org...

Actually, I have so= me=20 queries that are slow, however I was wondering if you could help me write= a=20 query that is rather simple, but I, as a true database novice, can't seem= to=20 conjure. So we have stocks, as I have previously said, and I have a= huge=20 table which contains all of the opening and closing prices of some stocks= from=20 each day. What I like to do, in English, for each stock in each day= is=20 find a ratio: abs(closing-opening)/opening. Then I would like to av= erage=20 all of the ratios of each day of each individual stock together to find a= =20 final ratio for each stock, then I would like to find the highest average= , to=20 find the best performing stock. So what query can I use, and (as is= =20 appropriate for this group), how can it be optimized to run the=20 fastest?

------=_NextPart_000_004C_01C45C95.6EC8EA80-- From dev@archonet.com Mon Jun 28 09:14:15 2004 X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id 0120DD1B347 for ; Mon, 28 Jun 2004 06:14:15 -0300 (ADT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 51185-02 for ; Mon, 28 Jun 2004 09:14:14 +0000 (GMT) Received: from anchor-post-32.mail.demon.net (anchor-post-32.mail.demon.net [194.217.242.90]) by svr1.postgresql.org (Postfix) with ESMTP id C0558D1B2BD for ; Mon, 28 Jun 2004 06:14:11 -0300 (ADT) Received: from mwynhau.demon.co.uk ([193.237.186.96] helo=mainbox.archonet.com) by anchor-post-32.mail.demon.net with esmtp (Exim 3.35 #1) id 1BesDN-0007o0-0W; Mon, 28 Jun 2004 10:14:13 +0100 Received: from [192.168.1.17] (client17.archonet.com [192.168.1.17]) by mainbox.archonet.com (Postfix) with ESMTP id 6DB0716C93; Mon, 28 Jun 2004 10:14:12 +0100 (BST) Message-ID: <40DFE163.8000107@archonet.com> Date: Mon, 28 Jun 2004 10:14:11 +0100 From: Richard Huxton User-Agent: Mozilla Thunderbird 0.7 (X11/20040615) X-Accept-Language: en-us, en MIME-Version: 1.0 To: Bill Cc: pgsql-performance@postgresql.org Subject: Re: Query performance References: In-Reply-To: Content-Type: text/plain; charset=us-ascii; format=flowed Content-Transfer-Encoding: 7bit X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.0 tagged_above=0.0 required=5.0 tests= X-Spam-Level: X-Archive-Number: 200406/274 X-Sequence-Number: 7332 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 From bill@math.uchicago.edu Mon Jun 28 17:02:44 2004 X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id 44399D1B1A9 for ; Mon, 28 Jun 2004 14:02:44 -0300 (ADT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 85498-05 for ; Mon, 28 Jun 2004 17:02:39 +0000 (GMT) Received: from math.uchicago.edu (math.uchicago.edu [128.135.72.38]) by svr1.postgresql.org (Postfix) with ESMTP id 6ABCBD1B174 for ; Mon, 28 Jun 2004 14:02:36 -0300 (ADT) Received: from billnotebook (wireless-199-208.uchicago.edu [128.135.199.208]) by math.uchicago.edu (8.12.10/8.12.10) with ESMTP id i5SH2YfL013269 for ; Mon, 28 Jun 2004 12:02:36 -0500 Message-Id: <200406281702.i5SH2YfL013269@math.uchicago.edu> From: "Bill" To: Subject: Re: Query performance Date: Mon, 28 Jun 2004 12:02:40 -0500 MIME-Version: 1.0 Content-Type: text/plain; charset="us-ascii" Content-Transfer-Encoding: 7bit X-Mailer: Microsoft Office Outlook, Build 11.0.5510 In-Reply-To: <40DFE163.8000107@archonet.com> X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2800.1409 Thread-Index: AcRc8SMpS+jFDE1lQ1KaBvEuUe1v6AAQEUUg X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.0 tagged_above=0.0 required=5.0 tests= X-Spam-Level: X-Archive-Number: 200406/276 X-Sequence-Number: 7334 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 From dev@archonet.com Tue Jun 29 08:37:59 2004 X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id EEC9FD1B211 for ; Tue, 29 Jun 2004 05:37:59 -0300 (ADT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 02892-04 for ; Tue, 29 Jun 2004 08:37:53 +0000 (GMT) Received: from anchor-post-30.mail.demon.net (anchor-post-30.mail.demon.net [194.217.242.88]) by svr1.postgresql.org (Postfix) with ESMTP id CB929D1B258 for ; Tue, 29 Jun 2004 05:37:50 -0300 (ADT) Received: from mwynhau.demon.co.uk ([193.237.186.96] helo=mainbox.archonet.com) by anchor-post-30.mail.demon.net with esmtp (Exim 3.35 #1) id 1BfE7j-000PTe-0U; Tue, 29 Jun 2004 09:37:51 +0100 Received: from [192.168.1.17] (client17.archonet.com [192.168.1.17]) by mainbox.archonet.com (Postfix) with ESMTP id 09E5316C60; Tue, 29 Jun 2004 09:37:50 +0100 (BST) Message-ID: <40E12A5D.70007@archonet.com> Date: Tue, 29 Jun 2004 09:37:49 +0100 From: Richard Huxton User-Agent: Mozilla Thunderbird 0.7 (X11/20040615) X-Accept-Language: en-us, en MIME-Version: 1.0 To: Bill Cc: pgsql-performance@postgresql.org Subject: Re: Query performance References: <200406281702.i5SH2YfL013269@math.uchicago.edu> In-Reply-To: <200406281702.i5SH2YfL013269@math.uchicago.edu> Content-Type: text/plain; charset=us-ascii; format=flowed Content-Transfer-Encoding: 7bit X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.0 tagged_above=0.0 required=5.0 tests= X-Spam-Level: X-Archive-Number: 200406/287 X-Sequence-Number: 7345 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 From bill@math.uchicago.edu Tue Jun 29 17:33:54 2004 X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id 64188D1B1B4 for ; Tue, 29 Jun 2004 14:33:54 -0300 (ADT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 09655-04 for ; Tue, 29 Jun 2004 17:33:48 +0000 (GMT) Received: from math.uchicago.edu (math.uchicago.edu [128.135.72.38]) by svr1.postgresql.org (Postfix) with ESMTP id 33A6DD1B1AA for ; Tue, 29 Jun 2004 14:33:46 -0300 (ADT) Received: from billnotebook (wireless-197-223.uchicago.edu [128.135.197.223]) by math.uchicago.edu (8.12.10/8.12.10) with ESMTP id i5THXkfL031311 for ; Tue, 29 Jun 2004 12:33:46 -0500 Message-Id: <200406291733.i5THXkfL031311@math.uchicago.edu> From: "Bill" To: Subject: Re: Query performance Date: Tue, 29 Jun 2004 12:33:51 -0500 MIME-Version: 1.0 Content-Type: text/plain; charset="us-ascii" Content-Transfer-Encoding: 7bit X-Mailer: Microsoft Office Outlook, Build 11.0.5510 X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2800.1409 In-Reply-To: <40E12A5D.70007@archonet.com> Thread-Index: AcRdtTtifCTAR38tQAerxJIonFzvKQASJpwg X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.0 tagged_above=0.0 required=5.0 tests= X-Spam-Level: X-Archive-Number: 200406/311 X-Sequence-Number: 7369 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 From dev@archonet.com Tue Jun 29 19:03:37 2004 X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id 9E252D1B182 for ; Tue, 29 Jun 2004 16:03:37 -0300 (ADT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 55866-03 for ; Tue, 29 Jun 2004 19:03:30 +0000 (GMT) Received: from anchor-post-37.mail.demon.net (anchor-post-36.mail.demon.net [194.217.242.86]) by svr1.postgresql.org (Postfix) with ESMTP id 643CDD1B258 for ; Tue, 29 Jun 2004 16:03:27 -0300 (ADT) Received: from mwynhau.demon.co.uk ([193.237.186.96] helo=mainbox.archonet.com) by anchor-post-37.mail.demon.net with esmtp (Exim 3.35 #1) id 1BfNt9-0007ZU-0b; Tue, 29 Jun 2004 20:03:27 +0100 Received: from [192.168.1.17] (client17.archonet.com [192.168.1.17]) by mainbox.archonet.com (Postfix) with ESMTP id C4ABB16520; Tue, 29 Jun 2004 20:03:26 +0100 (BST) Message-ID: <40E1BCFD.9090401@archonet.com> Date: Tue, 29 Jun 2004 20:03:25 +0100 From: Richard Huxton User-Agent: Mozilla Thunderbird 0.7 (X11/20040615) X-Accept-Language: en-us, en MIME-Version: 1.0 To: Bill Cc: pgsql-performance@postgresql.org Subject: Re: Query performance References: <200406291733.i5THXkfL031311@math.uchicago.edu> In-Reply-To: <200406291733.i5THXkfL031311@math.uchicago.edu> Content-Type: text/plain; charset=us-ascii; format=flowed Content-Transfer-Encoding: 7bit X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.0 tagged_above=0.0 required=5.0 tests= X-Spam-Level: X-Archive-Number: 200406/312 X-Sequence-Number: 7370 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 From bruno@wolff.to Tue Jun 29 19:45:02 2004 X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id 5C07ED1B1D2 for ; Tue, 29 Jun 2004 16:45:02 -0300 (ADT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 77015-05 for ; Tue, 29 Jun 2004 19:44:57 +0000 (GMT) Received: from wolff.to (wolff.to [66.93.249.74]) by svr1.postgresql.org (Postfix) with SMTP id D04C5D1B179 for ; Tue, 29 Jun 2004 16:44:54 -0300 (ADT) Received: (qmail 1689 invoked by uid 500); 29 Jun 2004 19:51:30 -0000 Date: Tue, 29 Jun 2004 14:51:30 -0500 From: Bruno Wolff III To: Bill Cc: pgsql-performance@postgresql.org Subject: Re: Query performance Message-ID: <20040629195130.GA1513@wolff.to> Mail-Followup-To: Bill , pgsql-performance@postgresql.org References: <40E12A5D.70007@archonet.com> <200406291733.i5THXkfL031311@math.uchicago.edu> Mime-Version: 1.0 Content-Type: text/plain; charset=us-ascii Content-Disposition: inline In-Reply-To: <200406291733.i5THXkfL031311@math.uchicago.edu> User-Agent: Mutt/1.5.6i X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.0 tagged_above=0.0 required=5.0 tests= X-Spam-Level: X-Archive-Number: 200406/314 X-Sequence-Number: 7372 On Tue, Jun 29, 2004 at 12:33:51 -0500, 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? 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. From bill@math.uchicago.edu Wed Jun 30 13:46:57 2004 X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id 930D6D1B184 for ; Wed, 30 Jun 2004 10:46:57 -0300 (ADT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 71125-05 for ; Wed, 30 Jun 2004 13:46:56 +0000 (GMT) Received: from math.uchicago.edu (math.uchicago.edu [128.135.72.38]) by svr1.postgresql.org (Postfix) with ESMTP id 7611FD1B172 for ; Wed, 30 Jun 2004 10:46:51 -0300 (ADT) Received: from billnotebook (wireless-197-223.uchicago.edu [128.135.197.223]) by math.uchicago.edu (8.12.10/8.12.10) with ESMTP id i5UDktfL013134 for ; Wed, 30 Jun 2004 08:46:55 -0500 Message-Id: <200406301346.i5UDktfL013134@math.uchicago.edu> From: "Bill" To: Subject: Re: Query performance Date: Wed, 30 Jun 2004 08:47:03 -0500 MIME-Version: 1.0 Content-Type: text/plain; charset="us-ascii" Content-Transfer-Encoding: 7bit X-Mailer: Microsoft Office Outlook, Build 11.0.5510 In-Reply-To: <20040629195130.GA1513@wolff.to> X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2800.1409 Thread-Index: AcReEY+bwr8Aj6a5SVG7hH5fF/GZ0QAlvmYg X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.0 tagged_above=0.0 required=5.0 tests= X-Spam-Level: X-Archive-Number: 200406/322 X-Sequence-Number: 7380 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 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. From pg@rbt.ca Wed Jun 30 14:27:30 2004 X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id 7F6CED1B26E for ; Wed, 30 Jun 2004 11:27:30 -0300 (ADT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 95765-02 for ; Wed, 30 Jun 2004 14:27:28 +0000 (GMT) Received: from tht.net (vista.tht.net [216.126.88.2]) by svr1.postgresql.org (Postfix) with ESMTP id CCF6DD1B252 for ; Wed, 30 Jun 2004 11:27:21 -0300 (ADT) Received: from [134.22.68.4] (dyn-68-4.tor.dsl.tht.net [134.22.68.4]) by tht.net (Postfix) with ESMTP id 0646076AAA; Wed, 30 Jun 2004 10:27:28 -0400 (EDT) Subject: Re: Query performance From: Rod Taylor To: Bill Cc: Postgresql Performance In-Reply-To: <200406301346.i5UDktfL013134@math.uchicago.edu> References: <200406301346.i5UDktfL013134@math.uchicago.edu> Content-Type: text/plain Message-Id: <1088605644.12340.10.camel@jester> Mime-Version: 1.0 X-Mailer: Ximian Evolution 1.4.6 Date: Wed, 30 Jun 2004 10:27:25 -0400 Content-Transfer-Encoding: 7bit X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.0 tagged_above=0.0 required=5.0 tests= X-Spam-Level: X-Archive-Number: 200406/323 X-Sequence-Number: 7381 > 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. From dmota@nexen.com.br Fri Aug 20 23:59:04 2004 X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id 1F4725E40BB for ; Fri, 20 Aug 2004 20:59:04 -0300 (ADT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 39943-06 for ; Fri, 20 Aug 2004 23:59:00 +0000 (GMT) Received: from hm171.locaweb.com.br (hm171.locaweb.com.br [200.234.203.7]) by svr1.postgresql.org (Postfix) with SMTP id D08705E46D1 for ; Fri, 20 Aug 2004 20:58:56 -0300 (ADT) Received: (qmail 66197 invoked by uid 0); 20 Aug 2004 23:58:30 -0000 Received: from unknown (10.1.10.141) by hm171.locaweb.com.br with QMTP; 20 Aug 2004 23:58:30 -0000 Received: (qmail 11196 invoked from network); 20 Aug 2004 23:58:56 -0000 Received: from unknown (HELO xws08) (dmota@nexen.com.br@201.9.66.167) by hm141.locaweb.com.br with SMTP; 20 Aug 2004 23:58:56 -0000 Reply-To: From: "Danilo Mota" To: Subject: Query Performance Date: Fri, 20 Aug 2004 21:03:54 -0300 Organization: Nexen Tecnologia Message-ID: <000001c48712$599737a0$8afea8c0@nexen.lan> MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_NextPart_000_0001_01C486F9.3449FFA0" X-Priority: 3 (Normal) X-MSMail-Priority: Normal X-Mailer: Microsoft Outlook, Build 10.0.2616 Importance: Normal X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2800.1441 X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.1 tagged_above=0.0 required=5.0 tests=HTML_70_80, HTML_MESSAGE X-Spam-Level: X-Archive-Number: 200408/310 X-Sequence-Number: 7946 This is a multi-part message in MIME format. ------=_NextPart_000_0001_01C486F9.3449FFA0 Content-Type: text/plain; charset="us-ascii" Content-Transfer-Encoding: 7bit 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) ------=_NextPart_000_0001_01C486F9.3449FFA0 Content-Type: text/html; charset="us-ascii" Content-Transfer-Encoding: quoted-printable

Hi all,=

<= /font>

th= e 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 <= span class=3DSpellE>Mota

=3D=3D=3D=3D=3D=3D=3D=3D= =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D= =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D= =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D= =3D

SELECT

sn.notafiscalnumero,

sn.notafiscalserie,

CASE = sn.notafiscaldata WHEN '00000000' THEN NULL ELSE to_date(sn.notafiscaldata,'YYYYMMDD') END,<= /font>

sn.mo= dalidade,

rcm.p= kclientemarca,

sn.no= tafiscalvalor/100,

sn.entrada/100,

sn.cliente

FROM r_clientemarca AS= rcm

INNER JOIN = r_cliente AS rc ON rc.pkcliente =3D rcm.fk= cliente

INNER JOIN sav_cliente_lg AS sc ON sc.cpfcnpj =3D rc.cpfcnpj=

INNER JOIN sav_nota_lg AS sn ON sn.cliente = =3D sc.codigo

WHERE rcm.fkmarca =3D 1

AND sn.notafiscalnumero||sn.notafiscalserie||sn.cliente N= OT IN ( SELECT numero||serie||codigo

= &nb= sp; = &nb= sp; = = &nb= sp; FROM r_contrato AS rcon

= &nb= sp; = &nb= sp; = = &nb= sp; WHERE savfonte =3D 'lg')

<= /font>

= &nb= sp; = &nb= sp; TABLES

-------------------------= ---------------------------------------------------------------------------= -------------------------

r_cliente: 75820 records<= o:p>

r_clientemarca: 97719 rec= ords

r_contrato: 782058 record= s

sav_cliente_lg: 65671 rec= ords

sav_nota_lg: 297329 rcord= s

= &nb= sp; = &nb= sp; MY SERVER

--------------------------------------------------------= ---------------------------------------------------------------------<= /o:p>

Pentium 4 2.4 GHz

1 GB RAM

36 GB SCSI

Postgresql 7.4.2

<= /font>

= &nb= sp; = &nb= sp; POSTGRESQL.CONF

--------------------------------------------------------= ---------------------------------------------------------------------<= /o:p>

shared_buffers = =3D 7800

sort_mem<= font size=3D2 face=3DArial> = =3D 4096

checkpoint_segments = =3D 5

effective_cache_size = =3D 12000

cpu_operator_cost = =3D 0.0015

stats_start_collector = =3D false

= &nb= sp; = &nb= sp; QUERY PLAN

--------------------------------------------------------= ---------------------------------------------------------------------<= /o:p>

Hash Join (co= st=3D27149.61..3090289650.24 rows=3D128765 width=3D4)

Has= h Cond: ("outer".cliente= =3D "inner".codigo)=

-> Seq Scan on sav_nota_lg sn (cost=3D0.00..3090258517.99 rows=3D148665 width=3D8)

Filter: (NOT (subplan))

SubPlan

= -> Seq Scan on r_contrato rcon (cost=3D0.00..20362.47 rows=3D282845 width=3D19)

= Filter: ((savfonte):= :text =3D 'lg'::text)

-> Hash (cost=3D26869.29..26869.29 rows=3D= 56880 width=3D4)

-> Hash Join (cost=3D22473.95..26869.= 29 rows=3D56880 width=3D4)

= Hash Cond: ("o= uter".fkcliente =3D "inner".pkcliente)

= -> Index Scan using ix_r_clientemarca_fkmar= ca on r_clientemarca rcm (cost=3D0.00..2244.46 rows=3D65665= width=3D4)

= Index Cond: (fkmarc= a =3D 1)

= -> Hash (cost=3D22118.44..22118.44 rows=3D= 65672 width=3D8)

= -> Hash Join (cost=3D6613.22..22118.44 rows=3D65672 width=3D8)

= &nb= sp; Hash Cond: (("= outer".cpfcnpj)::text =3D ("inner".cpfc= npj)::text)

= &nb= sp; -> Seq Scan on r_cliente rc (cost=3D0.00..12891.16 rows=3D75816 width=3D23)

= &nb= sp; -> Hash (cost=3D6129.71..6129.71 rows=3D65= 671 width=3D23)

= &nb= sp; -> Seq Scan on sav_cliente_l= g sc (cost=3D0.00..6129.71 rows= =3D65671 width=3D23)

------=_NextPart_000_0001_01C486F9.3449FFA0-- From brad@madfish.com Sat Aug 21 00:59:51 2004 X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id 671155E46BF for ; Fri, 20 Aug 2004 21:59:51 -0300 (ADT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 55195-01 for ; Sat, 21 Aug 2004 00:59:49 +0000 (GMT) Received: from bugge.potatoe.com (bugge.potatoe.com [216.240.48.15]) by svr1.postgresql.org (Postfix) with ESMTP id C639C5E37CB for ; Fri, 20 Aug 2004 21:59:46 -0300 (ADT) Received: from [127.0.0.1] (localhost [127.0.0.1]) by bugge.potatoe.com (Postfix) with ESMTP id 59151728DE5; Fri, 20 Aug 2004 18:02:49 -0700 (PDT) Message-ID: <41269F38.5090305@madfish.com> Date: Fri, 20 Aug 2004 18:02:48 -0700 From: Brad Bulger Organization: The Madfish Group User-Agent: Mozilla Thunderbird 0.7+ (Macintosh/20040715) X-Accept-Language: en-us, en MIME-Version: 1.0 To: dmota@nexen.com.br Cc: pgsql-performance@postgresql.org Subject: Re: Query Performance References: <000001c48712$599737a0$8afea8c0@nexen.lan> In-Reply-To: <000001c48712$599737a0$8afea8c0@nexen.lan> Content-Type: text/plain; charset=windows-1252; format=flowed Content-Transfer-Encoding: 8bit X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.0 tagged_above=0.0 required=5.0 tests= X-Spam-Level: X-Archive-Number: 200408/311 X-Sequence-Number: 7947 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) > > > > > From loquin@talleyds.com Fri Mar 11 18:48:29 2005 X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id D542B5651C for ; Fri, 11 Mar 2005 18:48:29 +0000 (GMT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 34045-07 for ; Fri, 11 Mar 2005 18:48:20 +0000 (GMT) Received: from mesagate.talleyds.com (unknown [207.114.138.130]) by svr1.postgresql.org (Postfix) with ESMTP id 35C0F563DE for ; Fri, 11 Mar 2005 18:48:18 +0000 (GMT) Received: from TDS-MTA by mesagate.talleyds.com with Novell_GroupWise; Fri, 11 Mar 2005 11:48:19 -0700 Message-Id: X-Mailer: Novell GroupWise Internet Agent 6.5.4 Date: Fri, 11 Mar 2005 11:47:54 -0700 From: "Lou O'Quin" To: Subject: Query performance Mime-Version: 1.0 Content-Type: multipart/alternative; boundary="=__Part361548CA.0__=" X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.505 tagged_above=0 required=5 tests=HTML_20_30, HTML_MESSAGE X-Spam-Level: X-Archive-Number: 200503/191 X-Sequence-Number: 10896 --=__Part361548CA.0__= Content-Type: text/plain; charset=US-ASCII Content-Transfer-Encoding: quoted-printable 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.=20 The times for the query to run and the data to return for each of the = three=20 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? --=__Part361548CA.0__= Content-Type: text/html; charset=ISO-8859-1 Content-Transfer-Encoding: quoted-printable Content-Description: HTML 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:=20

Local Server : 571+521 ms=20
Local network: 1187+1266 ms=20
Internet:14579+4016 ms

My 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 (!!!)

Obviousl= y, the transport times will be greater. But why does the execution = time bloat so? --=__Part361548CA.0__=-- From tgl@sss.pgh.pa.us Fri Mar 11 19:10:36 2005 X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id 73021563E7 for ; Fri, 11 Mar 2005 19:10:36 +0000 (GMT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 39460-05 for ; Fri, 11 Mar 2005 19:10:28 +0000 (GMT) Received: from sss.pgh.pa.us (sss.pgh.pa.us [66.207.139.130]) by svr1.postgresql.org (Postfix) with ESMTP id B7BFA55119 for ; Fri, 11 Mar 2005 19:10:27 +0000 (GMT) Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1]) by sss.pgh.pa.us (8.13.1/8.13.1) with ESMTP id j2BJARqu004476; Fri, 11 Mar 2005 14:10:27 -0500 (EST) To: "Lou O'Quin" Cc: pgsql-performance@postgresql.org Subject: Re: Query performance In-reply-to: References: Comments: In-reply-to "Lou O'Quin" message dated "Fri, 11 Mar 2005 11:47:54 -0700" Date: Fri, 11 Mar 2005 14:10:27 -0500 Message-ID: <4475.1110568227@sss.pgh.pa.us> From: Tom Lane X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.007 tagged_above=0 required=5 tests=AWL X-Spam-Level: X-Archive-Number: 200503/192 X-Sequence-Number: 10897 "Lou O'Quin" 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 From loquin@talleyds.com Fri Mar 11 19:38:41 2005 X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id AC4A35644B for ; Fri, 11 Mar 2005 19:38:41 +0000 (GMT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 45702-10 for ; Fri, 11 Mar 2005 19:38:32 +0000 (GMT) Received: from mesagate.talleyds.com (unknown [207.114.138.130]) by svr1.postgresql.org (Postfix) with ESMTP id 9F53754469 for ; Fri, 11 Mar 2005 19:38:30 +0000 (GMT) Received: from TDS-MTA by mesagate.talleyds.com with Novell_GroupWise; Fri, 11 Mar 2005 12:38:31 -0700 Message-Id: X-Mailer: Novell GroupWise Internet Agent 6.5.4 Date: Fri, 11 Mar 2005 12:38:16 -0700 From: "Lou O'Quin" To: Cc: Subject: Re: Query performance Mime-Version: 1.0 Content-Type: multipart/alternative; boundary="=__Part50732EB8.0__=" X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.281 tagged_above=0 required=5 tests=AWL, HTML_30_40, HTML_MESSAGE X-Spam-Level: X-Archive-Number: 200503/193 X-Sequence-Number: 10898 --=__Part50732EB8.0__= Content-Type: text/plain; charset=US-ASCII Content-Transfer-Encoding: quoted-printable Hi Tom. I referenced the status line of pgAdmin. Per the pgAdmin help = file: =20 "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." =20 Lou >>> Tom Lane 3/11/2005 12:10 PM >>> "Lou O'Quin" writes: > it appears to actually be hypersensitive to the transport delay. The =3D > ratios of time for the data transport (assuming 1 for the local server) = =3D > 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 --=__Part50732EB8.0__= Content-Type: text/html; charset=ISO-8859-1 Content-Transfer-Encoding: quoted-printable Content-Description: HTML

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 =3D
> ratios of time for the data transport (assuming 1 = for the local server) =3D
> 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

--=__Part50732EB8.0__=-- From tgl@sss.pgh.pa.us Fri Mar 11 20:21:21 2005 X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id ABAF7565E1 for ; Fri, 11 Mar 2005 20:21:21 +0000 (GMT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 58703-08 for ; Fri, 11 Mar 2005 20:21:12 +0000 (GMT) Received: from sss.pgh.pa.us (sss.pgh.pa.us [66.207.139.130]) by svr1.postgresql.org (Postfix) with ESMTP id CA160565AC for ; Fri, 11 Mar 2005 20:21:11 +0000 (GMT) Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1]) by sss.pgh.pa.us (8.13.1/8.13.1) with ESMTP id j2BKLCav004961; Fri, 11 Mar 2005 15:21:12 -0500 (EST) To: "Lou O'Quin" Cc: pgsql-performance@postgresql.org Subject: Re: Query performance In-reply-to: References: Comments: In-reply-to "Lou O'Quin" message dated "Fri, 11 Mar 2005 12:38:16 -0700" Date: Fri, 11 Mar 2005 15:21:12 -0500 Message-ID: <4960.1110572472@sss.pgh.pa.us> From: Tom Lane X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.007 tagged_above=0 required=5 tests=AWL X-Spam-Level: X-Archive-Number: 200503/195 X-Sequence-Number: 10900 "Lou O'Quin" 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 From loquin@talleyds.com Fri Mar 11 20:35:32 2005 X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id 2E31056616 for ; Fri, 11 Mar 2005 20:35:32 +0000 (GMT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 63693-04 for ; Fri, 11 Mar 2005 20:35:22 +0000 (GMT) Received: from mesagate.talleyds.com (unknown [207.114.138.130]) by svr1.postgresql.org (Postfix) with ESMTP id 460A85661A for ; Fri, 11 Mar 2005 20:35:20 +0000 (GMT) Received: from TDS-MTA by mesagate.talleyds.com with Novell_GroupWise; Fri, 11 Mar 2005 13:35:22 -0700 Message-Id: X-Mailer: Novell GroupWise Internet Agent 6.5.4 Date: Fri, 11 Mar 2005 13:35:03 -0700 From: "Lou O'Quin" To: Cc: Subject: Re: Query performance Mime-Version: 1.0 Content-Type: multipart/alternative; boundary="=__PartFFDC82E7.0__=" X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.384 tagged_above=0 required=5 tests=AWL, HTML_20_30, HTML_MESSAGE X-Spam-Level: X-Archive-Number: 200503/197 X-Sequence-Number: 10902 --=__PartFFDC82E7.0__= Content-Type: text/plain; charset=US-ASCII Content-Transfer-Encoding: quoted-printable I'll post there concerning how they determine the query execution time vs. = data retrieval time. =20 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 =20 Lou >>> Tom Lane 3/11/2005 1:21 PM >>> "Lou O'Quin" 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 --=__PartFFDC82E7.0__= Content-Type: text/html; charset=ISO-8859-1 Content-Transfer-Encoding: quoted-printable Content-Description: HTML

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
&n= bsp; joining column's datatypes do not match

--=__PartFFDC82E7.0__=-- From sgunderson@bigfoot.com Mon May 22 23:33:44 2006 X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (mx1.hub.org [200.46.208.251]) by postgresql.org (Postfix) with ESMTP id 5792D9FA2D8 for ; Mon, 22 May 2006 20:33:44 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.208.251]) (amavisd-new, port 10024) with ESMTP id 52623-08 for ; Mon, 22 May 2006 20:33:35 -0300 (ADT) X-Greylist: from auto-whitelisted by SQLgrey- Received: from floppy.pyrenet.fr (news.pyrenet.fr [194.116.145.2]) by postgresql.org (Postfix) with ESMTP id 7AB139F9B14 for ; Mon, 22 May 2006 20:33:35 -0300 (ADT) Received: by floppy.pyrenet.fr (Postfix, from userid 106) id 2453831932; Tue, 23 May 2006 01:33:34 +0200 (MET DST) From: "Steinar H. Gunderson" X-Newsgroups: comp.databases.postgresql,pgsql.performance Subject: Re: Query performance Date: Mon, 22 May 2006 23:33:33 +0000 (UTC) Organization: /etc/news/organization Lines: 20 Message-ID: References: X-Complaints-To: usenet@itea.ntnu.no User-Agent: slrn/0.9.8.1pl1 (Debian) To: pgsql-performance@postgresql.org X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200605/427 X-Sequence-Number: 19214 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/ From antonio.batovanja@humanomed.co.at Tue May 23 07:11:11 2006 X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (mx1.hub.org [200.46.208.251]) by postgresql.org (Postfix) with ESMTP id D84F89F9EEB for ; Tue, 23 May 2006 04:11:11 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.208.251]) (amavisd-new, port 10024) with ESMTP id 17638-02 for ; Tue, 23 May 2006 04:11:06 -0300 (ADT) X-Greylist: from auto-whitelisted by SQLgrey- Received: from floppy.pyrenet.fr (floppy.pyrenet.fr [194.116.145.2]) by postgresql.org (Postfix) with ESMTP id AB70D9F9CAA for ; Tue, 23 May 2006 04:11:05 -0300 (ADT) Received: by floppy.pyrenet.fr (Postfix, from userid 106) id 1603731932; Tue, 23 May 2006 09:11:04 +0200 (MET DST) From: Antonio Batovanja X-Newsgroups: comp.databases.postgresql,pgsql.performance Subject: Re: Query performance Followup-To: comp.databases.postgresql Date: Tue, 23 May 2006 09:10:29 +0200 Organization: Humanomed Lines: 29 Message-ID: References: Mime-Version: 1.0 Content-Type: text/plain; charset=us-ascii Content-Transfer-Encoding: 7Bit X-Complaints-To: abuse@uta.at User-Agent: KNode/0.9.2 To: pgsql-performance@postgresql.org X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200605/538 X-Sequence-Number: 19325 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 From brsaweda@gmail.com Sun May 28 21:56:42 2006 X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (mx1.hub.org [200.46.208.251]) by postgresql.org (Postfix) with ESMTP id 503ED9FA5D8 for ; Sun, 28 May 2006 18:56:42 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.208.251]) (amavisd-new, port 10024) with ESMTP id 76349-03 for ; Sun, 28 May 2006 18:56:31 -0300 (ADT) X-Greylist: from auto-whitelisted by SQLgrey- Received: from floppy.pyrenet.fr (floppy.pyrenet.fr [194.116.145.2]) by postgresql.org (Postfix) with ESMTP id 1A6059F9F3D for ; Sun, 28 May 2006 18:56:31 -0300 (ADT) Received: by floppy.pyrenet.fr (Postfix, from userid 106) id 9826831963; Sun, 28 May 2006 23:56:29 +0200 (MET DST) Date: Sun, 28 May 2006 23:56:27 +0200 From: Erwin Brandstetter User-Agent: Thunderbird 1.5.0.2 (Windows/20060308) MIME-Version: 1.0 X-Newsgroups: comp.databases.postgresql,pgsql.performance Subject: Re: Query performance References: <1148294589.715695@proxy.dienste.wien.at> In-Reply-To: Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit Message-ID: X-Complaints-To: abuse@chello.at Organization: chello.at Lines: 30 To: pgsql-performance@postgresql.org X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200605/547 X-Sequence-Number: 19334 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 From brsaweda@gmail.com Sun May 28 22:39:05 2006 X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (mx1.hub.org [200.46.208.251]) by postgresql.org (Postfix) with ESMTP id 9A7909FA48C for ; Sun, 28 May 2006 19:39:05 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.208.251]) (amavisd-new, port 10024) with ESMTP id 81906-09 for ; Sun, 28 May 2006 19:38:58 -0300 (ADT) X-Greylist: from auto-whitelisted by SQLgrey- Received: from floppy.pyrenet.fr (news.pyrenet.fr [194.116.145.2]) by postgresql.org (Postfix) with ESMTP id 624699FA131 for ; Sun, 28 May 2006 19:38:59 -0300 (ADT) Received: by floppy.pyrenet.fr (Postfix, from userid 106) id 73C8931963; Mon, 29 May 2006 00:38:57 +0200 (MET DST) Date: Mon, 29 May 2006 00:38:55 +0200 From: Erwin Brandstetter User-Agent: Thunderbird 1.5.0.2 (Windows/20060308) MIME-Version: 1.0 X-Newsgroups: comp.databases.postgresql,pgsql.performance Subject: Re: Query performance References: <1148294589.715695@proxy.dienste.wien.at> In-Reply-To: Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit Message-ID: X-Complaints-To: abuse@chello.at Organization: chello.at Lines: 65 To: pgsql-performance@postgresql.org X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200605/546 X-Sequence-Number: 19333 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 From chris.kings-lynne@calorieking.com Wed May 31 01:36:04 2006 X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (mx1.hub.org [200.46.208.251]) by postgresql.org (Postfix) with ESMTP id A5F099FA5F0 for ; Tue, 30 May 2006 22:36:04 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.208.251]) (amavisd-new, port 10024) with ESMTP id 45145-09 for ; Tue, 30 May 2006 22:35:58 -0300 (ADT) X-Greylist: delayed 00:31:42.956827 by SQLgrey- Received: from houston.familyhealth.com.au (iihouston.familyhealth.com.au [203.59.102.239]) by postgresql.org (Postfix) with ESMTP id DD8709FA5E1 for ; Tue, 30 May 2006 22:35:58 -0300 (ADT) Received: from houston.familyhealth.com.au (localhost [127.0.0.1]) by houston.familyhealth.com.au (Postfix) with ESMTP id D0DF62573F; Wed, 31 May 2006 09:04:11 +0800 (WST) Received: from [127.0.0.1] (work-48.internal [192.168.0.48]) by houston.familyhealth.com.au (Postfix) with ESMTP id 4354C2573B; Wed, 31 May 2006 09:04:09 +0800 (WST) Message-ID: <447CEC56.3070106@calorieking.com> Date: Wed, 31 May 2006 09:07:34 +0800 From: Christopher Kings-Lynne User-Agent: Thunderbird 1.5.0.2 (Windows/20060308) MIME-Version: 1.0 To: Antonio Batovanja Cc: pgsql-performance@postgresql.org Subject: Re: Query performance References: In-Reply-To: Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit X-familyhealth-MailScanner-Information: Please contact the ISP for more information X-familyhealth-MailScanner: Found to be clean X-familyhealth-MailScanner-From: chris.kings-lynne@calorieking.com X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200605/560 X-Sequence-Number: 19347 > 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 From kpmanojpg@gmail.com Thu Jun 13 07:56:48 2013 Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtp (Exim 4.80) (envelope-from ) id 1Un2Om-0001yH-9r for pgsql-performance@arkaria.postgresql.org; Thu, 13 Jun 2013 07:56:48 +0000 Received: from localhost ([127.0.0.1] helo=postgresql.org) by malur.postgresql.org with smtp (Exim 4.72) (envelope-from ) id 1Un2Ol-0007oP-L1 for pgsql-performance@arkaria.postgresql.org; Thu, 13 Jun 2013 07:56:47 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtp (Exim 4.72) (envelope-from ) id 1Un2Ok-0007oK-Gj for pgsql-performance@postgresql.org; Thu, 13 Jun 2013 07:56:46 +0000 Received: from mail-pb0-x244.google.com ([2607:f8b0:400e:c01::244]) by magus.postgresql.org with esmtp (Exim 4.80) (envelope-from ) id 1Un2Og-0002Sp-OD for pgsql-performance@postgresql.org; Thu, 13 Jun 2013 07:56:45 +0000 Received: by mail-pb0-f68.google.com with SMTP id jt11so5795339pbb.3 for ; Thu, 13 Jun 2013 00:56:41 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20120113; h=mime-version:date:message-id:subject:from:to:content-type; bh=ULCCvAjXRMDsOHgyrjy07li/TKL/F7O3y75R1lXjYiI=; b=QECDOqXDIw3EBMkNGDJFejnr0QsDXaMtosgwCro5WprpHr4nSSBekrV9fpYTzSbREQ PS3HvJaiM79DqhD+zZ68vzM6h0HNTuuGDUCXo9gMxpYJB4FLXp8KIfZ65qDOhDrQd/fa OuoNpw+ahsX1CRcOgW30/NGsMPM4zQxOrjzh4HSAq0z9ZJ0NWs0gWBcs2xb9Q40xn1G0 zQQDDwLf9VMDxrKHv0JVnPRjxcdh+PzGru+BjSvbQ4syjg7PMk6yUhch87MGcQmq90DP Uo/FLWXJX/SKGK+1H0CNt6CnVcaYRU56zSKlp75zxDAaW1VbMBxtnh9NdviU03F2egxo 8cVw== MIME-Version: 1.0 X-Received: by 10.68.200.133 with SMTP id js5mr13763431pbc.22.1371109782694; Thu, 13 Jun 2013 00:49:42 -0700 (PDT) Received: by 10.70.102.66 with HTTP; Thu, 13 Jun 2013 00:49:42 -0700 (PDT) Date: Thu, 13 Jun 2013 13:19:42 +0530 Message-ID: Subject: Query performance From: K P Manoj To: pgsql-performance@postgresql.org Content-Type: multipart/alternative; boundary=047d7b15a99f8bb1e104df045f25 X-Pg-Spam-Score: -2.0 (--) List-Archive: List-Help: List-ID: List-Owner: List-Post: List-Subscribe: List-Unsubscribe: X-Mailing-List: pgsql-performance Precedence: bulk Sender: pgsql-performance-owner@postgresql.org --047d7b15a99f8bb1e104df045f25 Content-Type: text/plain; charset=ISO-8859-1 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) --047d7b15a99f8bb1e104df045f25 Content-Type: text/html; charset=ISO-8859-1 Content-Transfer-Encoding: quoted-printable

Hi All

One of my query treat= ing performance issue on my production server.

Once i run =A0quer= y on my parent table with specific condition(hard coded value) its uses =A0= only proper child table and its index on explain plan =A0, but once i am us= ing table conditions (instead of hard coded value), query planner is going = all the child tables, Can i know where i am worng=A0

Postgresql version 9.2.2=A0

Pl= ease find details below =A0

=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D= =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D

XXX_db= =3D> select id from xxx where d_id =3D '5';

=A0id=A0

-------

=A0 =A0 =A05

=A0 =A0 45=

(2 rows)

XXX_db=3D> e= xplain =A0analyze =A0SELECT * =A0FROM xxx_parent_table WHERE id in (5,45) a= nd ( sts =3D 1 or status is null ) order by creation_time limit 40 ;

=A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 = =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 QUERY PLAN =A0 =A0 = =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0= =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0

=A0

----------= ---------------------------------------------------------------------------= -------------------------------------------------

=A0=A0

=A0Limit =A0(cost=3D12.21..12.21 rows=3D3= width=3D251) (actual time=3D6.585..6.585 rows=3D0 loops=3D1)

=A0= =A0-> =A0Sort =A0(cost=3D12.21..12.21 rows=3D3 width=3D251) (actual tim= e=3D6.582..6.582 rows=3D0 loops=3D1)

=A0 =A0 =A0 =A0 =A0Sort Key: public.xxx_parent_tables.creation_time

=A0 =A0 =A0 =A0 =A0Sort Method: quicksort =A0Memory: 25kB

=A0 =A0 =A0 =A0 =A0-> =A0Result =A0(cost=3D0.00..12.18 rows=3D3 width= =3D251) (actual time=3D6.571..6.571 rows=3D0 loops=3D1)

=A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0-> =A0Append =A0(cost=3D0.00..12.18 = rows=3D3 width=3D251) (actual time=3D6.569..6.569 rows=3D0 loops=3D1)

=A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0-> =A0Seq Scan on xxx_pa= rent_tables =A0(cost=3D0.00..0.00 rows=3D1 width=3D324) (actual time=3D0.00= 3..0.003 rows=3D0 loops=3D1)

=A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0Filter: ((id = =3D ANY ('{5,45}'::bigint[])) AND ((status =3D 1) OR (status IS NUL= L)))

=A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0-> =A0Bitmap H= eap Scan on xxx_parent_tables_table_details_ xxx_parent_tables =A0(cost=3D4= .52..6.53 rows=3D1 width=3D105) (actual ti

me=3D0.063..0.063 rows=3D0 loops=3D1)

=A0 =A0 =A0 =A0 =A0 = =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0Recheck Cond: ((status =3D 1) OR (status= IS NULL))

=A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 = =A0Filter: (id =3D ANY ('{5,45}'::bigint[]))

=A0 =A0 =A0 = =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0-> =A0BitmapOr =A0(cost=3D4.5= 2..4.52 rows=3D1 width=3D0) (actual time=3D0.059..0.059 rows=3D0 loops=3D1)=

=A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0-&g= t; =A0Bitmap Index Scan on xxx_parent_tables_table_details__status_idx =A0(= cost=3D0.00..2.26 rows=3D1 width=3D0)

=A0(actual time=3D0.038..0.= 038 rows=3D0 loops=3D1)

=A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 = =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0Index Cond: (status =3D 1)

=A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0-&g= t; =A0Bitmap Index Scan on xxx_parent_tables_table_details__status_idx =A0(= cost=3D0.00..2.26 rows=3D1 width=3D0)

=A0(actual time=3D0.019..0.= 019 rows=3D0 loops=3D1)

=A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 = =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0Index Cond: (status IS NULL)

=A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0-> =A0Bitmap Heap Scan o= n xxx_parent_tables_table_details_det xxx_parent_tables =A0(cost=3D2.52..5.= 65 rows=3D1 width=3D324) (actual ti

me=3D6.502..6.502 rows=3D0 lo= ops=3D1)

=A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0R= echeck Cond: (id =3D ANY ('{5,45}'::bigint[]))

=A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0Filter: ((statu= s =3D 1) OR (status IS NULL))

=A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0= =A0 =A0 =A0 =A0 =A0-> =A0Bitmap Index Scan on xxx_parent_tables_table_d= etails_id_idx =A0(cost=3D0.00..2.52 rows=3D2 width=3D0) (actua

l time=3D6.499..6.499 rows=3D0 loops=3D1)

=A0 =A0 =A0 =A0 = =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0Index Cond: (id =3D ANY = ('{5,45}'::bigint[]))

=A0Total runtime: 6.823 ms

(22 rows)

XXX_db =3D> explain analyze =A0 =A0SELECT * =A0FROM xxx_parent_tabl= es WHERE cp_id in (select id from xxx where d_id =3D '5') and ( sta= tus =3D 1 or status is null ) order by creation_time limit 40 ;

= =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0= =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 = =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 QUE= RY PLAN =A0 =A0 =A0 =A0 =A0 =A0 =A0

=A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 = =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0= =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0=A0

-------------= ---------------------------------------------------------------------------= ----------------------------------------------

----------------------------------------------------------------------= --------------------------

=A0Limit =A0(cost=3D3.66..6067.89 rows= =3D40 width=3D105) (actual time=3D70479.596..70479.596 rows=3D0 loops=3D1)<= /div>

=A0 =A0-> =A0Nested Loop Semi Join =A0(cost=3D3.66..4587291.92= rows=3D30258 width=3D105) (actual time=3D70479.593..70479.593 rows=3D0 loo= ps=3D1)

=A0 =A0 =A0 =A0 =A0Join Filter: (public.xxx_parent_tables.cp_id =3D cp= _info.cp_id)

=A0 =A0 =A0 =A0 =A0Rows Removed by Join Filter: 1416= 520

=A0 =A0 =A0 =A0 =A0-> =A0Merge Append =A0(cost=3D3.66..456= 5956.68 rows=3D711059 width=3D105) (actual time=3D67225.964..69635.016 rows= =3D708260 loops=3D1)

=A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0Sort Key: public.xxx_parent_tables.crea= tion_time

=A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0-> =A0Sort =A0(cost= =3D0.01..0.02 rows=3D1 width=3D324) (actual time=3D0.018..0.018 rows=3D0 lo= ops=3D1)

=A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0Sort Key: pub= lic.xxx_parent_tables.creation_time

=A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0Sort Method: quicksort =A0M= emory: 25kB

=A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0-> =A0S= eq Scan on xxx_parent_tables =A0(cost=3D0.00..0.00 rows=3D1 width=3D324) (a= ctual time=3D0.011..0.011 rows=3D0 loops=3D1)

=A0 =A0 =A0 =A0 =A0= =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0Filter: ((status =3D 1) OR (status IS N= ULL))

=A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0-> =A0Index Scan using xxx_parent_ta= bles_automobiles_carwale_creation_time_idx on xxx_parent_tables_automobiles= _carwale xxx_parent_tables =A0(co

st=3D0.00..649960.44 rows=3D17 = width=3D105) (actual time=3D10219.559..10219.559 rows=3D0 loops=3D1)

=A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0Filter: ((status =3D 1) OR = (status IS NULL))

=A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0Rows= Removed by Filter: 3102241

=A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0-> = =A0Index Scan using xxx_parent_tables_automobiles_sulekha_creation_time_idx= on xxx_parent_tables_automobiles_sulekha xxx_parent_tables =A0(co

st=3D0.00..1124998.57 rows=3D1 width=3D105) (actual time=3D17817.577..= 17817.577 rows=3D0 loops=3D1)

=A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0= =A0 =A0Filter: ((status =3D 1) OR (status IS NULL))

=A0 =A0 =A0 = =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0Rows Removed by Filter: 4016234

=A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0-> =A0Index Scan using xxx_parent_ta= bles_automobiles_verse_creation_time_idx on xxx_parent_tables_automobiles_v= erse xxx_parent_tables =A0(cost=3D0

.00..24068.88 rows=3D1 width= =3D103) (actual time=3D675.291..675.291 rows=3D0 loops=3D1)

=A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0Filter: ((status =3D 1) OR = (status IS NULL))

=A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0Rows= Removed by Filter: 420616

=A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0-> = =A0Index Scan using xxx_parent_tables_automobiles_yolist_creation_time_idx = on xxx_parent_tables_automobiles_yolist xxx_parent_tables =A0(cost

=3D0.00..25.05 rows=3D2 width=3D324) (actual time=3D0.016..0.016 rows= =3D0 loops=3D1)

=A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0Filter= : ((status =3D 1) OR (status IS NULL))

=A0 =A0 =A0 =A0 =A0 =A0 = =A0 =A0-> =A0Index Scan using xxx_parent_tables_deals_bagittoday_creatio= n_time_idx on xxx_parent_tables_deals_bagittoday xxx_parent_tables =A0(cost= =3D0.0

0..23882.78 rows=3D1 width=3D105) (actual time=3D234.672..234.672 rows= =3D0 loops=3D1)

=A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0Filter= : ((status =3D 1) OR (status IS NULL))

=A0 =A0 =A0 =A0 =A0 =A0 = =A0 =A0 =A0 =A0 =A0Rows Removed by Filter: 84988

=A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0-> =A0Index Scan using xxx_parent_ta= bles_deals_bindaasbargain_creation_time_idx on xxx_parent_tables_deals_bind= aasbargain xxx_parent_tables =A0(

cost=3D0.00..25.05 rows=3D2 wid= th=3D324) (actual time=3D0.016..0.016 rows=3D0 loops=3D1)

=A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0Filter: ((status =3D 1) OR = (status IS NULL))

=A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0-> =A0Index S= can using xxx_parent_tables_deals_buzzr_creation_time_idx on xxx_parent_tab= les_deals_buzzr xxx_parent_tables =A0(cost=3D0.00..11435.4

1 rows=3D1 width=3D105) (actual time=3D109.466..109.466 rows=3D0 loops= =3D1)

=A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0Filter: ((status= =3D 1) OR (status IS NULL))

=A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 = =A0 =A0Rows Removed by Filter: 33750

=A0 =A0 =A0 =A0 =A0 =A0 =A0 = =A0-> =A0Index Scan using xxx_parent_tables_deals_dealdrums_creation_tim= e_idx on xxx_parent_tables_deals_dealdrums xxx_parent_tables =A0(cost=3D0.0= 0.

.51.61 rows=3D1 width=3D105) (actual time=3D0.917..0.917 rows=3D0 loop= s=3D1)

=A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0Filter: ((statu= s =3D 1) OR (status IS NULL))

=A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0= =A0 =A0Rows Removed by Filter: 941

=A0 =A0 =A0 =A0 =A0 =A0 =A0 = =A0-> =A0Index Scan using xxx_parent_tables_deals_dealsandyou_creation_t= ime_idx on xxx_parent_tables_deals_dealsandyou xxx_parent_tables =A0(cost= =3D0

.00..25.05 rows=3D2 width=3D324) (actual time=3D0.012..0.012 rows=3D0 = loops=3D1)

=A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0Filter: ((s= tatus =3D 1) OR (status IS NULL))

=A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0= -> =A0Index Scan using xxx_parent_tables_deals_foodiebay_creation_time_i= dx on xxx_parent_tables_deals_foodiebay xxx_parent_tables =A0(cost=3D0.00.<= /div>

.25.05 rows=3D2 width=3D324) (actual time=3D0.024..0.024 rows=3D0 loop= s=3D1)

=A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0Filter: ((statu= s =3D 1) OR (status IS NULL))

=A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0->= ; =A0Index Scan using xxx_parent_tables_deals_futurebazaar_creation_time_id= x on xxx_parent_tables_deals_futurebazaar xxx_parent_tables =A0(cost

=3D0.00..30.37 rows=3D1 width=3D109) (actual time=3D0.348..0.348 rows= =3D0 loops=3D1)

=A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0Filter= : ((status =3D 1) OR (status IS NULL))

=A0 =A0

=A0 =A0 = =A0 =A0 =A0 =A0 =A0 =A0-> =A0Index Scan using xxx_parent_tables_jobs_job= sa1_creation_time_idx on xxx_parent_tables_jobs_jobsa1 xxx_parent_tables = =A0(cost=3D0.00..25.05 r

ows=3D2 width=3D324) (actual time=3D0.020..0.020 rows=3D0 loops=3D1)

=A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0Filter: ((status =3D 1)= OR (status IS NULL))

=A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0-> =A0Ind= ex Scan using xxx_parent_tables_jobs_jobsinnigeria_creation_time_idx on xxx= _parent_tables_jobs_jobsinnigeria xxx_parent_tables =A0(cost

=3D0.00..25.05 rows=3D2 width=3D324) (actual time=3D0.013..0.013 rows= =3D0 loops=3D1)

=A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0Filter= : ((status =3D 1) OR (status IS NULL))

=A0 =A0 =A0 =A0 =A0 =A0 = =A0 =A0-> =A0Index Scan using xxx_parent_tables_jobs_khojle_creation_tim= e_idx on xxx_parent_tables_jobs_khojle xxx_parent_tables =A0(cost=3D0.00..2= 5.05 r

ows=3D2 width=3D324) (actual time=3D0.013..0.013 rows=3D0 loops=3D1)

=A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0Filter: ((status =3D 1)= OR (status IS NULL))

=A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0-> =A0Ind= ex Scan using xxx_parent_tables_jobs_midday_creation_time_idx on xxx_parent= _tables_jobs_midday xxx_parent_tables =A0(cost=3D0.00..25.05 r

ows=3D2 width=3D324) (actual time=3D0.011..0.011 rows=3D0 loops=3D1)

=A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0Filter: ((status =3D 1)= OR (status IS NULL))

=A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0-> =A0Ind= ex Scan using xxx_parent_tables_jobs_monsterindia_creation_time_idx on xxx_= parent_tables_jobs_monsterindia xxx_parent_tables =A0(cost=3D0

.00..31569.68 rows=3D81849 width=3D105) (actual time=3D279.393..544.46= 7 rows=3D78622 loops=3D1)

=A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0= =A0Filter: ((status =3D 1) OR (status IS NULL))

=A0 =A0 =A0 =A0 = =A0 =A0 =A0 =A0 =A0 =A0 =A0Rows Removed by Filter: 155151

=A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0-> =A0Index Scan using xxx_parent_ta= bles_jobs_mprc_creation_time_idx on xxx_parent_tables_jobs_mprc xxx_parent_= tables =A0(cost=3D0.00..25.05 rows=3D

2 width=3D324) (actual time= =3D0.016..0.016 rows=3D0 loops=3D1)

=A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0Filter: ((status =3D 1) OR = (status IS NULL))

=A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0-> =A0Index S= can using xxx_parent_tables_jobs_myjobsintanzania_creation_time_idx on xxx_= parent_tables_jobs_myjobsintanzania xxx_parent_tables=A0

=A0(cost=3D0.00..25.05 rows=3D2 width=3D324) (actual time=3D0.012..0.0= 12 rows=3D0 loops=3D1)

=A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 = =A0Filter: ((status =3D 1) OR (status IS NULL))

=A0 =A0 =A0 =A0 = =A0 =A0 =A0 =A0 =A0 =A0 =A0 -> =A0Index Scan using xxx_parent_tables_mob= iles_verse_creation_time_idx on xxx_parent_tables_mobiles_verse xxx_parent_= tables =A0(cost=3D0.00..25.

05 rows=3D2 width=3D324) (actual time=3D0.015..0.015 rows=3D0 loops=3D= 1)

=A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0Filter: ((status = =3D 1) OR (status IS NULL))

=A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0-> = =A0Index Scan using xxx_parent_tables_mobileseeker_quikr_creation_time_idx = on xxx_parent_tables_mobileseeker_quikr xxx_parent_tables =A0(cost

=3D0.00..13.30 rows=3D1 width=3D105) (actual time=3D0.111..0.111 rows= =3D0 loops=3D1)

=A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0Filter= : ((status =3D 1) OR (status IS NULL))

=A0 =A0 =A0 =A0 =A0 =A0 = =A0 =A0 =A0 =A0 =A0Rows Removed by Filter: 61

=A0 =A0=A0

=A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0Filter: ((status =3D 1) OR = (status IS NULL))

=A0 =A0 =A0 =A0 =A0-> =A0Materialize =A0(cos= t=3D0.00..3.47 rows=3D2 width=3D8) (actual time=3D0.000..0.000 rows=3D2 loo= ps=3D708260)

=A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0-> =A0Seq Scan on = cp_info =A0(cost=3D0.00..3.46 rows=3D2 width=3D8) (actual time=3D0.028..0.0= 60 rows=3D2 loops=3D1)

=A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0Filter: (domain_id =3D 5::b= igint)

=A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0Rows Removed by= Filter: 115

=A0Total runtime: 70481.560 ms

(xxx rows)<= /div>

--047d7b15a99f8bb1e104df045f25-- From gray.ru@gmail.com Thu Jun 13 09:04:20 2013 Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtp (Exim 4.80) (envelope-from ) id 1Un3S8-0005lC-2n for pgsql-performance@arkaria.postgresql.org; Thu, 13 Jun 2013 09:04:20 +0000 Received: from localhost ([127.0.0.1] helo=postgresql.org) by malur.postgresql.org with smtp (Exim 4.72) (envelope-from ) id 1Un3S7-0005yE-GG for pgsql-performance@arkaria.postgresql.org; Thu, 13 Jun 2013 09:04:19 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtp (Exim 4.72) (envelope-from ) id 1Un3S6-0005y7-DK for pgsql-performance@postgresql.org; Thu, 13 Jun 2013 09:04:18 +0000 Received: from mail-pd0-f179.google.com ([209.85.192.179]) by magus.postgresql.org with esmtp (Exim 4.80) (envelope-from ) id 1Un3Rt-0003ce-C4 for pgsql-performance@postgresql.org; Thu, 13 Jun 2013 09:04:17 +0000 Received: by mail-pd0-f179.google.com with SMTP id q10so5161331pdj.38 for ; Thu, 13 Jun 2013 02:04:03 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20120113; h=mime-version:in-reply-to:references:from:date:message-id:subject:to :cc:content-type; bh=mLWhVj2tKPpFnxN7WDn5TjkKrFLwrD07MZ+9YY7ryYo=; b=zqCWCLDyuSA+YxMpX41xlnq8EOeVo5T6uQTqe+LTRbyHGk0wo6hbvwmVeyUfZMISqf ZijS6AsG9b7kunm9xqNoPk4okq3mtUcUQy8aTzZ0OFyj+3N95MriBFKZo7tlkhVKuj61 Wh6d8OH+l8n+h/bj5YoRCg0OQTqD+L1m75glMkkGbua5VIl+wyDc64h2PjPL+u2utZHI DBu/w/+LP8MiWiUHmzMbB6ufsWmjdxkXiEjqsbDfqEeXnX+bt4w/lSKERvl/M4TL+f4o nWjlolKUJmam01aIJ5W7NV2nktPQcgvzFHUyBKqomP8UlHCI45hS3EWUZvUiiPEQnNyy JBfw== X-Received: by 10.68.226.39 with SMTP id rp7mr23520597pbc.97.1371114243317; Thu, 13 Jun 2013 02:04:03 -0700 (PDT) MIME-Version: 1.0 Received: by 10.68.80.234 with HTTP; Thu, 13 Jun 2013 02:03:43 -0700 (PDT) In-Reply-To: References: From: Sergey Konoplev Date: Thu, 13 Jun 2013 02:03:43 -0700 Message-ID: Subject: Re: Query performance To: K P Manoj Cc: "pgsql-performance@postgresql.org" Content-Type: text/plain; charset=ISO-8859-1 X-Pg-Spam-Score: -2.0 (--) List-Archive: List-Help: List-ID: List-Owner: List-Post: List-Subscribe: List-Unsubscribe: X-Mailing-List: pgsql-performance Precedence: bulk Sender: pgsql-performance-owner@postgresql.org On Thu, Jun 13, 2013 at 12:49 AM, K P Manoj 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 From joe@tanga.com Sun Jan 25 05:42:05 2015 Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtp (Exim 4.80) (envelope-from ) id 1YFFxV-0001DG-L1 for pgsql-performance@arkaria.postgresql.org; Sun, 25 Jan 2015 05:42:05 +0000 Received: from localhost ([127.0.0.1] helo=postgresql.org) by malur.postgresql.org with smtp (Exim 4.80) (envelope-from ) id 1YFFxV-0000Fj-5O for pgsql-performance@arkaria.postgresql.org; Sun, 25 Jan 2015 05:42:05 +0000 Received: from makus.postgresql.org ([2001:4800:1501:1::229]) by malur.postgresql.org with esmtps (TLS1.2:DHE_RSA_AES_256_CBC_SHA256:256) (Exim 4.80) (envelope-from ) id 1YFFxT-0000Fc-LP for pgsql-performance@postgresql.org; Sun, 25 Jan 2015 05:42:04 +0000 Received: from mail-qa0-x230.google.com ([2607:f8b0:400d:c00::230]) by makus.postgresql.org with esmtps (TLS1.0:RSA_AES_256_CBC_SHA1:256) (Exim 4.80) (envelope-from ) id 1YFFxL-0007P8-8i for pgsql-performance@postgresql.org; Sun, 25 Jan 2015 05:42:01 +0000 Received: by mail-qa0-f48.google.com with SMTP id v8so3176026qal.7 for ; Sat, 24 Jan 2015 21:41:53 -0800 (PST) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20120113; h=mime-version:sender:from:date:message-id:subject:to:content-type; bh=eOCcjrkK+2LLP7/aJ5Jw7yoSRBfC7mr5nAEw99f33gE=; b=CJn2QCxPkMLfK+3DymE+c3sIyXEgghYK5iCqvpGN3rgMc5QoHnaE9YjJbUb8JOMf90 3VugGS1enFPfCyBcS0S8XNovA5o8xTAz4B0uYG3gJ3jBSQPfvnz7wucSqhMuY9Myn8IY 2eT4snZ6RHa7WHsQ1YLAqqqVJI2V4Cy6xfiryOjvep7t3/Rq02/1UQd0O7CYhzdiI2eY 9Zq/UQ9Yj5t0Wkykqw+cdudlELPNCC9/z8G0T+6tF2RTsAhmPCDeb4Onnd6/qvu7y0w/ mcGE3M1osasv+/Z3RN85Ki9FgFoA2L/mFJYd9BV9Tro0Zc1ifBxX6nfWWMCB5a7G3LiL JkrQ== DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=tanga.com; s=google; h=mime-version:sender:from:date:message-id:subject:to:content-type; bh=eOCcjrkK+2LLP7/aJ5Jw7yoSRBfC7mr5nAEw99f33gE=; b=NPO6vxlGQ1K5lUcm8IO8LLwFc+5pqwJot625ZIJAXCa9FQ/kK4He+lxRO/GPtzcT36 BFy9cgjBorWgoSCDmUFDmQeDqi1/De3Nu/GL++lId/jy85UnbIiEqLCHOcbQCwGbnwrD uOdU/uj3tLuQhkqTsj9r9cYLUb3+4oziGuK6k= X-Received: by 10.140.49.5 with SMTP id p5mr28923638qga.15.1422164513886; Sat, 24 Jan 2015 21:41:53 -0800 (PST) MIME-Version: 1.0 Received: by 10.140.240.23 with HTTP; Sat, 24 Jan 2015 21:41:13 -0800 (PST) From: Joe Van Dyk Date: Sat, 24 Jan 2015 21:41:13 -0800 X-Google-Sender-Auth: _S9Jwjs4PG9yRYa38WphCfjG-0c Message-ID: Subject: Query performance To: "pgsql-performance@postgresql.org" Content-Type: multipart/alternative; boundary=001a11351944a9b855050d737a64 X-Pg-Spam-Score: -2.7 (--) List-Archive: List-Help: List-ID: List-Owner: List-Post: List-Subscribe: List-Unsubscribe: X-Mailing-List: pgsql-performance Precedence: bulk Sender: pgsql-performance-owner@postgresql.org --001a11351944a9b855050d737a64 Content-Type: text/plain; charset=UTF-8 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/9940bbe033ebd56d38caa46e33c1ddfd9df36eda/gistfile1.txt 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) --001a11351944a9b855050d737a64 Content-Type: text/html; charset=UTF-8 Content-Transfer-Encoding: quoted-printable

I have an events table that records page views and pu= rchases (type =3D 'viewed' or type=3D'purchased'). I have a= query that figures out "people who bought/viewed this also bought/vie= wed that".

It worked fine, taking about 0.1 s= econds to complete, until a few hours ago when it started taking hours to c= omplete. Vacuum/analyze didn't help.=C2=A0 Turned out there was one ses= sion_id that had 400k rows in the system. Deleting that made the query perf= ormant again.=C2=A0

Is there anything I can do to = make the query work better in cases like that? Missing index, or better que= ry?

This is on 9.3.5.

The belo= w is reproduced at the following URL if it's not formatted correctly in= the email. h= ttps://gist.githubusercontent.com/joevandyk/cb8f4afdb6c1b178c606/raw/9940bb= e033ebd56d38caa46e33c1ddfd9df36eda/gistfile1.txt

explain select e1.product_id, e2.site_id, e2.product_id, count(nullif(e2.type=3D'viewed', false)) view_count, count(nullif(e2.type=3D'purchased', false)) purchase_count from events e1 join events e2 on e1.session_id =3D e2.session_id and e1.type =3D e2.type where e1.product_id =3D '82503' and e1.product_id !=3D e2.product_id group by e1.product_id, e2.product_id, e2.site_id; QUERY PLAN ---------------------------------------------------------------------------= ------------------------------------------------- GroupAggregate (cost=3D828395.67..945838.90 rows=3D22110 width=3D19) -> Sort (cost=3D828395.67..840117.89 rows=3D4688885 width=3D19) Sort Key: e1.product_id, e2.product_id, e2.site_id -> Nested Loop (cost=3D11.85..20371.14 rows=3D4688885 width= =3D19) -> Bitmap Heap Scan on events e1 (cost=3D11.29..1404.31= rows=3D369 width=3D49) Recheck Cond: (product_id =3D '82503'::citext) -> Bitmap Index Scan on events_product_id_site_id_= idx (cost=3D0.00..11.20 rows=3D369 width=3D0) Index Cond: (product_id =3D '82503'::cit= ext) -> Index Scan using events_session_id_type_product_id_id= x on events e2 (cost=3D0.56..51.28 rows=3D12 width=3D51) Index Cond: ((session_id =3D e1.session_id) AND (type = =3D e1.type)) Filter: (e1.product_id <> product_id) (11 rows) recommender_production=3D> \d events Table "public.events" Column | Type | Modifiers -------------+--------------------------+----------------------------------= ------------------- id | bigint | not null default nextval('eve= nts_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, ty= pe, product_id) Check constraints: "events_session_id_check" CHECK (length(session_id::text) <= ; 255) "events_type_check" CHECK (type =3D ANY (ARRAY['purchased= '::text, 'viewed'::text])) "events_user_id_check" CHECK (length(user_id::text) < 255)=

--001a11351944a9b855050d737a64-- From joe@tanga.com Sun Jan 25 05:44:03 2015 Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtp (Exim 4.80) (envelope-from ) id 1YFFzP-0001FO-SO for pgsql-performance@arkaria.postgresql.org; Sun, 25 Jan 2015 05:44:03 +0000 Received: from localhost ([127.0.0.1] helo=postgresql.org) by malur.postgresql.org with smtp (Exim 4.80) (envelope-from ) id 1YFFzP-0001rk-9t for pgsql-performance@arkaria.postgresql.org; Sun, 25 Jan 2015 05:44:03 +0000 Received: from makus.postgresql.org ([2001:4800:1501:1::229]) by malur.postgresql.org with esmtps (TLS1.2:DHE_RSA_AES_256_CBC_SHA256:256) (Exim 4.80) (envelope-from ) id 1YFFzN-0001p3-Ha for pgsql-performance@postgresql.org; Sun, 25 Jan 2015 05:44:01 +0000 Received: from mail-qg0-x235.google.com ([2607:f8b0:400d:c04::235]) by makus.postgresql.org with esmtps (TLS1.0:RSA_AES_256_CBC_SHA1:256) (Exim 4.80) (envelope-from ) id 1YFFzJ-0007Pg-VJ for pgsql-performance@postgresql.org; Sun, 25 Jan 2015 05:43:59 +0000 Received: by mail-qg0-f53.google.com with SMTP id a108so3181997qge.12 for ; Sat, 24 Jan 2015 21:43:57 -0800 (PST) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20120113; h=mime-version:sender:in-reply-to:references:from:date:message-id :subject:to:content-type; bh=LjtN/q+6NwVO7Xd+as4oal8yxCKwig2QAgxU8DjjwA4=; b=M0TBz5nyjdrGUXFATvVPBkU6f5doObVLijqknU5TLTbfON9c+T/DS20z7IG8wFrekd Bbej4ML8DxRnhHgc9apRc9MwzNYjCaWRef5OvNBjnG/u2oc0V/JzaP+FwgvPOG19NmBN QGXcduN9Xa0O4Ld4VWR5AmqpHLnTq28maRQbOoJg3KnmUdu3CV9vulYDO89lIC9N0K8W 339VM4mKnnz1WmB20mH0JR+6A6zFxf7O+ui2FqditpM4zVPFeLaYacVIG0ZP6jE8vJ8N 9+Bzf5eqsak8padzcIhIhXJbK+w2XAzBhzH+C4CIx3M2wyevjqSs3rF33Q41g7Q2zJ84 hZ4Q== DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=tanga.com; s=google; h=mime-version:sender:in-reply-to:references:from:date:message-id :subject:to:content-type; bh=LjtN/q+6NwVO7Xd+as4oal8yxCKwig2QAgxU8DjjwA4=; b=OOdHEWpFhC+fGjZv31nUIrUdqvvPQaMk9SCJP4GCzR2c8gBKnDMcu2yDNhL0XjpqV5 plsH+/JUENdGDFQUAja28DwJ1edi1hFXlaO4Qja7SOEACbtBgLsKjGbFm2HcVEe5zY3/ wHtq/NcILoqiUjnOqJ1wy7M9OkblhXFkFYPnk= X-Received: by 10.229.50.135 with SMTP id z7mr11984328qcf.21.1422164637320; Sat, 24 Jan 2015 21:43:57 -0800 (PST) MIME-Version: 1.0 Received: by 10.140.240.23 with HTTP; Sat, 24 Jan 2015 21:43:17 -0800 (PST) In-Reply-To: References: From: Joe Van Dyk Date: Sat, 24 Jan 2015 21:43:17 -0800 X-Google-Sender-Auth: gEkCs9DnGP9pLL7MkqrL3SdiJX4 Message-ID: Subject: Re: Query performance To: "pgsql-performance@postgresql.org" Content-Type: multipart/alternative; boundary=001a11341e5c050e00050d7382bb X-Pg-Spam-Score: -2.7 (--) List-Archive: List-Help: List-ID: List-Owner: List-Post: List-Subscribe: List-Unsubscribe: X-Mailing-List: pgsql-performance Precedence: bulk Sender: pgsql-performance-owner@postgresql.org --001a11341e5c050e00050d7382bb Content-Type: text/plain; charset=UTF-8 On Sat, Jan 24, 2015 at 9:41 PM, Joe Van Dyk 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/9940bbe033ebd56d38caa46e33c1ddfd9df36eda/gistfile1.txt > > 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 --001a11341e5c050e00050d7382bb Content-Type: text/html; charset=UTF-8 Content-Transfer-Encoding: quoted-printable

On S= at, Jan 24, 2015 at 9:41 PM, Joe Van Dyk <joe@tanga.com> wrote:<= br>

I have an events table that recor= ds page views and purchases (type =3D 'viewed' or type=3D'purch= ased'). 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 start= ed taking hours to complete. Vacuum/analyze didn't help.=C2=A0 Turned o= ut there was one session_id that had 400k rows in the system. Deleting that= made the query performant again.=C2=A0

Is there a= nything 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 fo= rmatted correctly in the email. https://gist.githubusercontent.com/joe= vandyk/cb8f4afdb6c1b178c606/raw/9940bbe033ebd56d38caa46e33c1ddfd9df36eda/gi= stfile1.txt

explain  select
   e1.product_id,
   e2.site_id,
   e2.product_id,
   count(nullif(e2.type=3D'viewed', false)) view_count,
   count(nullif(e2.type=3D'purchased', false)) purchase_count
 from events e1
 join events e2 on e1.session_id =3D e2.session_id and e1.type =3D e2.type
 where
   e1.product_id =3D '82503' and
   e1.product_id !=3D e2.product_id
 group by e1.product_id, e2.product_id, e2.site_id;
                                                         QUERY PLAN
---------------------------------------------------------------------------=
-------------------------------------------------
 GroupAggregate  (cost=3D828395.67..945838.90 rows=3D22110 width=3D19)
   ->  Sort  (cost=3D828395.67..840117.89 rows=3D4688885 width=3D19)
         Sort Key: e1.product_id, e2.product_id, e2.site_id
         ->  Nested Loop  (cost=3D11.85..20371.14 rows=3D4688885 width=
=3D19)
               ->  Bitmap Heap Scan on events e1  (cost=3D11.29..1404.31=
 rows=3D369 width=3D49)
                     Recheck Cond: (product_id =3D '82503'::citext)
                     ->  Bitmap Index Scan on events_product_id_site_id_=
idx  (cost=3D0.00..11.20 rows=3D369 width=3D0)
                           Index Cond: (product_id =3D '82503'::cit=
ext)
               ->  Index Scan using events_session_id_type_product_id_id=
x on events e2  (cost=3D0.56..51.28 rows=3D12 width=3D51)
                     Index Cond: ((session_id =3D e1.session_id) AND (type =
=3D e1.type))
                     Filter: (e1.product_id <> product_id)
(11 rows)

recommender_production=3D> \d events
                                    Table "public.events"
   Column    |           Type           |                      Modifiers
-------------+--------------------------+----------------------------------=
-------------------
 id          | bigint                   | not null default nextval('eve=
nts_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, ty=
pe, product_id)
Check constraints:
    "events_session_id_check" CHECK (length(session_id::text) <=
; 255)
    "events_type_check" CHECK (type =3D 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

--001a11341e5c050e00050d7382bb-- From joe@tanga.com Sun Jan 25 05:46:40 2015 Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtp (Exim 4.80) (envelope-from ) id 1YFG1w-0001MO-JS for pgsql-performance@arkaria.postgresql.org; Sun, 25 Jan 2015 05:46:40 +0000 Received: from localhost ([127.0.0.1] helo=postgresql.org) by malur.postgresql.org with smtp (Exim 4.80) (envelope-from ) id 1YFG1v-0003yh-Ig for pgsql-performance@arkaria.postgresql.org; Sun, 25 Jan 2015 05:46:39 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.2:DHE_RSA_AES_256_CBC_SHA256:256) (Exim 4.80) (envelope-from ) id 1YFG1u-0003yb-Op for pgsql-performance@postgresql.org; Sun, 25 Jan 2015 05:46:38 +0000 Received: from mail-qc0-x229.google.com ([2607:f8b0:400d:c01::229]) by magus.postgresql.org with esmtps (TLS1.0:RSA_AES_256_CBC_SHA1:256) (Exim 4.80) (envelope-from ) id 1YFG1p-0008Bu-W8 for pgsql-performance@postgresql.org; Sun, 25 Jan 2015 05:46:37 +0000 Received: by mail-qc0-f169.google.com with SMTP id b13so3272461qcw.0 for ; Sat, 24 Jan 2015 21:46:30 -0800 (PST) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20120113; h=mime-version:sender:in-reply-to:references:from:date:message-id :subject:to:content-type; bh=lbpoLMqvE97pWtrOT2zCtz4v270H6OyQBpnARHIasEQ=; b=bqaxa54yJjEYr6XhB/KnEDoY3NuX8J+U4yxwT+TRlt585rGd6IV8ZtcdMjqx6BB2dG pW1VqhUfRrPviJnmGZXzEHWin4srZ84HyDGsocSPYqAhfBQZFSeoInabxu78PqvhKFIm vXvLjJFqSC6eFAAM3x7A14czP0Tm/MS+gf8qEEoCyhiZZzTPzvMLgU8C0LQpjexHDm/t UhvyczDEU2Q58Ak8PRjdap7/7/Ljm4Af4nTQsiuKuM/tmXsJdJbIXptHgtAeaNRgS5pt KT0OpHLADGEE6hKqH+IEsPPFodXMhZI7fEQ9qytTITk7yafmb8ys3SOiDF7AVUltxIZ9 Pn7g== DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=tanga.com; s=google; h=mime-version:sender:in-reply-to:references:from:date:message-id :subject:to:content-type; bh=lbpoLMqvE97pWtrOT2zCtz4v270H6OyQBpnARHIasEQ=; b=jhn5Qoim404Zn0tRCjRzz6iZjyWwKE6jPDdGrBrbC8Tjc96CGsfrvLiLuLvC+HckWh lWYBcQGF7+y06RVuW3sBCU/qCDPHiFXU0yzF7Zg+Tz90IiLV4ebOrmaMvjMQYd2QQULK CKWbQBGD7QnCXUkjN8uIETdM5Wt1RG3or9ork= X-Received: by 10.224.75.71 with SMTP id x7mr4326489qaj.90.1422164790889; Sat, 24 Jan 2015 21:46:30 -0800 (PST) MIME-Version: 1.0 Received: by 10.140.240.23 with HTTP; Sat, 24 Jan 2015 21:45:50 -0800 (PST) In-Reply-To: References: From: Joe Van Dyk Date: Sat, 24 Jan 2015 21:45:50 -0800 X-Google-Sender-Auth: zIKNSA0p4P-U-4jmxh4vlYOJQrg Message-ID: Subject: Re: Query performance To: "pgsql-performance@postgresql.org" Content-Type: multipart/alternative; boundary=001a11c304b22c5412050d738b5d X-Pg-Spam-Score: -2.7 (--) List-Archive: List-Help: List-ID: List-Owner: List-Post: List-Subscribe: List-Unsubscribe: X-Mailing-List: pgsql-performance Precedence: bulk Sender: pgsql-performance-owner@postgresql.org --001a11c304b22c5412050d738b5d Content-Type: text/plain; charset=UTF-8 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 wrote: > On Sat, Jan 24, 2015 at 9:41 PM, Joe Van Dyk 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/9940bbe033ebd56d38caa46e33c1ddfd9df36eda/gistfile1.txt >> >> 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 > --001a11c304b22c5412050d738b5d Content-Type: text/html; charset=UTF-8 Content-Transfer-Encoding: quoted-printable

Oops, didn't run vacuum analyze after deleting the eve= nts. Here is another 'explain analyze':=C2=A0http://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 =3D= 'viewed' or type=3D'purchased'). I have a query that figur= es out "people who bought/viewed this also bought/viewed that".

It worked fine, taking about 0.1 seconds to complet= e, until a few hours ago when it started taking hours to complete. Vacuum/a= nalyze didn't help.=C2=A0 Turned out there was one session_id that had = 400k rows in the system. Deleting that made the query performant again.=C2= =A0

Is there anything I can do to make the query w= ork 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/9940= bbe033ebd56d38caa46e33c1ddfd9df36eda/gistfile1.txt

<= pre style=3D"color:rgb(0,0,0);word-wrap:break-word;white-space:pre-wrap">explain select e1.product_id, e2.site_id, e2.product_id, count(nullif(e2.type=3D'viewed', false)) view_count, count(nullif(e2.type=3D'purchased', false)) purchase_count from events e1 join events e2 on e1.session_id =3D e2.session_id and e1.type =3D e2.type where e1.product_id =3D '82503' and e1.product_id !=3D e2.product_id group by e1.product_id, e2.product_id, e2.site_id; QUERY PLAN ---------------------------------------------------------------------------= ------------------------------------------------- GroupAggregate (cost=3D828395.67..945838.90 rows=3D22110 width=3D19) -> Sort (cost=3D828395.67..840117.89 rows=3D4688885 width=3D19) Sort Key: e1.product_id, e2.product_id, e2.site_id -> Nested Loop (cost=3D11.85..20371.14 rows=3D4688885 width= =3D19) -> Bitmap Heap Scan on events e1 (cost=3D11.29..1404.31= rows=3D369 width=3D49) Recheck Cond: (product_id =3D '82503'::citext) -> Bitmap Index Scan on events_product_id_site_id_= idx (cost=3D0.00..11.20 rows=3D369 width=3D0) Index Cond: (product_id =3D '82503'::cit= ext) -> Index Scan using events_session_id_type_product_id_id= x on events e2 (cost=3D0.56..51.28 rows=3D12 width=3D51) Index Cond: ((session_id =3D e1.session_id) AND (type = =3D e1.type)) Filter: (e1.product_id <> product_id) (11 rows) recommender_production=3D> \d events Table "public.events" Column | Type | Modifiers -------------+--------------------------+----------------------------------= ------------------- id | bigint | not null default nextval('eve= nts_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, ty= pe, product_id) Check constraints: "events_session_id_check" CHECK (length(session_id::text) <= ; 255) "events_type_check" CHECK (type =3D ANY (ARRAY['purchased= '::text, 'viewed'::text])) "events_user_id_check" CHECK (length(user_id::text) < 255)=

After r= emoving the session with 400k events, I was able to do an explain analyze, = here is one of them:
http://explain.depesz.com/s/PFNk

--001a11c304b22c5412050d738b5d-- From pavel.stehule@gmail.com Sun Jan 25 06:13:00 2015 Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtp (Exim 4.80) (envelope-from ) id 1YFGRQ-0002Ed-LJ for pgsql-performance@arkaria.postgresql.org; Sun, 25 Jan 2015 06:13:00 +0000 Received: from localhost ([127.0.0.1] helo=postgresql.org) by malur.postgresql.org with smtp (Exim 4.80) (envelope-from ) id 1YFGRP-0006Ob-VS for pgsql-performance@arkaria.postgresql.org; Sun, 25 Jan 2015 06:13:00 +0000 Received: from makus.postgresql.org ([2001:4800:1501:1::229]) by malur.postgresql.org with esmtps (TLS1.2:DHE_RSA_AES_256_CBC_SHA256:256) (Exim 4.80) (envelope-from ) id 1YFGRN-0006OS-Be for pgsql-performance@postgresql.org; Sun, 25 Jan 2015 06:12:57 +0000 Received: from mail-pd0-x22f.google.com ([2607:f8b0:400e:c02::22f]) by makus.postgresql.org with esmtps (TLS1.0:RSA_AES_256_CBC_SHA1:256) (Exim 4.80) (envelope-from ) id 1YFGRJ-0007yi-J4 for pgsql-performance@postgresql.org; Sun, 25 Jan 2015 06:12:55 +0000 Received: by mail-pd0-f175.google.com with SMTP id fl12so5898623pdb.6 for ; Sat, 24 Jan 2015 22:12:52 -0800 (PST) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20120113; h=mime-version:in-reply-to:references:from:date:message-id:subject:to :cc:content-type; bh=PCcdj7dG7it6iPK+pnbMrDVFOSpMUDA43c0mx9Nb4Ho=; b=ETk0J3lOp6lzAVRy6RmAdXTcXcstFWMIorlyZz7stbeusVgMQ3IwjKEtmmZIXDZyML Z0qyPZhukLls4to32jhrA+3SrMQUREDMvVdsO2cOx73lUZYQ3v/8bAtPL8FFfxrXwV7U 0HjjYaagICg15nUAdGJkGD6NFJo/bDz/ZaCIEypk2OjYRQah+fuF1JtMUaZjXd0qlu+h sYa3l7KwIt9E8iKTO8MfoVWLnMVdxlsr/HR2o46Lb8r4M0cdM2/7X2GpuRyHnxry0m9l pKa8iCCA93Sy4HAZIR2vfKBoffPvoMFkcMBBQOWzRwB2/lGffYjQsVxtHeeJfJafPYgJ sRjQ== X-Received: by 10.66.100.226 with SMTP id fb2mr8097175pab.92.1422166372093; Sat, 24 Jan 2015 22:12:52 -0800 (PST) MIME-Version: 1.0 Received: by 10.70.120.70 with HTTP; Sat, 24 Jan 2015 22:12:11 -0800 (PST) In-Reply-To: References:

this plan looks well

Regards

Pavel

2015-01-25 6:45 GMT+01:00 Joe Van Dyk &l= t;joe@tanga.com><= /span>:

Oops, didn't= run vacuum analyze after deleting the events. Here is another 'explain= analyze':=C2=A0http://explain.depesz.com/s/AviN
On Sat, Jan 24, 2015 at 9:43 PM, Joe Van Dyk <joe@tanga.com> w= rote:
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 purchas= es (type =3D 'viewed' or type=3D'purchased'). I have a quer= y that figures out "people who bought/viewed this also bought/viewed t= hat".

It worked fine, taking about 0.1 second= s to complete, until a few hours ago when it started taking hours to comple= te. Vacuum/analyze didn't help.=C2=A0 Turned out there was one session_= id that had 400k rows in the system. Deleting that made the query performan= t again.=C2=A0
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/cb8f4afdb6c1b178c6= 06/raw/9940bbe033ebd56d38caa46e33c1ddfd9df36eda/gistfile1.txt
<= /div>
explain  select
   e1.product_id,
   e2.site_id,
   e2.product_id,
   count(nullif(e2.type=3D'viewed', false)) view_count,
   count(nullif(e2.type=3D'purchased', false)) purchase_count
 from events e1
 join events e2 on e1.session_id =3D e2.session_id and e1.type =3D e2.type
 where
   e1.product_id =3D '82503' and
   e1.product_id !=3D e2.product_id
 group by e1.product_id, e2.product_id, e2.site_id;
                                                         QUERY PLAN
---------------------------------------------------------------------------=
-------------------------------------------------
 GroupAggregate  (cost=3D828395.67..945838.90 rows=3D22110 width=3D19)
   ->  Sort  (cost=3D828395.67..840117.89 rows=3D4688885 width=3D19)
         Sort Key: e1.product_id, e2.product_id, e2.site_id
         ->  Nested Loop  (cost=3D11.85..20371.14 rows=3D4688885 width=
=3D19)
               ->  Bitmap Heap Scan on events e1  (cost=3D11.29..1404.31=
 rows=3D369 width=3D49)
                     Recheck Cond: (product_id =3D '82503'::citext)
                     ->  Bitmap Index Scan on events_product_id_site_id_=
idx  (cost=3D0.00..11.20 rows=3D369 width=3D0)
                           Index Cond: (product_id =3D '82503'::cit=
ext)
               ->  Index Scan using events_session_id_type_product_id_id=
x on events e2  (cost=3D0.56..51.28 rows=3D12 width=3D51)
                     Index Cond: ((session_id =3D e1.session_id) AND (type =
=3D e1.type))
                     Filter: (e1.product_id <> product_id)
(11 rows)

recommender_production=3D> \d events
                                    Table "public.events"
   Column    |           Type           |                      Modifiers
-------------+--------------------------+----------------------------------=
-------------------
 id          | bigint                   | not null default nextval('eve=
nts_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, ty=
pe, product_id)
Check constraints:
    "events_session_id_check" CHECK (length(session_id::text) <=
; 255)
    "events_type_check" CHECK (type =3D ANY (ARRAY['purchased=
'::text, 'viewed'::text]))
    "events_user_id_check" CHECK (length(user_id::text) < 255)=
After r= emoving the session with 400k events, I was able to do an explain analyze, = here is one of them:
http://explain.depesz.com/s/PFNk

--047d7bd908906b94bb050d73e911-- From joe@tanga.com Sun Jan 25 06:38:55 2015 Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtp (Exim 4.80) (envelope-from ) id 1YFGqV-0003BF-0M for pgsql-performance@arkaria.postgresql.org; Sun, 25 Jan 2015 06:38:55 +0000 Received: from localhost ([127.0.0.1] helo=postgresql.org) by malur.postgresql.org with smtp (Exim 4.80) (envelope-from ) id 1YFGqT-0000XC-Ot for pgsql-performance@arkaria.postgresql.org; Sun, 25 Jan 2015 06:38:53 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.2:DHE_RSA_AES_256_CBC_SHA256:256) (Exim 4.80) (envelope-from ) id 1YFGqS-0000X3-7B for pgsql-performance@postgresql.org; Sun, 25 Jan 2015 06:38:52 +0000 Received: from mail-qc0-x236.google.com ([2607:f8b0:400d:c01::236]) by magus.postgresql.org with esmtps (TLS1.0:RSA_AES_256_CBC_SHA1:256) (Exim 4.80) (envelope-from ) id 1YFGqM-0000od-Q6 for pgsql-performance@postgresql.org; Sun, 25 Jan 2015 06:38:50 +0000 Received: by mail-qc0-f182.google.com with SMTP id l6so3296573qcy.13 for ; Sat, 24 Jan 2015 22:38:44 -0800 (PST) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20120113; h=mime-version:sender:in-reply-to:references:from:date:message-id :subject:to:cc:content-type; bh=8LmV39FrV35dXzmqUO6hbO0yo2ThyV+OpEcrIe34JVI=; b=pjhztXJPhYeKlYmunZM1tJNllLySYrgPwNu8nKa2xoJwywxrMyLzojrqzVK5TJB/L0 fXEBgvEZmfhcqNDem2R/RmeaaZk91nIH2S8+tDoBs8w6euqZERFfOsV/T7F9b+hIDt7K 6XTTZD5CnRsZfnfkfeMUOAzzLdpNzrlfuYTi4GmuGab+KLHUsRTJHU8GjOcopRNPwS/G AZ8Fxgcps6B0qrAE1eK+gJ6KSMQBGY1zDX1w6UhYUEi1ROEjXNntGfUUIjMN87nWZ8T0 5FsQp1w+SyZGVs8nimbHHmscYuJgivhaKfwdkx2/EWCUvgaRfDYYyWVHApO5vnQM2uzM 42Uw== DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=tanga.com; s=google; h=mime-version:sender:in-reply-to:references:from:date:message-id :subject:to:cc:content-type; bh=8LmV39FrV35dXzmqUO6hbO0yo2ThyV+OpEcrIe34JVI=; b=PByNZHNhw+OOSzjXqrqxL6IKTZFh9lSSlIBbOVzDAW7npeRQgyeGMm8/yhva1aApZZ VhxLnTMQVXGQAmMyeaSgd0EV4RPt7XMt8YZwXBvWDDAkEvOZDrISkLMjdXzXO9q8a78d dkk0mcKu61tWh67/FScXcgkBTgnEsHX9maX7I= X-Received: by 10.140.19.139 with SMTP id 11mr29767697qgh.14.1422167924634; Sat, 24 Jan 2015 22:38:44 -0800 (PST) MIME-Version: 1.0 Received: by 10.140.240.23 with HTTP; Sat, 24 Jan 2015 22:38:04 -0800 (PST) In-Reply-To: References:

On Sat, Jan 24, 2015 at 10:12 PM, Pavel Stehule <<= a href=3D"mailto:pavel.stehule@gmail.com" target=3D"_blank">pavel.stehule@g= mail.com> wrote:

=
Hi

this plan looks well

Regards

Pavel
Here's one that's not quite as well: http://explain.depesz.com/s/SgT<= /div>

Joe
=C2=A0
2015-01-25 6:45 GMT+01:00 Joe Van Dyk <joe@tanga.com>:
Oops, didn't run vacuum analyz= e after deleting the events. Here is another 'explain analyze':=C2= =A0http://ex= plain.depesz.com/s/AviN
<= br>
On Sat, Jan 24, 2015 at 9:43 PM, Joe Van Dyk = <jo= e@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 vie= ws and purchases (type =3D 'viewed' or type=3D'purchased').= I have a query that figures out "people who bought/viewed this also b= ought/viewed that".

It worked fine, taking ab= out 0.1 seconds to complete, until a few hours ago when it started taking h= ours to complete. Vacuum/analyze didn't help.=C2=A0 Turned out there wa= s one session_id that had 400k rows in the system. Deleting that made the q= uery performant again.=C2=A0

Is there anything I c= an do to make the query work better in cases like that? Missing index, or b= etter query?

This is on 9.3.5.

The below is reproduced at the following URL if it's not formatted cor= rectly in the email. https://gist.githubusercontent.com/joevandyk/cb8f= 4afdb6c1b178c606/raw/9940bbe033ebd56d38caa46e33c1ddfd9df36eda/gistfile1.txt=

explain select e1.product_id, e2.site_id, e2.product_id, count(nullif(e2.type=3D'viewed', false)) view_count, count(nullif(e2.type=3D'purchased', false)) purchase_count from events e1 join events e2 on e1.session_id =3D e2.session_id and e1.type =3D e2.type where e1.product_id =3D '82503' and e1.product_id !=3D e2.product_id group by e1.product_id, e2.product_id, e2.site_id; QUERY PLAN ---------------------------------------------------------------------------= ------------------------------------------------- GroupAggregate (cost=3D828395.67..945838.90 rows=3D22110 width=3D19) -> Sort (cost=3D828395.67..840117.89 rows=3D4688885 width=3D19) Sort Key: e1.product_id, e2.product_id, e2.site_id -> Nested Loop (cost=3D11.85..20371.14 rows=3D4688885 width= =3D19) -> Bitmap Heap Scan on events e1 (cost=3D11.29..1404.31= rows=3D369 width=3D49) Recheck Cond: (product_id =3D '82503'::citext) -> Bitmap Index Scan on events_product_id_site_id_= idx (cost=3D0.00..11.20 rows=3D369 width=3D0) Index Cond: (product_id =3D '82503'::cit= ext) -> Index Scan using events_session_id_type_product_id_id= x on events e2 (cost=3D0.56..51.28 rows=3D12 width=3D51) Index Cond: ((session_id =3D e1.session_id) AND (type = =3D e1.type)) Filter: (e1.product_id <> product_id) (11 rows) recommender_production=3D> \d events Table "public.events" Column | Type | Modifiers -------------+--------------------------+----------------------------------= ------------------- id | bigint | not null default nextval('eve= nts_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, ty= pe, product_id) Check constraints: "events_session_id_check" CHECK (length(session_id::text) <= ; 255) "events_type_check" CHECK (type =3D ANY (ARRAY['purchased= '::text, 'viewed'::text])) "events_user_id_check" CHECK (length(user_id::text) < 255)=

After r= emoving the session with 400k events, I was able to do an explain analyze, = here is one of them:
http://explain.depesz.com/s/PFNk

explain = select e1.product_id, e2.site_id, e2.product_id, count(nullif(e2.type=3D'viewed', false)) view_count, count(nullif(e2.type=3D'purchased', false)) purchase_count from events e1 join events e2 on e1.session_id =3D e2.session_id and e1.type =3D e2.type where e1.product_id =3D '82503' and e1.product_id !=3D e2.product_id group by e1.product_id, e2.product_id, e2.site_id; QUERY PLAN ---------------------------------------------------------------------------= ------------------------------------------------- GroupAggregate (cost=3D828395.67..945838.90 rows=3D22110 width=3D19) -> Sort (cost=3D828395.67..840117.89 rows=3D4688885 width=3D19) Sort Key: e1.product_id, e2.product_id, e2.site_id -> Nested Loop (cost=3D11.85..20371.14 rows=3D4688885 width= =3D19) -> Bitmap Heap Scan on events e1 (cost=3D11.29..1404.31= rows=3D369 width=3D49) Recheck Cond: (product_id =3D '82503'::citext) -> Bitmap Index Scan on events_product_id_site_id_= idx (cost=3D0.00..11.20 rows=3D369 width=3D0) Index Cond: (product_id =3D '82503'::cit= ext) -> Index Scan using events_session_id_type_product_id_id= x on events e2 (cost=3D0.56..51.28 rows=3D12 width=3D51) Index Cond: ((session_id =3D e1.session_id) AND (type = =3D e1.type)) Filter: (e1.product_id <> product_id) (11 rows) recommender_production=3D> \d events Table "public.events" Column | Type | Modifiers -------------+--------------------------+----------------------------------= ------------------- id | bigint | not null default nextval('eve= nts_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, ty= pe, product_id) Check constraints: "events_session_id_check" CHECK (length(session_id::text) <= ; 255) "events_type_check" CHECK (type =3D ANY (ARRAY['purchased= '::text, 'viewed'::text])) "events_user_id_check" CHECK (length(user_id::text) < 255)=