[PERFORM] Query plan issues - volatile tables

? Basically we want it to assume that there may be 0, 1 or 100,000 entries coming out from a query on that table at any time, and that it should not make any assumptions. Thanks, Brian Brian Herlihy Trellian Pty Ltd +65 67534396 (Office) +65 92720492 (Handphone

Re: [PERFORM] strange pauses

We had the same situation, and did two things 1. Reduce checkpoint timeout 2. Reduce quantity of data going into database (nice if it's possible!) 1 alone wasn't enough to eliminate the delays, but it did make each delay small enough that the user interface was only minimally affected. Previo

Re: [PERFORM] Vacuum goes worse

Would it make sense to show the FSM stats for individual table vaccums as well? I'm wondering if the reason they aren't shown is because it wouldn't be useful or isn't practical, or just that it hasn't been done. Brian - Original Message From: Tom Lane <[EMAIL PROTECTED]> If you're t

[PERFORM] Join vs Subquery

Hi, I am using postgres 8.1.3 for this. If this has been dealt with later, please disregard. And this is not a complaint or a request, I am just curious, so I know how to best construct my queries. There is a unique index mapping domains to domain_ids. views_ts specifies a partitioned table,

Re: [PERFORM] An unwanted seqscan

From: Tom Lane <[EMAIL PROTECTED]> To: Brian Herlihy <[EMAIL PROTECTED]> Cc: Postgresql Performance Sent: Wednesday, 14 February, 2007 4:53:54 PM Subject: Re: [PERFORM] An unwanted seqscan Brian Herlihy <[EMAIL PROTECTED]> writes: > I am having trouble understanding wh

[PERFORM] An unwanted seqscan

Hi, I am having trouble understanding why a seqscan is chosen for this query. In practice the seqscan is very expensive, whereas the nested loop is usually quite fast, even with several hundred rows returned from meta_keywords_url. The server is running version 8.1.3, and both tables were analy

Re: [PERFORM] Querying distinct values from a large table

As I understand, there's no hashing for DISTINCT, but there is for GROUP BY. GROUP BY will choose between a hash and a sort (or maybe other options?) depending on the circumstances. So you can write SELECT a, b FROM tbl GROUP BY a,b and the sort/unique part of the query may run faster. Brian

Re: [PERFORM] group by will not use an index?

Actually, as I recently discovered, GROUP BY is faster than DISTINCT. It's just due to how they are implemented, so don't go looking for any deep reason :) The thread "GROUP BY vs DISTINCT" from 2006-12-20 discusses it. DISTINCT sorts the results to find the unique rows, but GROUP BY uses a h

[PERFORM] GROUP BY vs DISTINCT

I have a question about the following. The table has an index on (clicked at time zone 'PST'). I am using postgres 8.1.3 Actually, I think I answered my own question already. But I want to confirm - Is the GROUP BY faster because it doesn't have to sort results, whereas DISTINCT must produce

Re: [PERFORM] Simple join optimized badly?

--- Tom Lane <[EMAIL PROTECTED]> wrote: > Personally I have no use for the idea of "force the planner to do > exactly X given a query of exactly Y". You don't have exactly Y > today, tomorrow, and the day after (if you do, you don't need a > hint mechanism at all, you need a mysql-style query cach

Re: [PERFORM] Simple join optimized badly?

-- tom lane wrote - "Jim C. Nasby" <[EMAIL PROTECTED]> writes: > I'd rather have the ugly solution sooner rather than the elegant one > later (if ever). The trouble with that is that we couldn't ever get rid of it, and we'd be stuck with back

Re: [PERFORM] Simple join optimized badly?

PG does support hints actually.. and I used them to solve the last performance problem I had, rather than waiting n years for the query planner to be improved. The problem in question (from an automated query planning point of view) is the lack of multi-column statistics, leading to the wrong inde

Re: [PERFORM] Query planner is using wrong index.

--- Tom Lane <[EMAIL PROTECTED]> wrote: > Brian Herlihy <[EMAIL PROTECTED]> writes: > > My options seem to be > > - Fudge the analysis results so that the selectivity estimate changes. I > > have tested reducing n_distinct, but this doesn't seem to help

Re: [PERFORM] Query planner is using wrong index.

--- Dave Dutcher <[EMAIL PROTECTED]> wrote: > > -Original Message- > > To: pgsql-performance@postgresql.org > > Subject: Re: [PERFORM] Query planner is using wrong index. > [Snip] > > I am really surprised that I have to go through such contortions just > to > > use > > the primary key! T

Re: [PERFORM] Query planner is using wrong index.

--- Ragnar <[EMAIL PROTECTED]> wrote: > On fös, 2006-04-07 at 00:01 +1000, Brian Herlihy wrote: > > Index Scan using p2_p3_idx on t (cost=0.00..6.02 rows=1 width=102) > (actual > > time=2793.247..2793.247 rows=0 loops=1) > >Index Cond: (((p2)::text = 

Re: [PERFORM] Query planner is using wrong index.

--- Ragnar <[EMAIL PROTECTED]> wrote: > On fim, 2006-04-06 at 19:27 +1000, Brian Herlihy wrote: > > > Yes, the primary key is far better. I gave it the ultimate test - I > dropped > > the (p2, p3) index. It's blindingly fast when using the PK, > > I h

Re: [PERFORM] Query planner is using wrong index.

--- Ragnar <[EMAIL PROTECTED]> wrote: > On fim, 2006-04-06 at 12:35 +1000, Brian Herlihy wrote: > > > I have a problem with the choice of index made by the query planner. > > > > My table looks like this: > > > > CREATE TABLE t > > ( >

[PERFORM] Query planner is using wrong index.

Hi, I have a problem with the choice of index made by the query planner. My table looks like this: CREATE TABLE t ( p1 varchar not null, p2 varchar not null, p3 varchar not null, i1 integer, i2 integer, i3 integer, i4 integer, i5 integer, d1 date, d2 date, d3 date, PRIMAR