>
> Additionally in the plan which mysql makes and showing the highest
> response time, is it suffering because of differences of the speed of the
> underlying IO/storage or is it just because of the optimization features
> which are available in postgres and not there in mysql ? Trying to
> unders
On Wed, Oct 23, 2024 at 2:06 AM yudhi s wrote:
>
>
> On Wed, Oct 23, 2024 at 12:32 AM Greg Sabino Mullane
> wrote:
>
>> To be frank, there is so much wrong with this query that it is hard to
>> know where to start. But a few top items:
>>
>> * Make sure all of the tables involved have been analy
On Tue, 2024-10-22 at 13:06 -0700, David G. Johnston wrote:
> On Tue, Oct 22, 2024 at 1:01 PM Ron Johnson wrote:
> > On Tue, Oct 22, 2024 at 3:02 PM Greg Sabino Mullane
> > wrote:
> > > * Don't use the "FROM table1, table2, table3" syntax but use "FROM table1
> > > JOIN table2 ON (...) JOIN tab
On Wed, Oct 23, 2024 at 12:32 AM Greg Sabino Mullane
wrote:
> To be frank, there is so much wrong with this query that it is hard to
> know where to start. But a few top items:
>
> * Make sure all of the tables involved have been analyzed. You might want
> to bump default_statistics_target up and
On Tue, Oct 22, 2024 at 1:01 PM Ron Johnson wrote:
> On Tue, Oct 22, 2024 at 3:02 PM Greg Sabino Mullane
> wrote:
> [snip]
>
>> * Don't use the "FROM table1, table2, table3" syntax but use "FROM table1
>> JOIN table2 ON (...) JOIN table3 ON (...)
>>
>
> Why?
>
>
Readability is improved when done
On Tue, Oct 22, 2024 at 3:02 PM Greg Sabino Mullane
wrote:
[snip]
> * Don't use the "FROM table1, table2, table3" syntax but use "FROM table1
> JOIN table2 ON (...) JOIN table3 ON (...)
>
Why?
--
Death to , and butter sauce.
Don't boil me, I'm still alive.
crustacean!
To be frank, there is so much wrong with this query that it is hard to know
where to start. But a few top items:
* Make sure all of the tables involved have been analyzed. You might want
to bump default_statistics_target up and see if that helps.
* As mentioned already, increase work_mem, as you
On 10/21/24 23:31, yudhi s wrote:
On Thu, Oct 17, 2024 at 3:06 AM Peter J. Holzer wrote:
The execution plan looks like a postgresql execution plan, not a mysql
execution plan. Did you run this query on postgresql? That may be
interesting for comparison purposese, but ultimately i
On Thu, Oct 17, 2024 at 3:06 AM Peter J. Holzer wrote:
>
> The execution plan looks like a postgresql execution plan, not a mysql
> execution plan. Did you run this query on postgresql? That may be
> interesting for comparison purposese, but ultimately it is useless: You
> won't get mysql to work
On Thu, Oct 17, 2024 at 3:06 AM Peter J. Holzer wrote:
> On 2024-10-16 23:20:36 +0530, yudhi s wrote:
> > Below is a query which is running for ~40 seconds.
> [...]
> > In the execution path below , the line number marked in bold are the top
> lines
> > for the IN and NOT IN subquery evaluation a
On 2024-10-16 23:20:36 +0530, yudhi s wrote:
> Below is a query which is running for ~40 seconds.
[...]
> In the execution path below , the line number marked in bold are the top lines
> for the IN and NOT IN subquery evaluation and they are showing "Actual time"
> as
> Approx ~9 seconds and ~8 s
On 10/16/24 10:50 AM, yudhi s wrote:
Hi,
Below question got in one discussion.Appreciate any guidance on this.
Below is a query which is running for ~40 seconds. As it's a query which
is executed from UI , we were expecting it to finish in <~5 seconds. It
has a "IN" and a "NOT IN" subquery
Thanks for the update.
On Mon, Jan 29, 2024, 16:53 Ron Johnson wrote:
> According to my tests, sometimes JIT is a little faster, and sometimes
> it's a little slower. Mostly within the realm of statistical noise
> (especially with each query having a sample size of only 13, on a VM that
> lives
According to my tests, sometimes JIT is a little faster, and sometimes it's
a little slower. Mostly within the realm of statistical noise (especially
with each query having a sample size of only 13, on a VM that lives on a
probably-busy host).
On Mon, Jan 29, 2024 at 9:18 AM Ron Johnson wrote:
>
Yes, jit=on.
I'll test them with jit=off, to see the difference. (The application is
3rd party, so will change it at the system level.)
On Mon, Jan 29, 2024 at 7:09 AM Bob Jolliffe wrote:
> Out of curiosity, is the pg14 running with the default jit=on setting?
>
> This is obviously entirely du
Out of curiosity, is the pg14 running with the default jit=on setting?
This is obviously entirely due to the nature of the particular queries
themselves, but we found that for our workloads that pg versions
greater than 11 were exacting a huge cost due to the jit compiler. Once we
explicitly turn
On Sun, Jan 28, 2024 at 10:44 PM David Rowley wrote:
> On Mon, 29 Jan 2024 at 07:37, Ron Johnson wrote:
>
>> 08 9.6.24 1,142.164 1,160.801 1,103.716 1,249.852 1,191.081
>> 14.10 159.354 155.111 155.111 162.797 158.157 86.72%
>>
>
> Your speedup per cent calculation undersells PG14 by quite a bit
On Mon, 29 Jan 2024 at 07:37, Ron Johnson wrote:
> 08 9.6.24 1,142.164 1,160.801 1,103.716 1,249.852 1,191.081
> 14.10 159.354 155.111 155.111 162.797 158.157 86.72%
>
Your speedup per cent calculation undersells PG14 by quite a bit. I'd call
that an increase of ~639% rather than 86.72%.
I thi
: [EXT] Re: Query performance going from Oracle to Postgres
External Email: Use caution with links and attachments.
On Thu, Sep 7, 2023 at 3:48 AM David Rowley wrote:
> On Thu, 7 Sept 2023 at 19:17, Peter Geoghegan wrote:
> > It seems likely that the problem here is that some of the p
On Thu, Sep 7, 2023 at 3:48 AM David Rowley wrote:
> On Thu, 7 Sept 2023 at 19:17, Peter Geoghegan wrote:
> > It seems likely that the problem here is that some of the predicates
> > appear as so-called "Filter:" conditions, as opposed to true index
> > quals.
>
> hmm, if that were true we'd see
On Thu, 7 Sept 2023 at 19:17, Peter Geoghegan wrote:
> It seems likely that the problem here is that some of the predicates
> appear as so-called "Filter:" conditions, as opposed to true index
> quals.
hmm, if that were true we'd see "Rows Removed by Filter" in the
explain analyze.
I think all t
On Wed, Sep 6, 2023 at 1:07 PM Dirschel, Steve
wrote:
> Oracle will find the same 332 rows using the same index but in Oracle it only
> does 20 logical reads. I thought maybe the index was fragmented so I
> reindexed that index:
It seems likely that the problem here is that some of the predica
On Thu, 7 Sept 2023 at 11:14, Dirschel, Steve
wrote:
> select count(historyeve0_.HISTORY_EVENT_SID) as col_0_0_ from
> hist28.history_event_display_timestamp_20230301 historyeve0_ where
> historyeve0_.IS_DELETED=0
> history_event_sid | character varying(32) | | not
> nu
On Wed, 2023-09-06 at 20:06 +, Dirschel, Steve wrote:
> We are in the process of converting from Oracle to Postgres and I have a
> query that is using
> the same index in Postgres as is used in Oracle but in Postgres the query
> does 16x more
> buffer/logical reads. I’d like to understand wh
I should have given you the full query. Here it is
Select a.locationname, a.usercode, a.itemname, a.uomname, a.batchnumber,
a.expirydate, a.itemnamefk, a.itemuomfk, f.itemgroupname, a.locationpk,
Sum(a.quantity) as quantity, Sum(a.freequantity) as freequantity,
b.befo
On 2022-09-17 05:28:25 +, sivapostg...@yahoo.com wrote:
> My query is like this
>
> Select a.field1, a.field2, a.field3
> From (Select a.field1, b.field2, c.field3
> From table1 a
> Join table2 b
> on b.something = a.something
> Join table3 c
>
On 9/17/22 00:28, sivapostg...@yahoo.com wrote:
Hello,
My query is like this
Select a.field1, a.field2, a.field3
From (Select a.field1, b.field2, c.field3
From table1 a
Join table2 b
on b.something = a.something
Join table3 c
On c.somethin
Hello,
My query is like this
Select a.field1, a.field2, a.field3From (Select a.field1, b.field2, c.field3
From table1 a Join table2 b on b.something =
a.something Join table3 c On c.something = a.something
Where a.field7 = 'value'
"Yorwerth, Adam" writes:
> We seem to have found a situation where a query run using explain analyse or
> pgbench is incredibly fast, but run via Java under load performs very poorly
> – we’ve checked query performance metrics for our Postgres instance and can
> confirm that it’s the query runn
Hi Adam,
What're the query times when you run the query directly on the psql prompt, but
without explain/analyze?
Can you check the cache hit rate vs disk read on explain analyze vs from java?
Sometimes, the data's in RAM when you run a query manually, but the live Java
app might be hitting th
On Sun, 18 Feb 2018 00:35:18 +0100
hmidi slim wrote:
> Hi,
> I have two tables: establishment which contains these columns: id, name,
> longitude, latitude, geom (Geometric column)
> Product contains: id, name, establishment_id
> First of all I want to select the establishment within a radius.
>
On 02/18/2018 06:37 AM, David Rowley wrote:
> On 18 February 2018 at 12:35, hmidi slim wrote:
>> Is there an other optimized solution to make a query such this:
>> select * from (
>> select e.name, e1.name, e.id
>> from establishment as e, establishment as e1
>> where e.id <> e1.id
>> and e1.id =
On 18 February 2018 at 12:35, hmidi slim wrote:
> Is there an other optimized solution to make a query such this:
> select * from (
> select e.name, e1.name, e.id
> from establishment as e, establishment as e1
> where e.id <> e1.id
> and e1.id = 1
> and ST_DWithin(geom, ST_MakePoint(e1.longitude,
33 matches
Mail list logo
