I'm struggling to resolve a login error. It seems that I'm getting
"ident" authentication, no mater what I set pg_hba.conf to. Here's a
log file:
LOG: next transaction ID: 602; next OID: 17232
LOG: database system is ready
...
FATAL: Ident authentication failed for user "bryce"
L
t; I'm just trying to make sure you are connecting to the postmaster you
> think you are ...
>
>> Bryce Nesbitt:
>> I've renamed pg_hba.conf temporarily, just to verify that postmaster
>> won't start without it. So I'm editing the right file, and rest
IPv6?
Really?
That new fangled thing?
That's enabled on my machine by default?
Whadda know.
That's it.
Thanks.
Tom Lane wrote:
> Bryce Nesbitt <[EMAIL PROTECTED]> writes:
>
>> I've renamed pg_hba.conf temporarily, just to verify that postmaster
>> won&
When I delete a record from a certain table, I need to delete a
(possibly) attached note as well. How can I do this with postgres? The
tables are like this:
reservation
reservation_id
stuff...
isuse
issue_id
reservation_id
stuff..
note
issue_id
text comments...
A s
BigSmoke wrote:
> ...I'd usually do this by using
> issue_id INTEGER REFERENCES issue ON DELETE CASCADE
>
Good, and valuable, thanks!
But at the moment I can't change the schema. So is there a way to do a
cascaded or joined delete
in a sql schema that did not anticipate it? Again, this is d
Owen Jacobson wrote:
> BEGIN;
> DELETE FROM note WHERE issue_id IN (SELECT issue_id FROM isuse
> WHERE reservation_id = reservation_to_delete);
> DELETE FROM isuse WHERE reservation_id = reservation_to_delete;
> DELETE FROM reservations WHERE reservation_id = reservation_to_delete;
> COMM
Markus Schaber wrote:
> Hi, Bryce,
>
> Bryce Nesbitt wrote:
>
>
>> BEGIN;
>> DELETE FROM note WHERE issue_id IN (SELECT issue_id FROM isuse
>> WHERE reservation_id IN
>> (select reservation_id from reservations where date > magi
This threw me for a loop. Is this my fault, or a
problem in postgres?
I have a table with the following:
"eg_vehicle_event_pkey" PRIMARY KEY, btree (event_id)
"no_duplicate_events" UNIQUE, btree (thing, other_thing,
"timestamp", number, other_number)
The "no_duplicate_events" constraint
If I do:
select event_id from event join token using
(token_number) where token_status=50 and
reconciled=false limit 1;
Then:
update event set reconciled=true where event_id={XXX};
It returns in about a second, or less. But If I do the same thing with
the IN syntax:
Tom Lane wrote:
> Bryce Nesbitt <[EMAIL PROTECTED]> writes:
>
>> update event set reconciled=true where event_id in
>> (select event_id from event join token using (token_number)
>> where token_status=50 and reconciled=false LIMIT 1);
>>
&
Tom Lane wrote:
> Bryce Nesbitt <[EMAIL PROTECTED]> writes:
>
>> Tom Lane wrote:
>>
>>> What does EXPLAIN show for this and for the base query?
>>>
>
>
>>-> Seq Scan on event (cost=0.00..0.00
Peter Eisentraut wrote:
> Bryce Nesbitt wrote:
>
>>> It seems pretty clear that you've never vacuumed nor analyzed these
>>> tables ... else the planner would have some clue about their sizes.
>>> Do that and then see what you get.
>>>
&
Tom Lane wrote:
> Peter Eisentraut <[EMAIL PROTECTED]> writes:
>
>> Bryce Nesbitt wrote:
>>
>>> They occur in finite time. That's good, thanks. But jeeze, can't
>>> postgres figure this out for itself?
>>>
>> I&#x
Ken Hill wrote:
>> also (hate to be obvious) have you analyzed lately?
>>
I'd say that's fair game, not obvious. Vacuum/Analyze is ar so aparent
to a person moving
to Postgres from other DB's.
---(end of broadcast)---
TIP 4: Have you searched
le, easy to maintain?
Bryce Nesbitt
---(end of broadcast)---
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match
Andrew Sullivan wrote:
> On Wed, Mar 01, 2006 at 09:51:46AM -0800, Bryce Nesbitt wrote:
>
>> switch over and rebuild the DB. "No-lost transaction" is far more
>> important than switch time.
>>
>
> You can't guarantee that without two phase c
I'm getting sequential scans (and poor performance), on scans using my
primary keys.
Can anyone help figure out why?
demo=# \d xx_thing
Table "public.xx_thing"
Column |Type | Modifiers
-+
I'm getting sequential scans (and poor performance), on scans using my
primary keys. This is an older postgres.
Can anyone help figure out why?
demo=# \d xx_thing
Table "public.xx_thing"
Column |Type | Modifiers
-
Andrew Sullivan wrote:
thing_event_id=1;
^
in quotes. The automatic int4-int8 coercion is probably your
problem. Also
Yup that's it. But this project uses (ugh) Hibernate. I can't change
it. I may have to change
from BIGINT primary keys to IN
All;
I have a need to create a view. The data table does not exist yet, the
question is on how to best set it up in postgres. The resulting view
has to be spreadsheet-like, and will be loaded directly via ODBC into
Excel for number crunching. Maybe something like:
R
I've got a working query:
stage=# SELECT date_trunc('day',endtime),count(*)
FROM eg_event where endtime >= '2006-02-01' and endtime < '2006-03-01'
GROUP BY date_trunc('day',endtime)
ORDER BY date_trunc('day',endtime);
date_trunc | count
-+---
2006-02-01 00:00:0
Tom Lane wrote:
In this particular case you could say
... GROUP BY 1 ORDER BY 1;
"ORDER BY n" as a reference to the n'th SELECT output column is in the
SQL92 spec. (IIRC they removed it in SQL99, but we still support it,
and I think most other DBMSes do too.) "GROUP BY n" is *not* in an
Terry Lee Tucker wrote:
> rnd=# \h comment
> Command: COMMENT
> Description: define or change the comment of an object
> ..I believe this is what you need.
>
Cool!
That's a great feature. Though it would be even nicer if the comment
showed when
you "\d" a table::
stage=# comment on table
"volunteer on the xxx project", or none of the above.
I could assign each role a bit.
Or, create a string field: "Friend,Money, Emp,VolXXX".
Or, create related tables:
friend_pk, address_id
cardlist_pk, address_id
money_pk, add
I'm using the current Windows psqlODBC driver. "Show system" tables is
turned off, but not working. When I use an ODBC client I see all
tables... including system tables, and views & tables for which no
permission exists.
Anyone else see this? And where can I submit a bug report (I've looked,
b
Bryce Nesbitt wrote:
> Tom Lane wrote:
>> In this particular case you could say
>>
>> ... GROUP BY 1 ORDER BY 1;
>>
>> "ORDER BY n" as a reference to the n'th SELECT output column is in the
>> SQL92 spec. (IIRC they removed it in SQL
that meet a
particular where condition.
-Bryce Nesbitt
--
Visit http://www.obviously.com/
---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
Scott Marlowe wrote:
> If it's for the same rows in the same table, you're overworking it.
>
> update table set field1=field2 where somecondition;
>
If it were for the same rows in the same table, I would not have asked.
This is for columns from two tables, whos relationship is only
meaningful a
Tom Lane wrote:
Bryce Nesbitt <[EMAIL PROTECTED]> writes:
If it were for the same rows in the same table, I would not have asked.
This is for columns from two tables, whos relationship is only
meaningful after a join.
You have to write out the join condition lo
I find myself with long lists of tables
grant select on xx_tax to user;
grant select on xx_trip to user;
grant select on xx_foo to user;
Is there a way to grant to all tables, with a single grant? I know how
to do it in mysql, but not postgres. As close as I get it:
#grant select on data
A. Kretschmer wrote:
> Is there a way to grant to all tables, with a single grant? I know how
>
>
> No, but you can use a little Script, please read:
> http://people.planetpostgresql.org/greg/index.php?/archives/38-guid.html#extended
>
> HTH, Andreas
>
Thanks,
What always happens is I create
I have a function, the results of which seem to apply to ORDER BY and
HAVING, but not to WHERE. Is this expected?
-- Return distance in some mystery units (TODO: convert to miles or
kilometers)
CREATE FUNCTION calculate_distance(double precision, double precision,
double precision, double preci
Aaron Bono wrote:
> First I recommend making your function IMMUTABLE since, given the same
> arguments, it gives the same result - this will allow PostgreSQL to
> optimize the function call and cache the results.
Will do!
> Then, don't use "4", use "calculate_distance(lat,lon,
> 37.789629,-122.42
Aaron Bono wrote:
>
>
> On 7/10/06, *Bryce Nesbitt* <[EMAIL PROTECTED]
> <mailto:[EMAIL PROTECTED]>> wrote:
>
>
> I think it is ugly also, but no other syntax seems to work:
>
> stage=# select
> pod_code,lat,lon,calculate_distance(lat,lon
Tom Lane wrote:
> But as far as the underlying misconception goes, you seem to think that
> "4" in the WHERE clause might somehow be taken as referring to the
> fourth SELECT result column (why you don't think that the "1" would
> likewise refer to the first result column isn't clear). This is not
Thanks, that's good.
Rob Sargent wrote:
> create table junk_six (foo int)
> create unique index junk_six_id on junk_six(foo)
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
RY KEY, btree (userid, site_key)
"test_5_pkey" UNIQUE, btree (userid, site_key)
Foreign-key constraints:
"test_5_site_key_fkey" FOREIGN KEY (site_key) REFERENCES
contexts(context_key) ON DELETE CASCADE
"test_5_userid_fkey" FOREIGN KEY (userid) REFERENCE
Dear Postgres Gurus;
I've just diagnosed a PostgreSQL 8.3.4 server which, about once a month, would
deadlock shortly after 11pm. It had been doing this for years,
and the prior response was simply to reboot everything. The culprit boils down
to:
# create table cache_table_20090921 (
site_key
Dear Postgres Gurus;
I've just diagnosed a PostgreSQL 8.3.4 server which, about once a month,
would deadlock shortly after 11pm. It had been doing this for years,
and the prior response was simply to reboot everything. The culprit
boils down to:
# create table cache_table_20090921 (
site_key
Tom Lane wrote:
Bryce Nesbitt writes:
1) Why the AccessExclusiveLock on create table?
It has to install a trigger on the referenced table. There has been
some discussion that maybe CREATE TRIGGER could take just ExclusiveLock
and not AccessExclusiveLock, but it hasn&#
So when restoring a particular DB with pg_restore, I get "WARNING:
errors ignore on restore". Is there a way to dump a list of those errors?
# /usr/local/bin/pg_restore8.3 -d SUP-3067 -h 192.168.1.93 -p 5433 -U
postgres -L toc_with_parts_commented_out.txt -v production_db.dump.20091016
...
Craig Ringer wrote:
In truth, that's how I'd expect it to happen. If I ask for the byte 0xfd
in a string, I don't want the server to decide that I must've meant
something else because I have a different client encoding. If I wanted
encoding conversion, I wouldn't have written it in an escape fo
Craig Ringer wrote:
Yes - but you are *not* presenting a Latin-1 character. You're
presenting four Latin-1 characters:
'\', '3', '7', '5'
Well, then I have a different question. If I can view a bytea column as so:
> select object from context_objects where context_key=100041;
obje
I'm tracking another bug, but wanted to verify stuff on the command
line. I can't figure out why this did not work:
# psql dblack3-deleteme
Welcome to psql 8.3.8 (server 8.3.4), the PostgreSQL interactive
terminal.
dblack3-deleteme=> create table bryce1 (key int,val1 text,val2
bytea);
dblack3
I've just tracked down a serious bug with PG_BYTEA columns, which is probably
perl specific. But I thought
people might want to know:
Package: libdbd-pg-perl
Version: upgrading to 2.8.7 compared to 1.49. Our 1.49 was patched for the
memory leak in BYTEA.
With 2.8.7, if pg_server_prepare is s
I looking at a script that does a pg_restore followed by an immediate
VACUUM ANALYZE (postgres 8.3).
I'm told that without the VACUUM ANALYZE the database will run slow.
Does this ring true?
Does pg_restore not update the query planner statistics?
--
Sent via pgsql-sql mailing list (pgsql-sq
Tom Lane wrote:
Does pg_restore not update the query planner statistics?
Correct. Autovacuum might fix them eventually, but usually it's
worth forcing the issue once you've completed your data loading.
(This might involve multiple steps, which is why pg_restore
doesn't try t
Dear experts,
This point is confusing me with the || operator. I've got a table with
"one column per data type", like so:
# \d context_keyvals;
Table "public.context_keyvals"
Column|Type | Modifiers
-+-+---
Craig Ringer wrote:
On 24/12/2009 5:04 AM, Rosser Schwarz wrote:
On Wed, Dec 23, 2009 at 1:44 AM, Craig Ringer
wrote:
Your invocation of COALESCE is incorrect -- it is n-ary, but it
returns its first non-NULL argument.
Yes. That was the point.
I think we're assuming the OP wants different th
I'm setting up remote monitoring of postgres, but running into an
uncomfortable situation with permissions.
Basically it seems hard to set up a secure "read only" role, yet also
allow proper monitoring.
A brief writeup of that is here:
http://help.logicmonitor.com/installation-getting-started/n
If I create a new table the "Access privileges" are blank, which gives
implicit access to the table owner.
But if I touch the grants, then revoke, the privileges are set to {}
which is not the same thing:
production=> create table zzz_junk1 ();
production=> \z zzz_junk1
Schema | Name| Ty
On psql 8.3.9, I ran a limited query limited to 5 results. There was a
moderately expensive function call
which I expected to be called 5 times, but was apparently called for
each row of the sequential scan. Why?
preproduction=> explain analyze select url(context_key) from extractq
order b
I have a cluster with log_min_duration_statement set to log slow
queries. Presently what I'm tracking down is almost certainly a lock
problem. Is there any analog of log_min_duration_statement for locks?
If there is a lock on a certain critical tables for more than a few
hundredths of a secon
Dear helpful postgres people;
I've got a database deadlock. The initiating process was " in
transaction" with PID 2219.
Use of pg_cancel_backend() returns true, but does not actually kill the
process.
When this happens I kill the PID at the Unix level, which feels sort of
stupid, like drivi
Michael Glaesemann wrote:
On Jan 4, 2011, at 17:29, Bryce Nesbitt wrote:
Should I expect pg_cancel_backend() to work in a case like this? If not does
this sound like a reportable bug?
pg_cancel_backend() cancels the running query for that backend. In the case
of, there is no
My postmaster won't start. This is on a Fedora Core fc5 box, new and clean.
It's postgres 8.1.4. All I get is "LOG: logger shutting down" in the
pgstartup.log.
I've checked for permission errors.
At the relevant moment I get:
# sh -x /etc/init.d/postgresql start
...
++ cat /var/lib/pgsql/data/
I got bit by this to be sure, but is it a bug? I guess I'd hoped for a
warning at the critical step (see "poof" below):
create table tester (one int, two int, three int);
alter table only tester add constraint no_dupes unique (one, two, three);
insert into tester values(1,2,3);
insert into tester
is
current, this may be part of the battle. Any clues? Is there a way to
get a "test connection" functionality, similar to the MySQL ODBC driver?
-Bryce Nesbitt
--
Visit http://www.obviously.com/
---(end of broadcast)
I need to create some nearly identical copies of rows in a complicated
table.
Is there a handy syntax that would let me copy a existing row, but get a
new primary key for the copy? I'd then go in an edit the 1 or 2
additional columns that differ. The duplicate would be in the same
table as the o
I have a table that describes some properties. It is logically
equivalent to:
pk userid favorite_color time_zone count
122100 red Pacific7
145101 blue Eastern 7
For various reasons I need to manually add a few r
Philip Hallstrom wrote:
>> I need to create some nearly identical copies of rows in a complicated
>> table.
>>
>> Is there a handy syntax that would let me copy a existing row, but get a
>> new primary key for the copy? I'd then go in an edit the 1 or 2
>> additional columns that differ. The dupl
I've got a DELETE FROM that seems to run forever, pegging the CPU at
100%. I can't figure out why it's slow. Any clues?
stage=# EXPLAIN DELETE FROM EG_INVOICE WHERE PERIOD_ID = 1017506;
Index Scan using ix22f7bc70c7de2059 on eg_invoice (cost=0.00..105.39
rows=3955 width=6)
Index Cond: (peri
Tom Lane wrote:
> Bryce Nesbitt <[EMAIL PROTECTED]> writes:
>
>> I've got a DELETE FROM that seems to run forever, pegging the CPU at
>> 100%. I can't figure out why it's slow. Any clues?
>>
>
> Unindexed foreign key constraints point
Dear Postgres folks;
I'm considering using a postgres table for something that could be done
with a flat file. Is this a good idea?
I have events on a machine "A", which need to be sent by an SMS/Cell
Phone modem that's on a totally different machine "B". Potentially this
is a job for a flat fi
All;
Is there a way to get a conditional aggregate? I have this two column view:
SELECT count(*) AS count, xx_plan.plan_name
FROM xx_membership
JOIN xx_account USING (account_id)
JOIN xx_plan USING (plan_id)
WHERE xx_membership.status = 10
GROUP BY xx_plan.plan_name;
And would like
Ah perfect. I was struggling with CASE outside the SUM(), which was not
working.
---(end of broadcast)---
TIP 7: You can help support the PostgreSQL project by donating at
http://www.postgresql.org/about/donate
I have a bunch of data which is expressed in terms of start and stop
dates, e.g.:
Member
Start
Stop
Fred
2007-01-01
2007-05-01
Joe
2005-05-04
2007-04-01
.
Is there a way to get the "where invoice_id=x" into the subquery?
"select distinct order_id from eg_order where invoice_id=x" would do it.
I can't redesign the view, because it all goes into an object relational
mapper that thinks it's a real tabl
Great analysis Gregory & Tom... UNION ALL will make a difference.
---
Here invoices consist of orders, orders consist of order lines. Thus,
each order_id corresponds to just one invoice_id.
One possibility is to add an invoice_i
How do I specify a cast, if the type name has spaces? foo::integer is
easy,
but foo::'timestamp without time zone' is more murky.
In my case I have a table, and a view. For no apparent reason the table
has
timestamp without time zone, but I need timestamp with time zone.
I'm using "select column
One down. Total runtime of the simplest query went from 34661.572 ms to
.634 ms (45,000 times faster).
stage=> explain analyze select * from eg_order_summary_view where
invoice_id=1432655;
QUERY
PLAN
I've got a table of "coupons" which have an expiration date. For each
type of coupon, I'd like to get the primary key of the coupon which will
expire first.
# create table coupon
(
coupon_id serial primary key,
type varchar(255),
expires date
);
insert into coupon values(
Judith wrote:
> Hello everybody!!
>
> I'm trying in SUSE to connect to a postgres db and this is the error:
>
> Ident Authentification failed for user <>
>
> I'm already created the user with createuser root, but the error
> persist, I would aprecciate some help, thanks in advanced
...or just
Expecting to save 4 seconds per query, I built a partial index on a
table, and was surprised that it did not work. Could someone explain
the difference between "foo=false" and "foo is false", for a boolean
type column?
stage=# create index eg_ve_reconciled_partial on eg_vehicle_event
(reconci
Tom Lane wrote:
Bryce Nesbitt <[EMAIL PROTECTED]> writes:
Could someone explain
the difference between "foo=false" and "foo is false", for a boolean
type column?
They give different results for NULL --- specifically, NULL for the
former and FA
This is a reformulation of an earlier question. I've got a confusing
case of a partial index not working. The column in question is a
not-null boolean, which is false only for the most recent entries into
the table.
# explain analyze select count(vehicleeve0_.VEHICLE_EVENT_ID) as x0_0_
from
Richard Huxton provided the answer: It's a prepared query-plan,
which means it can't plan to use the index because the next EXECUTE
might have reconciled=true.
Bryce Nesbitt wrote:
...Which is all good. But the Hibernate version
of query still takes several seconds, and still
I've got a join where a certain column name appears twice:
select username,last_name from eg_member join eg_membership using
(member_id) join eg_account using (account_id) join eg_person using
(person_id);
ERROR: common column name "person_id" appears more than once in left table
My first incli
Hmm. Seemed so simple. But how do I get the number of years an
interval represents? extract is clearly the wrong way:
stage=# select 'now()-'1987-02-01' as interval,extract(year from
now()-'1987-02-01') as age;
interval | age
--+-
7665 days 18:05:51.
Sigh. Ok, I settled on:
select '1987-01-29'::timestamp + interval '21 years'
> now();
Which is closer to what I wanted anyway (this was all about determining
who was under 21 years old). This at least should be robust over leap
years.
Tom Lane wrote:
There is nothing simple about datetim
seems somewhat on target. I am using postgres, in a
mostly database independent manner.
Bryce Nesbitt
http://www.citycarshare.org/
---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
I'm getting the dreaded psql warning:
WARNING: You are connected to a server with major version 8.1,
but your psql client is major version 8.2. Some backslash commands,
such as \d, might not work properly.
And indeed, most stuff works, but \d for a specific table fails. Has
there been ta
(because our legacy application, which won't change, is using the
underlying tables. We can't do step #5).
Bryce Nesbitt wrote:
Yes, the view approach has some advantages. But it still leaves the
underlying tables naked to modification.
And since the most likely error is...
Yes, the view approach has some advantages. But it still leaves the
underlying tables naked to modification.
And since the most likely error is... well... me (or another admin) at
the SQL prompt, we want underlying tables protected also.
chester c young wrote:
> instead of triggers I use update-a
I'm running SUSE 10.3. I have to install something behind the back of
the package manager to keep two versions of psql around. Its not the
end of the world, but it's a hassle... and fiddly in it's own right. A
8.2/8.3 client that could talk to 8.1 would save hassle here.
Richard Huxton wrote:
>
I'm expecting COLUMN comments to work much like table comments, but I'm
getting nothing back. Is this a reportable bug, or a misunderstanding?
# comment on column sched.days is 'Bitmask 0=Sunday,7=Holiday, 8=School
day, 9=Special';
COMMENT
# \dd sched.days;
Schema | Name | Object | Description
-
latforms, and others find it useful, I'd like to learn how and
where to introduce it as a patch. Any tips are appreciated.
This is intended to solve that annoying case where a few rows in a
column are too long, blowing past the widest window you can fit onscreen.
-Bryce Nesbitt
-
Karsten Hilbert wrote:
# \pset format aligned-wrapped
# \pset border 2
# select * from distributors order by did;
+--++-+---+
| did |name|descr| long_col_name |
+--++--
Is there a clean way in Postgres to specify a default return value, if a
column does not exist in a database? In pseudocode:
select p_email,
CASE WHEN EXISTS("p_email_alt") THEN p_email_alt ELSE 'none' END
from eg_application;
I can kind of almost get there with:
select p_email,
CASE WHEN EXI
Dear Postgres Gurus;
Is there a way to truncate a table, at pg_dump time?
I'm aware of various ways to exclude a table from a dump (>= 8.2), or to
selectively pg_restore. What I'm seeking here is different. I've got
tables with pretty disposable data... meaning I want to drop the data...
but re
chris smith wrote:
On Sat, Oct 25, 2008 at 5:32 PM, Bryce Nesbitt <[EMAIL PROTECTED]> wrote:
I've got
tables with pretty disposable data... meaning I want to drop the data...
but restore empty indexed tables at pg_restore time.
Do a schema-only dump.
pg_
Dear Helpful People,
I'm getting a bunch of:
2008-10-25 14:36:59 PDT ERROR: syntax error at or near "SET" at character 9
2008-10-25 14:36:59 PDT ERROR: syntax error at or near "fetch" at
character 9
2008-10-25 14:36:59 PDT ERROR: current transaction is aborted, commands
ignored until end of tr
I'm a bit confused why the query planner is not restricting my join, and
not using the index. Two explain analyze statements follow.
Why is the second so much better?
lyell5=> select version();
PostgreSQL 8.3.4 on x86_64-pc-linux-gnu, compiled by GCC cc (GCC) 4.1.2
20061115 (prerelease) (Debian 4
Dear Postgres Folks,
I'm a bit confused why the query planner is not restricting my join, and
not using the index.
Two explain analyze statements follow. Why is the second so much better?
lyell5=> vacuum analyze;
lyell5=> select version();
PostgreSQL 8.3.4 on x86_64-pc-linux-gnu, compiled by GCC
Tom Lane wrote:
Why are the rowcount estimates so far off? Maybe you need to increase
the statistics target for this table.
regards, tom lane
Tom,
How does one tell the rowcount is off in a query plan? I've never
found a great reference on interpreting the query analyze output!
Upp
I've got a table for which "CLUSTER tablename USING index" makes an
order of magnitude difference.
Are there ways to determine how "unclustered" this table becomes over
time, so I can schedule downtime to recluster? I could use the pg_stat
tables, but this seems awkward.
-Bry
Dear Experts,
I'm looking for a good technique to do "and" searches on one-to-many
joined tables. For example, to find people with both 'dark hair' and
'president':
# select * from test_people join test_attributes using (people_id);
+---+-+---+
| people_id | p
It works (with a DISTINCT clause added because of the duplicated row
for Obama). It has a nice clean looking explain plan. It has the
slowest execution time on this sample table (though that might not mean
anything).
SELECT
DISTINCT
person_name
FROM test_people p
JOIN test_attributes a
ON ((
Milan Oparnica wrote:
> This is how I do it, and it runs fast:
> select p.*
> from test_people p inner join test_attributes a on p.people_id =
> a.people_id
> where a."attribute" = @firstAttr or a."attribute" = @secondAttr
But that does an "or" search, not "and", returning Satan in addition to
Ob
I've got a bunch of tables in a legacy database that I know are
never used, and some more I'm not sure about. So I tried to
identify and confirm with:
select pg_stat_reset();
-- Wait a long time
select * from pg_stat_all_tables where schemaname='public' order by
seq_scan,seq_tup_read
1 - 100 of 111 matches
Mail list logo
