*not* the psql session)
gdb /usr/bin/postgres
attach
break toast_fetch_datum_slice
continue
session 1:
select substring(residues from 100 for 2) from feature where
feature_id=1;
session 2:
did we hit the breakpoint in toast_fetch_datum_slice?
HTH,
Joe
re
out what is going on.
Is there a sample table schema and dataset available (external-storage
case) that we can play with?
Joe
---(end of broadcast)---
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
YZE on these update statements manually. It also
might help to run VACUUM ANALYZE after populating the tables.
HTH,
Joe
---(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
Tom Lane wrote:
Scott Cain <[EMAIL PROTECTED]> writes:
A few days ago, I asked for advice on speeding up substring queries on
the GENERAL mailing list. Joe Conway helpfully pointed out the ALTER
TABLE STORAGE EXTERNAL documentation. After doing the alter,
the queries got slower! Here
Scott Cain wrote:
Oh, and I forgot to mention: it's highly compressed (bzip2 -9) and is
109M.
Thanks. I'll grab a copy from home later today and see if I can find
some time to poke at it.
Joe
---(end of broadcast)---
TIP 1: sub
ying to do, but
perhaps some sort of calculated checksum or hash would work to determine
if the data has changed?
Joe
---(end of broadcast)---
TIP 6: Have you searched our list archives?
http://archives.postgresql.org
t, this is still taking a long time.
---
Seq Scan on user_account (cost=0.00..748990.51 rows=36242 width=716)
Do you have an index on user_account.user_account_id?
Joe
---(end of broadcast)---
TIP 8: explain analyze is your friend
(10,0))?
Also, have you run VACUUM ANALYZE lately?
Joe
---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
always restore from last night and re-run everything that was done since
then.
If you can, use COPY -- it is far faster than INSERT.
See:
http://www.postgresql.org/docs/current/static/sql-copy.html
HTH,
Joe
---(end of broadcast)---
TIP 9: the planner
tal runtime: 1.35 msec
This was intended to just measure the time to execute a simple "hello
world" type of function, for the first time in a given session. I did
not repeat/average the results though, so you might want to do some of
your own testing.
Joe
---(end of broadcast)---
TIP 8: explain analyze is your friend
eases to 7 -
9 minutes. Restarting the postmaster causes the cycle to repeat, i.e.
the first one or two inserts are back to the 1.5 minute range.
Any ideas spring to mind? I don't have much experience with Postgres on
Solaris -- could it be related to that somehow?
Thanks fo
reply.
Joe
---(end of broadcast)---
TIP 8: explain analyze is your friend
after sprinkling your
SQL with hints, you could easily periodically (e.g. after a Postgres
upgrade) test what would happen if the hints were removed.
Joe
--
Joe Conway
credativ LLC: http://www.credativ.us
Linux, PostgreSQL, and general Open Source
Training, Service, Consulting, & 24x
On 11/21/2012 09:28 AM, Craig James wrote:
>
>
> On Wed, Nov 21, 2012 at 9:25 AM, Joe Conway <mailto:m...@joeconway.com>> wrote:
>
> On 11/21/2012 08:05 AM, Heikki Linnakangas wrote:
> > Rather than telling the planner what to do or not to do, I'd m
manage so many databases, but I
would test it carefully before committing.
Joe
- --
Joe Conway
credativ LLC: http://www.credativ.us
Linux, PostgreSQL, and general Open Source
Training, Service, Consulting, & 24x7 Support
-BEGIN PGP SIGNATURE-
Version: GnuPG v1.4.12 (GNU/Linux)
Comm
aggregate
plans, but I suspect there is a lot to be gained there as well.
HTH,
Joe
- --
Joe Conway
-BEGIN PGP SIGNATURE-
Version: GnuPG v2.0.22 (GNU/Linux)
iQIcBAEBAgAGBQJVfHITAAoJEDfy90M199hlGvcP/ijyCsXnWZAeZSUAW4qb20YJ
AHKn0Gl8D9mH9cfPfJeCO+60dcWINzUE6l7qOWWN8JtT6pgbRPGvQsCkx9xRz
Also available on S.O.:
http://stackoverflow.com/questions/35658238/postgres-odd-behavior-with-indices
I've got a datavalue table with ~200M rows or so, with indices on both
site_id and parameter_id. I need to execute queries like "return all sites
with data" and "return all parameters with data"
16 at 2:02 PM, David G. Johnston <
david.g.johns...@gmail.com> wrote:
> On Fri, Feb 26, 2016 at 12:43 PM, joe meiring
> wrote:
>
>> Also available on S.O.:
>>
>>
>> http://stackoverflow.com/questions/35658238/postgres-odd-behavior-with-indices
>>
&g
of out dated statistics
Have done an analyze table but no changes.
Thanks,
Joe
From: pgsql-performance-ow...@postgresql.org
[mailto:pgsql-performance-ow...@postgresql.org] On Behalf Of Jake Nielsen
Sent: Wednesday, September 28, 2016 2:11 PM
To: Tom Lane
Cc: pgsql-performance@postgresql.org
S
My Apologies , was in the wrong email/forum, please disregard my email!
From: pgsql-performance-ow...@postgresql.org
[mailto:pgsql-performance-ow...@postgresql.org] On Behalf Of Joe Proietti
Sent: Friday, September 30, 2016 8:03 AM
To: Jake Nielsen ; Tom Lane
Cc: pgsql-performance
the patch originally I saw significant
improvements, e.g. 8x in early versions. The thread is here:
https://www.postgresql.org/message-id/flat/44C4451A.4010906%40joeconway.com#44c4451a.4010...@joeconway.com
Joe
--
Crunchy Data - http://crunchydata.com
PostgreSQL Support for Secure Enterprises
Consulting, Training, & Open Source Development
signature.asc
Description: OpenPGP digital signature
re be excessive overhead from the CREATE/DROP POLICY
statements?
Thanks,
Joe
--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
olicy every time.
Thanks,
Joe
On 10/17/2017 03:06 PM, Tom Lane wrote:
Tomas Vondra writes:
On 10/17/2017 10:44 PM, Joe Carlson wrote:
What I was wondering is what is the performance differences between a
row level security implementation:
...
and an implementation where I add on the constr
Yes it does:
SET EXPLAIN ON;
It writes the file to sqexplain.out
-Original Message-
From: [EMAIL PROTECTED]
[mailto:[EMAIL PROTECTED] On Behalf Of Tom Lane
Sent: Tuesday, January 09, 2007 9:13 AM
To: Gregory S. Williamson
Cc: [EMAIL PROTECTED]; pgsql-performance@postgresql.org
Subject:
I have used this in the past ... run this against the database that you want to
inspect.
SELECT
current_database(), schemaname, tablename, /*reltuples::bigint,
relpages::bigint, otta,*/
ROUND(CASE WHEN otta=0 THEN 0.0 ELSE sml.relpages/otta::numeric END,1) AS
tbloat,
CASE WHEN relpages <
I was always under the impression that pg_dump and pg_dumpall cause all data to
be read in to the buffers and then out, (of course squeezing out whatever may
be active). That is the big advantage to using PITR backups and using a tar or
cpio method of backing up active containers and shipping o
I have a very busy system that takes about 9 million inserts per day and each
record gets updated at least once after the insert (all for the one same
table), there are other tables that get hit but not as severely. As suspected
I am having a problem with table bloat. Any advice on how to be m
---++-+---
public | log_events_y2010m02| 356 GB | 610 GB
-Original Message-
From: Scott Marlowe [mailto:scott.marl...@gmail.com]
Sent: Monday, March 01, 2010 12:58 AM
To: Plugge, Joe R.
Cc: pgsql-performanc
]
Sent: Monday, March 01, 2010 6:51 AM
To: Plugge, Joe R.
Cc: Scott Marlowe; pgsql-performance@postgresql.org
Subject: Re: [PERFORM] Autovacuum Tuning advice
storing all fields as varchar surely doesn't make:
- indicies small,
- the thing fly,
- tables small.
...
On 9.2.4, running two identical queries except for the value of a column in
the WHERE clause. Postgres is picking very different query plans, the first
is much slower than the second.
Any ideas on how I can speed this up? I have btree indexes for all the
columns used in the query.
explain analyz
(
https://gist.github.com/joevandyk/df0df703f3fda6d14ae1/raw/c15cae813913b7f8c35b24b467a0c732c0100d79/gistfile1.txtshows
a non-wrapped version of the queries and plan)
On Fri, Apr 5, 2013 at 6:38 PM, Joe Van Dyk wrote:
> On 9.2.4, running two identical queries except for the value of a col
chased_items pi (cost=0.00..7.60 rows=2 width=4) (actual
time=0.005..0.005 rows=1 loops=8178)
Index Cond: (line_item_id = li.id)
Heap Fetches: 144
Total runtime: 103.442 ms
(11 rows)
On Fri, Apr 5, 2013 at 6:38 PM, Joe Van Dyk wrote:
> On 9.2.4, running two
On Fri, Apr 5, 2013 at 6:54 PM, Greg Williamson wrote:
> Joe --
>
> >____
> > From: Joe Van Dyk
> >To: pgsql-performance@postgresql.org
> >Sent: Friday, April 5, 2013 6:42 PM
> >Subject: Re: [PERFORM] slow joins?
> >
&g
The actual query selects columns from each of those tables.
If I remove the join on order_shipping_addresses, it's very fast. Likewise,
if I remove the join on skus, base_skus, or products, it's also very fast.
I'm pretty sure I have all the necessary indexes.
The below is also at
https://gist.g
Hm, setting set join_collapse_limit = 9 seemed to fix the problem. Is that
my best/only option?
On Mon, Dec 16, 2013 at 1:52 PM, Joe Van Dyk wrote:
> The actual query selects columns from each of those tables.
>
> If I remove the join on order_shipping_addresses, it's very fast.
On Mon, Dec 16, 2013 at 4:14 PM, Tom Lane wrote:
> Joe Van Dyk writes:
> > Hm, setting set join_collapse_limit = 9 seemed to fix the problem. Is
> that
> > my best/only option?
>
> Yup, that's what I was just about to suggest. You might want to use
> 10 or 12 in
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 c
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".
>
>
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
On Sat, Jan 24, 2015 at 10:12 PM, Pavel Stehule
wrote:
> Hi
>
> this plan looks well
>
> Regards
>
> Pavel
>
Here's one that's not quite as well: http://explain.depesz.com/s/SgT
Joe
>
> 2015-01-25 6:45 GMT+01:00 Joe Van Dyk :
>
>> Oops, di
On Sat, Jan 24, 2015 at 11:14 PM, Pavel Stehule
wrote:
>
>
> 2015-01-25 7:38 GMT+01:00 Joe Van Dyk :
>
>>
>>
>> On Sat, Jan 24, 2015 at 10:12 PM, Pavel Stehule
>> wrote:
>>
>>> Hi
>>>
>>> this plan looks well
>>>
I noticed that querying for
product_attributes @> '{"upsell":["true"]}'
is much slower than querying for
product_attributes @> '{"upsell": 1}'
Is that expected? I have a gin index on product_attributes. I'm using 9.4.1.
explain analyze
select count(*) from products where product_attributes
You're right, brain fart. Nevermind! :)
On Sat, Nov 7, 2015 at 4:00 PM, Tom Lane wrote:
> Joe Van Dyk writes:
> > I noticed that querying for
> >product_attributes @> '{"upsell":["true"]}'
> > is much slower than querying for
101 - 143 of 143 matches
Mail list logo
