Re: [SQL] Q: performance on some selects (7.0.2)?
Emils, There is no index on articles.id (should it not be a primary key?) Regards, Grant Emils Klotins wrote: > A typical query runs like this: > > SELECT a.id,a.title,c.fullpath,c.section FROM articles > a,articles_groups x,newscategories c WHERE x.articleid=a.id AND > a.categoryid=c.id AND x.groupid='9590' AND a.status=1 AND > timestamp(a.publishdate,a.publishtime)<'now'::datetime ORDER > BY a.createddate desc,a.createdtime desc LIMIT 3 > > Explain says: > > NOTICE: QUERY PLAN: > > Sort (cost=171.93..171.93 rows=1 width=56) > -> Nested Loop (cost=0.00..171.92 rows=1 width=56) > -> Nested Loop (cost=0.00..169.95 rows=1 width=36) > -> Seq Scan on articles_groups x (cost=0.00..12.10 > rows=1 width=4) > -> Seq Scan on articles a (cost=0.00..135.55 rows=636 > width=32) > -> Seq Scan on newscategories c (cost=0.00..1.43 rows=43 > width=20) > > EXPLAIN > > Now, as I understand the thing that slows everything is the Seq > scan on articles. I wonder why should it be that the query can't use > index? > > TIA! > Emils -- > Poorly planned software requires a genius to write it > and a hero to use it. Grant Finnemore BSc(Eng) (mailto:[EMAIL PROTECTED]) Software Engineer Universal Computer Services Tel (+27)(11)712-1366PO Box 31266 Braamfontein 2017, South Africa Cell (+27)(82)604-553620th Floor, 209 Smit St., Braamfontein Fax (+27)(11)339-3421Johannesburg, South Africa
Re: [SQL] renaming columns... danger?
Just tested this on latest devel. version, and there does seem to be a
problem.
[]$ psql test
Welcome to psql, the PostgreSQL interactive terminal.
Type: \copyright for distribution terms
\h for help with SQL commands
\? for help on internal slash commands
\g or terminate with semicolon to execute query
\q to quit
test=# select version();
version
PostgreSQL 7.1devel on i686-pc-linux-gnu, compiled by GCC egcs-2.91.66
(1 row)
test=# create table a ( aa serial primary key );
NOTICE: CREATE TABLE will create implicit sequence 'a_aa_seq' for
SERIAL column 'a.aa'
NOTICE: CREATE TABLE/PRIMARY KEY will create implicit index 'a_pkey'
for table 'a'
CREATE
test=# alter TABLE a RENAME aa to new_aa;
ALTER
[]$ pg_dump test
--
-- Selected TOC Entries:
--
\connect - gaf
--
-- TOC Entry ID 2 (OID 20352)
--
-- Name: "a_aa_seq" Type: SEQUENCE Owner: gaf
--
CREATE SEQUENCE "a_aa_seq" start 1 increment 1 maxvalue 2147483647
minvalue 1 cache 1 ;
--
-- TOC Entry ID 4 (OID 20370)
--
-- Name: a Type: TABLE Owner: gaf
--
CREATE TABLE "a" (
"new_aa" integer DEFAULT nextval('"a_aa_seq"'::text) NOT NULL,
PRIMARY KEY ("aa")
);
--
-- Data for TOC Entry ID 5 (OID 20370) TABLE DATA a
--
-- Disable triggers
UPDATE "pg_class" SET "reltriggers" = 0 WHERE "relname" ~* 'a';
COPY "a" FROM stdin;
\.
-- Enable triggers
BEGIN TRANSACTION;
CREATE TEMP TABLE "tr" ("tmp_relname" name, "tmp_reltriggers" smallint);
INSERT INTO "tr" SELECT C."relname", count(T."oid") FROM "pg_class" C,
"pg_trigger" T WHERE C."oid" = T."tgrelid" AND C."relname" ~* 'a' GROUP
BY 1;
UPDATE "pg_class" SET "reltriggers" = TMP."tmp_reltriggers" FROM "tr"
TMP WHERE "pg_class"."relname" = TMP."tmp_relname";
DROP TABLE "tr";
COMMIT TRANSACTION;
--
-- TOC Entry ID 3 (OID 20352)
--
-- Name: "a_aa_seq" Type: SEQUENCE SET Owner:
--
SELECT setval ('"a_aa_seq"', 1, 'f');
Michael Teter wrote:
> hi.
>
> I just discovered that doing an alter table ... alter
> column (to rename a column) does not do a complete
> rename throughout the database.
>
> for example, say you have table a, with columns b and
> c. b is your primary key.
>
> now rename b to new_b. if you do a dump of the schema
> after you rename, you'll find that you can't reload
> that schema because at the bottom of the definition of
> table a you have PRIMARY KEY ("b").
>
> shouldn't rename update any index and key definitions?
>
> also, and this may actually the source of the problem,
> while scanning my full (schema and data) dump, I
> noticed that the contents of table pga_layout also had
> the old values of columns that I have renamed.
>
> I'm very frightened right now, because I'm rather
> dependent upon my database right now. I don't like
> the thought that my database is corrupt at the schema
> level.
>
> michael
>
> __
> Do You Yahoo!?
> Yahoo! Messenger - Talk while you surf! It's FREE.
> http://im.yahoo.com/
--
> Poorly planned software requires a genius to write it
> and a hero to use it.
Grant Finnemore BSc(Eng) (mailto:[EMAIL PROTECTED])
Software Engineer Universal Computer Services
Tel (+27)(11)712-1366PO Box 31266 Braamfontein 2017, South Africa
Cell (+27)(82)604-553620th Floor, 209 Smit St., Braamfontein
Fax (+27)(11)339-3421Johannesburg, South Africa
Re: [SQL] Outer Joins
Marc, I did not look at your queries, but outer joins are supported in the latest development version of PostgreSQL, and will be supported in the upcoming 7.1 release of the software - beta due soon. Regards, Grant Marc Rohloff wrote: > I've been looking at the open-source databases for a project I am working on and >while reading about Postgres I saw that they do not support outer joins yet. I was >intrigued by their solution of using a union query. > > Something Like: > select a.col1, b.col2 from a,b where a.col1 = b.col2 > union > select a.col1, NULL from a where a.col1 not in (select b.col2 from b) > > But I was wondering if the following would work (it does in some other databases) > > select a.col1, b.col2 from a,b > where a.col1 = b.col2 >or b.col2 is null > > or maybe even > > select a.col1, b.col2 from a,b > where a.col1 = b.col2 >or a.col1 not in (select b.col2 from b) > > These would seem to be far more efficient than a union query > (I would try this but I don't have a Unix box at the moment to install PostgreSQL >on!) > > Marc Rohloff -- > Poorly planned software requires a genius to write it > and a hero to use it. Grant Finnemore BSc(Eng) (mailto:[EMAIL PROTECTED]) Software Engineer Universal Computer Services Tel (+27)(11)712-1366PO Box 31266 Braamfontein 2017, South Africa Cell (+27)(82)604-553620th Floor, 209 Smit St., Braamfontein Fax (+27)(11)339-3421Johannesburg, South Africa
Re: [SQL] Returning Recordsets from Stored-procs
Marc, Marc Rohloff wrote: > Is there anyway to return a recordset from a Stored Procedure in Postgres so that it >can be used as a type of view or select? In short:- No, there isn't. More detail:- PostgreSQL does not have stored procedures as such, it has user defined functions. The difference being that a stored procedure returns both a scalar value (the result), and optionally, one or more sets. (your recordset) Functions on the other hand, only return a scalar value. This has been slightly extended in PostgreSQL so that a list of values can be returned, but these have to be of the same type, and so are not a general replacement for a set. Whilst this is an unfortunate position at the moment, it has been my experience that it does not cause insurmountable problems. (Some short term headaches - yes. ;-) > I know that you can do this in Interbase or MS-SQL. > > I have seen that you can return a complete record but that's not really the same >thing. > > Marc Rohloff Regards, Grant -- > Poorly planned software requires a genius to write it > and a hero to use it. Grant Finnemore BSc(Eng) (mailto:[EMAIL PROTECTED]) Software Engineer Universal Computer Services Tel (+27)(11)712-1366PO Box 31266 Braamfontein 2017, South Africa Cell (+27)(82)604-553620th Floor, 209 Smit St., Braamfontein Fax (+27)(11)339-3421Johannesburg, South Africa
Re: [SQL] Rollback & Nextval fails
Fredrik,
> I have been trying the following SQL code :
>
> BEGIN;
> INSERT INTO table VALUES ( NEXTVAL('serial'), 'Data' );
> ROLLBACK;
>
> And the insert function is rolled back but the serial sequence isn't. Hav I
> misunderstood the functionality of rollback or is this a bug? Is there
> someway to get the functionality that rollsback everything?
>
It is not a bug. In order to enable concurrent users access to the nextval()
function, every call to that function will increment the counter. Should a
client rollback, we cannot re-use the number(s) allocated to them, as other
clients might already have been allocated higher numbers. This means that a
full table scan would be required to allocate "blank" numbers inside the
sequence. Even this would not solve the issue of contiguous numbering in the
table, as at any point in time, "blanks" might exist.
If you *really* need a sequence with no unused numbers, you might consider
creating a table using a single row as the current sequence number. Use either
SQL or a stored proc. to lock the row and increment the value for the counter
on each next value that you require. This has the effect of serializing every
client update transaction where this scheme is used. Can you afford that?
Regards,
Grant
--
> Poorly planned software requires a genius to write it
> and a hero to use it.
Grant Finnemore BSc(Eng) (mailto:[EMAIL PROTECTED])
Software Engineer Universal Computer Services
Tel (+27)(11)712-1366PO Box 31266 Braamfontein 2017, South Africa
Cell (+27)(82)604-553620th Floor, 209 Smit St., Braamfontein
Fax (+27)(11)339-3421Johannesburg, South Africa
Re: [SQL] adding fields containing NULL values
Werner, > Umfortunately 'select employee,date,sallary+extras as total' doesn't give the > desired result because 'somevalue + NULL' is considered to be NULL. > Is there any solution for my (small) problem? Try SELECT employee, date, salary + COALESCE(extras, 0) as total FROM ... Regards Grant -- > Poorly planned software requires a genius to write it > and a hero to use it. Grant Finnemore BSc(Eng) (mailto:[EMAIL PROTECTED]) Software Engineer Universal Computer Services Tel (+27)(11)712-1366PO Box 31266 Braamfontein 2017, South Africa Cell (+27)(82)604-553620th Floor, 209 Smit St., Braamfontein Fax (+27)(11)339-3421Johannesburg, South Africa
Re: [SQL] trigger or something else?
> I suppose you can use view for your need. For example: > ... > Unfortunately this way suits for select only, not for 'insert into b' > and 'update b' statement. > Except that you can use rules to update/insert data into tables when an insert/update is done on the view. See the docs for details on how to do this. > > -- > Anatoly K. Lasareff Email: [EMAIL PROTECTED] Grant -- > Poorly planned software requires a genius to write it > and a hero to use it. Grant Finnemore BSc(Eng) (mailto:[EMAIL PROTECTED]) Software Engineer Universal Computer Services Tel (+27)(11)712-1366PO Box 31266 Braamfontein 2017, South Africa Cell (+27)(82)604-553620th Floor, 209 Smit St., Braamfontein Fax (+27)(11)339-3421Johannesburg, South Africa
