RE: [SQL] primary key scans in sequence
actually the serials are declared int4 (integer),
I tried to use your work around anyway, but it didn't work...:
dsc_competition=# \d classes
Table "classes"
Attribute | Type | Modifier
+-+-
-
id | integer | not null default nextval('classes_id_seq'::text)
name | text| not null
definition | text|
active | boolean | default 't'::bool
Indices: classes_pkey,
index_classes_name,
unq_classes_name
dsc_competition=# explain select * from classes where id = int8(4);
NOTICE: QUERY PLAN:
Seq Scan on classes (cost=0.00..1.07 rows=1 width=29)
EXPLAIN
dsc_competition=# explain select * from classes where id = int4(4);
NOTICE: QUERY PLAN:
Seq Scan on classes (cost=0.00..1.07 rows=1 width=29)
EXPLAIN
dsc_competition=# explain select * from classes where id = int2(4);
NOTICE: QUERY PLAN:
Seq Scan on classes (cost=0.00..1.07 rows=1 width=29)
EXPLAIN
Thing I descovered after i posted to the group was that after creating
the scheme again, the indexes are used! after vacuum (analyze) the use
of indexes was gone again on certain tables...
Any other suggestions?
-Original Message-
From: bernd pinter [mailto:[EMAIL PROTECTED]]
Sent: donderdag 31 mei 2001 8:53
To: Koen Antonissen
Subject: Re: [SQL] primary key scans in sequence
the problem is the optimizer.
you use a int8 as primary key.
so if you do somthing like
"select * from sponsors where id = 34;"
then the optimizer interprets the 34 as an int4 => id is int8, so
postgres cant use the primary
key-index.
the workaround is simple. say that you are lookiong for an int8 (instead
of an int4):
"select * from sponsors where id = int8(34);"
thats it! now you convert 34 to an int8, postgres can use the
index..
bernd.
Koen Antonissen wrote:
> I have the same problem, my primary key is defined as a serial though.
> Other tables use tables are defined as serials as well, but DO use
Index
> Scans
> some tables do, some tables don't, even when creating 'my own' index
on
> the primary key, it still uses sequencial scans!
>
>
> This one works fine:
> Table "sponsors"
> Attribute | Type | Modifier
>
>
---+-+--
> ---
> id| integer | not null default
nextval('sponsors_id_seq'::text)
> name | text| not null
> email | text|
> logo | text| not null default
> 'images/sponsors/logo_default.gif'
> url | text|
> qoute | text|
> active| boolean | default 't'::bool
> main | boolean | default 'f'::bool
> Indices: index_sponsors_main,
> index_sponsors_name,
> sponsors_pkey,
> unq_sponosrs_name
>
> dsc_competition=# explain select * from sponsors where id = 4;
> NOTICE: QUERY PLAN:
>
> Index Scan using sponsors_pkey on sponsors (cost=0.00..2.01 rows=1
> width=66)
>
> EXPLAIN
>
> Now this one doesn't:
> Table "teams"
> Attribute | Type | Modifier
>
---+-+--
> id| integer | not null default nextval('teams_id_seq'::text)
> name | text| not null
> mgr_name | text|
> address | text|
> zipcode | text|
> city | text|
> country | text|
> email | text|
> telnr | text|
> mobnr | text|
> faxnr | text|
> logo | text| not null default
'images/teams/logo_default.gif'
> movie | text|
> url | text|
> qoute | text|
> active| boolean | default 't'::bool
> Indices: index_teams_id, <=!!! 'my own' index
> index_teams_name,
> teams_pkey, <=normal pkey index
> unq_teams_name
>
> NOTICE: QUERY PLAN:
>
> Seq Scan on teams (cost=0.00..1.09 rows=1 width=173)
>
> EXPLAIN
>
>
> I really don't understand the difference between the two, and it
didn't
> work before i created an extra index on id...
>
> Kind regards,
> Koen Antonissen
>
>
> -Original Message-
> From: Richard Poole [mailto:[EMAIL PROTECTED]]
> Sent: vrijdag 30 maart 2001 18:12
> To: bernd
> Cc: [EMAIL PROTECTED]
> Subject: Re: [SQL] primary key scans in sequence
>
>
> Because the type of the "mitgliedid" is "bigint", but the type of the
> constant "833228" is "integer" (I think; certainly it isn't "bigint").
> Postgres doesn't realise that it can use an index on a bigint to do
> comparisons to an integer. If you explicitly cast the constant to a
> bigint, it should be willing to do an index scan, like so:
>
> select * from mitglied where mitgliedid = 833228::bigint
>
> Yes, this is a bit unpleasant to have to in your client code, and no,
> I don't know if there's a neater way to let Postgres know it can use
> this index for this
[SQL] question about PL/pgSQL function
With postgres 7.1 1. Function like example found on 24.4 Example in Programmer Guide CREATE FUNCTION xxx (EMP, integer) RETURNS boolean AS'.. (which EMP is a table ) How do I pass a record to this function in PL/PGSQL? I try this select into rec1 * from EMP where empNo =''''; i1 := xxx(rec1,5); But it won't work! 2. Is that possible to return a record from a function? And how do I get the returned record in PL/pgSql JACK ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[SQL] 7.1.1 Lock Problems on Views
Greetings, I am investigating whether our application will run on 7.1.1 (from 7.0.3), and our client software only accesses the database through views for security and convenience. In our client app, we lock our table before doing something to it. Fortunately, in 7.0.3, a view looked like a table and we could perform: lock v_whatever in share row exclusive mode; However, in 7.1.1, it comes back and tells me that: ERROR: LOCK TABLE: v_whatever is not a table The HUGE advantage in 7.0.3 was that we could present our db through views and the client didn't have to know. I tried to put the lock statements into the rule of each view when the rule does the actual work, such as: create rule v_whatever_insert as on insert to v_whatever do instead ( lock whatever in share row exclusive mode; insert into whatever (blah) values (new.blah); ); However, the view won't be created, it claims there is a syntax error. So, with 7.1.1, how can I create views and lock the data when modifying the table? Thanks, --Brian ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
RE: [SQL] primary key scans in sequence
It really depends on the number of rows. If the number of
rows in the tables are small or the number of rows returned is
a reasonable percentage, the index scan is currently more expensive.
What does (for example) select count(*) from classes; give?
On Thu, 31 May 2001, Koen Antonissen wrote:
> actually the serials are declared int4 (integer),
> I tried to use your work around anyway, but it didn't work...:
>
> dsc_competition=# \d classes
> Table "classes"
> Attribute | Type | Modifier
>
> +-+-
> -
> id | integer | not null default nextval('classes_id_seq'::text)
> name | text| not null
> definition | text|
> active | boolean | default 't'::bool
> Indices: classes_pkey,
> index_classes_name,
> unq_classes_name
>
> dsc_competition=# explain select * from classes where id = int8(4);
> NOTICE: QUERY PLAN:
>
> Seq Scan on classes (cost=0.00..1.07 rows=1 width=29)
>
> EXPLAIN
> dsc_competition=# explain select * from classes where id = int4(4);
> NOTICE: QUERY PLAN:
>
> Seq Scan on classes (cost=0.00..1.07 rows=1 width=29)
>
> EXPLAIN
> dsc_competition=# explain select * from classes where id = int2(4);
> NOTICE: QUERY PLAN:
>
> Seq Scan on classes (cost=0.00..1.07 rows=1 width=29)
>
> EXPLAIN
>
>
>
> Thing I descovered after i posted to the group was that after creating
> the scheme again, the indexes are used! after vacuum (analyze) the use
> of indexes was gone again on certain tables...
>
> Any other suggestions?
---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[SQL] Re: rowset Return from postgresql
AKM,
> I know you have an workaround idea to retrieve rowset (Resultset
> in
> JDBC) from PostGres function call in 7.1. I was looking at mailing
> archive
> to find but was not successful. If you please help me out stating
> your
> ideas, I will appreciate that.
I can understand that. The "search" function for the list archives
seems to be rather non-functional.
Folks, is there anything we can do to enhance/fix the archive search?
Hub.org people? Right now I'm keeping my own archive so that I can
search it through Netscape!
The answer to your question, in a simplified form for using PL/pgSQL
functions to return rowsets for complex searches:
1. Create a sequence "search_sq"
2. Create a table searches (
search_sq INT4,
key_no INT4,
CONSTRAINT PRIMARY KEY search_PK (search_sq, key_no) );
3. Create a view (sv_results) that defines the column set you want to
see.
4. Create a function ("search_it") that:
a. grabs the s_key := Nextval('search_sq'),
b. INSERT INTO searches SELECT s_key, key_no from sv_results
WHERE ... (dynamically defined criteria)
c. Return the s_key from the function.
5. Query from you front-end app on sv_results JOIN searches where
search_sq = s_key.
This works quite well, especially for web applications.
-Josh
__AGLIO DATABASE SOLUTIONS___
Josh Berkus
Complete information technology [EMAIL PROTECTED]
and data management solutions (415) 565-7293
for law firms, small businessesfax 621-2533
and non-profit organizations. San Francisco
---(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: [SQL] 7.1.1 Lock Problems on Views
Brian Powell <[EMAIL PROTECTED]> writes: > In our client app, we lock our table before doing something to it. Why don't you redesign the app to not use table-level locks? An MVCC-aware app should have little or no need for table-level locking. Locking views strikes me as a pretty fragile, if not outright broken, approach anyway --- a lock on a view would only protect you against other users of the same view, not against other users accessing the same underlying tables through different views. regards, tom lane ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] 7.1.1 Lock Problems on Views
On Thursday, May 31, 2001, at 10:42 AM, Tom Lane wrote: > Why don't you redesign the app to not use table-level locks? > An MVCC-aware app should have little or no need for table-level > locking. > Thanks, I'll read up on MVCC in the docs. While digging around, I came across the MVCC and it was the first I have heard of it (I haven't been reading up on the new features of 7.1)... --Brian ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[SQL] Date manipulation
How does one perform date manipulation within SQL? For example, SQL Server has a dateadd() function that takes a date part, scalar, and the date to manipulate. I have a query that determines the number of days that are between now and a particular date that looks something like this: select datediff (dd, now (), column) as difference_in_days from ... Anything available short of coding my own function? Thanks, Mark ---(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: [SQL] primary key scans in sequence
I think you're just witnessing the optimizer at work. If it thinks that doing sequential scans is faster, it will ignore the indices. At 11:03 AM 5/31/2001 +0200, Koen Antonissen wrote: >Thing I descovered after i posted to the group was that after creating >the scheme again, the indexes are used! after vacuum (analyze) the use >of indexes was gone again on certain tables... > >Any other suggestions? ---(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
