Re: [PERFORM] Fixed width rows faster?
> This is bogus reasoning. The limit on index entry length will not > change when you rebuild the index. What I meant by 'rebuilding' was not issuing a REINDEX command, but creating a new index after having dropped the index and inserted whatever records. Building indexes can be slow, and I'd rather not have the operation fail after several hours because record #98556761 is deemed to be too long for indexing... While we are busy complaining, it's a pity Postgres doesn't allow us to disable and later recreate all indexes on a table using a single command ;-) ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [PERFORM] Using bigint needs explicit cast to use the index
On Mon, Mar 08, 2004 at 10:26:21AM +1000, Steven Butler wrote: > I tested my hunch by casting the constant to bigint (as can be seen below) > and suddenly the query is using the index again. Yes. You can make this work all the time by quoting the constant. That is, instead of WHERE indexcolumn = 123 do WHERE indexcolumn = '123' > We are currently using pg 7.3.4. Is this intended behaviour? Should the > constant be cast to the type of the table column where possible, or should "Intended", no. "Expected", yes. This topic has had the best Postgres minds work on it, and so far nobody's come up with a solution. There was a proposal to put in a special-case automatic fix for int4/int8 in 7.4, but I don't know whether it made it in. A -- Andrew Sullivan | [EMAIL PROTECTED] I remember when computers were frustrating because they *did* exactly what you told them to. That actually seems sort of quaint now. --J.D. Baldwin ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [PERFORM] Using bigint needs explicit cast to use the index
Andrew Sullivan wrote: "Intended", no. "Expected", yes. This topic has had the best Postgres minds work on it, and so far nobody's come up with a solution. Actually, this has already been fixed in CVS HEAD (as I mentioned in this thread yesterday). To wit: nconway=# create table t1 (a int8); CREATE TABLE nconway=# create index t1_a_idx on t1 (a); CREATE INDEX nconway=# explain select * from t1 where a = 5; QUERY PLAN Index Scan using t1_a_idx on t1 (cost=0.00..17.07 rows=5 width=8) Index Cond: (a = 5) (2 rows) nconway=# select version(); version PostgreSQL 7.5devel on i686-pc-linux-gnu, compiled by GCC gcc (GCC) 3.3.3 (Debian) (1 row) -Neil ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [PERFORM] Using bigint needs explicit cast to use the index
On Mon, Mar 08, 2004 at 11:05:25 -0500, Andrew Sullivan <[EMAIL PROTECTED]> wrote: > > "Intended", no. "Expected", yes. This topic has had the best > Postgres minds work on it, and so far nobody's come up with a > solution. There was a proposal to put in a special-case automatic > fix for int4/int8 in 7.4, but I don't know whether it made it in. This is handled better in 7.5. Instead of doing things deciding what types of type conversion to do, a check is make for cross type conversion functions that could be used for an index scan. This is a general solution that doesn't result in unexpected type conversions. ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [PERFORM] Using bigint needs explicit cast to use the index
On Mon, Mar 08, 2004 at 11:22:56AM -0500, Neil Conway wrote: > Actually, this has already been fixed in CVS HEAD (as I mentioned in > this thread yesterday). To wit: Yes, I saw that after I sent my mail. What can I say except, "Yay! Good work!" A -- Andrew Sullivan | [EMAIL PROTECTED] This work was visionary and imaginative, and goes to show that visionary and imaginative work need not end up well. --Dennis Ritchie ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [PERFORM] Feature request: smarter use of conditional indexes
Tom Lane wrote: > Larry Rosenman <[EMAIL PROTECTED]> writes: > > Just a suggestion, please use diff -c format, as it makes it easier for > > the folks who apply the patches to do so. > > That's not just a suggestion ... patches that aren't in diff -c (or at > least diff -u) format will be rejected out of hand. Without the context > lines provided by these formats, applying a patch is an exercise in > risk-taking, because you can't be certain that you are applying the same > patch the submitter intended. Also, when you get 'fuzz' output when applying the patch, you should review the patch to make sure it appeared in the right place. That has gotten me a few times. -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [PERFORM] simple query join
Title: Message
Eek.
Casting both to varchar makes it super quick so I'll
fix up the tables.
Added to the list of things to check for next
time...
On a
side note - I tried it with 7.4.1 on another box and it handled it
ok.
Thanks again :)
Chris.
-Original Message-From: Steven Butler
[mailto:[EMAIL PROTECTED] Sent: Monday, March 08, 2004 6:12
PMTo: Chris Smith;
[EMAIL PROTECTED]Subject: Re: [PERFORM] simple query
join
Looks to me like it's because your assetid is
varchar in one table and an integer in the other table. AFAIK, PG is
unable to use an index join when the join types are different. The query
plan shows it is doing full table scans of both tables.
Change both to varchar or both to integer and see
what happens.
Also make sure to vacuum analyze the tables
regularly to keep the query planner statistics up-to-date.
Cheers,
Steve Butler
assetid
|
integer
| not null default 0Indexes: sq_asset_pkey primary key btree
(assetid)
assetid | character varying(255) | not null
default '0'EXPLAIN ANALYZE SELECT p.*FROM sq_asset a,
sq_asset_permission pWHERE a.assetid = p.assetidAND p.permission =
'1'AND p.access = '1'AND p.userid =
'0';
QUERY
PLAN Nested
Loop (cost=0.00..4743553.10 rows=2582 width=27) (actual
time=237.91..759310.60 rows=11393 loops=1) Join Filter:
(("inner".assetid)::text = ("outer".assetid)::text)
-> Seq Scan on sq_asset_permission p (cost=0.00..1852.01
rows=2288 width=23) (actual time=0.06..196.90 rows=12873
loops=1) Filter:
((permission = 1) AND ("access" = '1'::bpchar) AND (userid = '0'::character
varying)) -> Seq Scan on sq_asset a
(cost=0.00..1825.67 rows=16467 width=4) (actual time=1.40..29.09 rows=16467
loops=12873) Total runtime: 759331.85 msec(6
rows)
