[PERFORM] Cost estimate vs. actual - do I care?

go up with actual time, you're fine. 2. You should try to ensure that costs go up linearly with actual time. 3. You should try to ensure that costs are as close as possible to actual time. 4. The number "4". Jay Levitt -- Sent via pgsql-performance mailing list (pgsql-performanc

Re: [PERFORM] Subquery in a JOIN not getting restricted?

Tom Lane wrote: Jay Levitt writes: If the query was more like select questions.id from questions join ( select sum(u.id) from users as u group by u.id ) as s on s.id = questions.user_id where questions.id = 1; would you no longer be surprised that it scanned all user rows

Re: [PERFORM] Subquery in a JOIN not getting restricted?

ing" helps; we'll just have to rephrase the query. Jay -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance

Re: [PERFORM] Subquery in a JOIN not getting restricted?

Kevin Grittner wrote: Merlin Moncure wrote: Well, this may not fit the OP's 'real' query Right, if I recall correctly, the OP said it was simplified down as far as it could be and still have the issue show. but the inner subquery is probably better written as a semi-join (WHERE EXISTS).

Re: [PERFORM] Subquery in a JOIN not getting restricted?

Kevin Grittner wrote: Jay Levitt wrote: I don't get why the GROUP BY in this subquery forces it to scan the entire users table (seq scan here, index scan on a larger table) when there's only one row in users that can match: Are you sure there's a plan significantly fas

Re: [PERFORM] Subquery in a JOIN not getting restricted?

Jay Levitt wrote: And yep! When I do a CREATE TABLE AS from that view, and add an index on user_id, it works just as I'd like. Or not. Feel free to kick me back over to pgsql-novice, but I don't get why the GROUP BY in this subquery forces it to scan the entire users table (seq

Re: [PERFORM] Subquery in a JOIN not getting restricted?

anyway, so that's what I'll do. Thanks for the push in the right direction.. Jay -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance

[PERFORM] Subquery in a JOIN not getting restricted?

that matches questions.user_id? Jay Levitt -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance

Re: [PERFORM] Predicates not getting pushed into SQL function?

Jay Levitt wrote: Yes, that patch works great! Oddly enough, the workaround now does NOT work; functions returning SETOF named composite types don't get inlined, but functions returning the equivalent TABLE do get inlined. Let me know if you need a failcase, but the bug doesn't actua

Re: [PERFORM] Predicates not getting pushed into SQL function?

ning the equivalent TABLE do get inlined. Let me know if you need a failcase, but the bug doesn't actually affect me now :) Jay > > create type matcher_result as (user_id int, match int); > > create or replace function matcher() returns setof matcher_result as ... -- S

Re: [PERFORM] Predicates not getting pushed into SQL function?

What other info can I provide?  id is int, gender is varchar(255), and it's happening on 9.0.4... Tom Lane November 3, 2011 2:41 PM Jay Levitt writes: I'm confused. I have a now-trivial SQL function that, unrestricted, would scan my whole users tabl

[PERFORM] Predicates not getting pushed into SQL function?

I'm confused. I have a now-trivial SQL function that, unrestricted, would scan my whole users table. When I paste the body of the function as a subquery and restrict it to one row, it only produces one row. When I paste the body of the function into a view and restrict it to one row, it produ

[PERFORM] Guide to PG's capabilities for inlining, predicate hoisting, flattening, etc?

l perform great but it will be difficult to maintain, and it will be inelegant and a kitten will die. My tools are CTEs, subqueries, aliases, SQL functions, PL/pgSQL functions, and views (and other tools?) What optimizations do each of those prevent? We're on 9.0 now but will happily upgrade to 9.1 if

[PERFORM] High Frequency Inserts to Postgres Database vs Writing to a File

frequency writes to a postgres database? - Jay

[PERFORM] 答复: [PERFORM] Improve COPY performance for large data sets

I suspect your table has index, or checkpoint_segments is small and lead PG do checkpoint frequently. If the table has index or constraint, drop it and copy it ,after copy finished, do create index or constraint again. If checkpoint_segments is small, enlarge it. And also you can turn fsync off wh

Re: [PERFORM] Using PK value as a String

Well thats what I thought when I first started writing the tables... To Jay: Thanks for keeping it short and simple. "I'd like sequence, which is unique and just use 8 bytes(bigint) When it querying other tables, it will faster , and disk space smaller than UUID(40 bytes)." I'm

[PERFORM] 答复: [PERFORM] Using PK value as a String

22088-13072 贸易通ID:jaymo 淘宝ID:jackem 公司网站:www.alisoft.com wiki:http://10.0.32.21:1688/confluence/pages/viewpage.action?pageId=10338 -邮件原件- 发件人: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] 代表 Jay 发送时间: 2008年8月11日 15:35 收件人: pgsql-performance@postgresql.org 主题: [PERFORM] Using PK value as a S

[PERFORM] Using PK value as a String

I have a table named table_Users: CREATE TABLE table_Users ( UserID character(40) NOT NULL default '', Username varchar(256) NOT NULL default '', Email varchar(256) NOT NULL default '' etc... ); The UserID is a character(40) and is generated using UUID function. We

[PERFORM] 答复: [PERFORM] Postgresql update op is very very slow

ort transaction within function yet? -邮件原件- 发件人: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] 代表 Heikki Linnakangas 发送时间: 2008年6月25日 18:11 收件人: jay 抄送: pgsql-performance@postgresql.org 主题: Re: [PERFORM] Postgresql update op is very very slow jay wrote: > I've a table with abou

[PERFORM] 答复: [PERFORM] Postgresql update op is very very slow

Conover 发送时间: 2008年6月25日 13:02 收件人: jay 抄送: pgsql-performance@postgresql.org 主题: Re: [PERFORM] Postgresql update op is very very slow On Jun 24, 2008, at 9:12 PM, jay wrote: > > I've a table with about 34601755 rows ,when I execute 'update > msg_table set >

[PERFORM] Postgresql update op is very very slow

I've a table with about 34601755 rows ,when I execute 'update msg_table set type=0;' is very very slow, cost several hours, but still not complete? Why postgresql is so slowly? Is the PG MVCC problem? But I try it on Mysql, the same table and rows, it only cost about 340 seconds. Any idea for

Re: [PERFORM] Questions on Tags table schema

Hey Richard, Thanks again for the reply, its great to hear some feedback. So once again, here we go: On 7/30/07, Richard Huxton <[EMAIL PROTECTED]> wrote: > > Jay Kang wrote: > > Thanks for the reply Richard, but I guess I didn't explain myself well. > I > > ha

Re: [PERFORM] Questions on Tags table schema

e a better solution or is this wishful thinking? On 7/30/07, Richard Huxton <[EMAIL PROTECTED]> wrote: > > Jay Kang wrote: > > Hello, > > > > I'm currently trying to decide on a database design for tags in my web > > 2.0application. The problem I'm

[PERFORM] Questions on Tags table schema

on the schoolname field, because it accepts varchar not integer. There seems to be some design that would better fit my needs. I'm asking you guys for a little assistance. -- Regards, Jay Kang

Re: [PERFORM] Postgres slower than MS ACCESS

hanks, Jay. -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Jeff Trout Sent: Wednesday, February 15, 2006 6:23 AM To: Jay Greenfield Cc: 'Tom Lane'; 'Stephen Frost'; pgsql-performance@postgresql.org Subject: Re: [PERFORM] Postgres sl

Re: [PERFORM] Postgres slower than MS ACCESS

> Hmm, the large number of columns might have something to do with it ... > what datatypes are the columns? All sorts, but mostly float4 and varchar(2 to 10) Jay -Original Message- From: Tom Lane [mailto:[EMAIL PROTECTED] Sent: Tuesday, February 14, 2006 1:03 PM To: Jay Greenfi

Re: [PERFORM] Postgres slower than MS ACCESS

ot;Total runtime: 4472646.988 ms" > Is this table exceptionally large in same way (ie: lots > of columns)? The table is 1.2 million rows X 246 columns. The only index is the primary key. I will try to remove that index to see if that improves performance at all. Jay -Origi

Re: [PERFORM] Postgres slower than MS ACCESS

Is it possible to configure Postgres to behave like Access - a single user and use as much of the recourses as required? Thanks, Jay. -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Scott Marlowe Sent: Tuesday, February 14, 2006 8:05 AM To: Jay

[PERFORM] Postgres slower than MS ACCESS

tested.   work_mem:  1,000 to 2,000,000 temp_buffers:  1,000 to 10,000 shared_buffers:  1,000 to 64,000 sort_mem:  1,024,000 fsync on / off   Why does Access run so much faster?  How can I get Postgres to run as fast as Access?   Thanks,   Jay