[SQL] Optimizing view
Hello. I'm running `PostgreSQL 7.2 on i586-pc-linux-gnu, compiled by GCC 2.95.3' and have some problems with queries on a view. I've got two tables: working one and archive and one view, I've attached the description. And here is my problem: queries on a view runs to much time. explain analyze select status, target, attempts from messages_all where message_id = 21823; NOTICE: QUERY PLAN: Subquery Scan messages_all (cost=2410.78..2887.07 rows=1270 width=210) (actual time=5946.24..5948.41 rows=1 loops=1) -> Unique (cost=2410.78..2887.07 rows=1270 width=210) (actual time=4567.78..5765.08 rows=12857 loops=1) -> Sort (cost=2410.78..2410.78 rows=12701 width=210) (actual time=4567.75..5043.40 rows=12857 loops=1) -> Append (cost=0.00..764.01 rows=12701 width=210) (actual time=0.62..2346.45 rows=12857 loops=1) -> Subquery Scan *SELECT* 1 (cost=0.00..287.17 rows=917 width=210) (actual time=0.61..229.99 rows=1073 loops=1) -> Seq Scan on messages (cost=0.00..287.17 rows=917 width=210) (actual time=0.52..157.78 rows=1073 loops=1) -> Subquery Scan *SELECT* 2 (cost=0.00..476.84 rows=11784 width=207) (actual time=3.13..2037.05 rows=11784 loops=1) -> Seq Scan on messages_archive (cost=0.00..476.84 rows=11784 width=207) (actual time=3.04..1216.87 rows=11784 loops=1) Total runtime: 6101.58 msec EXPLAIN Too lo-o-ong for 13 000 tuples. When I UNION 2 queries from this tables, query is pretty fast: explain analyze select status, target, attempts from messages where message_id = 21823 union select status, target, attempts from messages_archive where message_id = 21823; NOTICE: QUERY PLAN: Unique (cost=7.34..7.36 rows=1 width=19) (actual time=224.32..224.34 rows=1 loops=1) -> Sort (cost=7.34..7.34 rows=2 width=19) (actual time=224.31..224.31 rows=1 loops=1) -> Append (cost=0.00..7.33 rows=2 width=19) (actual time=128.29..205.15 rows=1 loops=1) -> Subquery Scan *SELECT* 1 (cost=0.00..4.24 rows=1 width=19) (actual time=128.27..128.47 rows=1 loops=1) -> Index Scan using messages_primary on messages (cost=0.00..4.24 rows=1 width=19) (actual time=128.23..128.42 rows=1 loops=1) -> Subquery Scan *SELECT* 2 (cost=0.00..3.10 rows=1 width=19) (actual time=76.65..76.65 rows=0 loops=1) -> Index Scan using messages_archive_id on messages_archive (cost=0.00..3.10 rows=1 width=19) (actual time=76.63..76.63 rows=0 loops=1) Total runtime: 225.35 msec EXPLAIN I've read manual on REWRITE RULES, but I can't get, how to create RULE to keep the WHERE clause and separate one query into 2, and union them after. Any suggestions? -- Victor Yegorov CREATE TABLE messages ( message_id INT4NOT NULL, target VARCHAR(99), client_id INT NOT NULL, content VARCHAR(999), time_in TIMESTAMP NOT NULL, time_outTIMESTAMP, time_gotTIMESTAMP, status INT NOT NULL, attemptsINT NOT NULL, priorityINT NOT NULL DEFAULT 0, notify INT, device INT, delay INT, validityINT NOT NULL DEFAULT -1, route_idINT, CONSTRAINT messages_primary PRIMARY KEY(message_id), CONSTRAINT messages_client_id FOREIGN KEY(client_id) REFERENCES clients(client_id), CONSTRAINT messages_status FOREIGN KEY(status) REFERENCES mstatus(status) ); CREATE TABLE messages_archive ( message_id INT4NOT NULL, target VARCHAR(99), client_id INT NOT NULL, content VARCHAR(999), time_in TIMESTAMP NOT NULL, time_outTIMESTAMP, time_gotTIMESTAMP, status INT NOT NULL, attemptsINT NOT NULL, priorityINT NOT NULL, notify INT, device INT, delay INT, validityINT NOT NULL, route_idINT ); CREATE VIEW messages_all AS SELECT * FROM messages UNION SELECT * FROM messages_archive; pgp0.pgp Description: PGP signature
[SQL] Execution plan Question
hi all,
I have a strange problem and really wish to get some help in here.
I have the following tables
create table shr_objects(
objectidint8not null
companyid int4not null
namevarchar(255)
description varchar(255)
)
primary key : object id
foreign key on companyid references shr_companies(companyid)
Index on companyid
Number of rows ~ 1,410,000
create table smb_contacts{
contactid int4not null
objectidint8not null
firstname varchar(255)
lastnamevarchar(255)
)
primary key : contactid
foreign key on objectid references shr_objects9objectid)
index on : objectid
Number of rows ~ 10,000
I am trying to execute a query that joins the 2 tables on object id , it
works fine but when i add an order clause the performance is degarded
dramatically. I have included both quiries with their excution plan.
Regards,
--ObjectZ Maker
---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] Help on indexing timestamps
On Mon, 10 Mar 2003 10:12:15 +0100
Tomasz Myrta <[EMAIL PROTECTED]> wrote:
> Andre Schubert wrote:
> > Hi all,
> >
> > i have a little problem on indexing a table which contains
> > about 4 millions of traffic-data.
> > My problem is, that a want to select all data from
> > a specific month from a specific ip and this select should use the index.
> > I use the following select:
> >
> > db_nmkm3aue=# explain analyze select sum(inet_up+inet_down) from tbl_traffic where
> > date_trunc('month',tbl_traffic.time_stamp)::timestamptz = date_trunc('month',now()
> > - timespan('1 months')) and ip = '80.243.38.57';
> > NOTICE: QUERY PLAN:
> >
> > Aggregate (cost=116.30..116.30 rows=1 width=16) (actual time=1620.79..1620.79
> > rows=1 loops=1)
> > -> Index Scan using idx_ip_time_stamp on tbl_traffic (cost=0.00..116.30 rows=1
> > width=16) (actual time=1216.79..1579.89 rows=5232 loops=1)
> > Total runtime: 1620.94 msec
> >
> > But it takes a long time to select the traffic for all Ips.
> > Is there a way to select these data with using the index correctly ?
> >
> > Thanks in advance
> >
> I have one more solution - try to rewrite your where clause to NOT USE
> function on time_stamp. If your query will look like:
> select ... where time_stamp between (function with now() returning first
> day) and (function with now() returning last day);
> your index will work fine.
>
Thanks for the hint, i will test this and report to this list.
Thanks, as
---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] explain (internal feature)
Josh Berkus <[EMAIL PROTECTED]> writes: >> db=#explain vacuum; >> to return: >> NOTICE: "vacuum" is an internal postgresql feature. >> instead of: >> ERROR: parser: parse error at or near "vacuum" at character 9 > No, it would not. Actually, it's a fair question considering that the 7.3 man page for EXPLAIN says you can explain "any query". But CVS-tip documentation says that EXPLAIN can explain Any SELECT, INSERT, UPDATE, DELETE, EXECUTE, or DECLARE CURSOR query. which I think is sufficient description. I'm not really eager to enlarge the parser enough to accept EXPLAIN when it's only gonna fail anyway... regards, tom lane ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [SQL] How to notice column changes in trigger
Hello Christoph Haller , > > > > >So you want the trigger executed only if assignments to b and/or c do Do sime thing like this use if /then /esle s block for the problem In the first if blick check that b and c is null by the declaration of IS NULL after that you run the update command as required The program code will show as create function CREATE FUNCTION BLAH_FUNCTION() RETURNS "trigger" AS ' usual blah blah of declre and all , IF NEW.b IS NULL and NEW.C IS NULL THEN ROCK BABY ELSE Do OTHERWISE END IF; more blah blah plpgsql'; CREATE TRIGGER BLAH BEFORE UPDATE ON BLAH_TABLE FOR EACH ROW EXECUTE PROCEDURE BLAH_FUNCTION Please revert back if this helps . Regards V Kashyap > > >not appear within the update command. Right? > > > > > > > Right, that's what I want. > > > I'm afraid I have no idea how to accomplish that. > > Regards, Christoph > > > > ---(end of broadcast)--- > TIP 4: Don't 'kill -9' the postmaster > ---(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] Execution plan Question
Objectz wrote: > Oops .. Here they are > > > = > > intranet=# explain analyze SELECT obj.companyid, obj.name, > obj.description, intranet-# cnt.firstname, cnt.lastname intranet-# FROM > smb_contacts cnt JOIN shr_objects obj ON cnt.objectid = obj.objectid > intranet-# order by obj.companyid intranet-# limit 90; > NOTICE: QUERY PLAN: > > Limit (cost=44459.46..44459.46 rows=90 width=566) (actual > time=14426.92..14427.26 rows=90 loops=1) > -> Sort (cost=44459.46..44459.46 rows=10101 width=566) (actual > time=14426.91..14427.05 rows=91 loops=1) > -> Merge Join (cost=853.84..41938.61 rows=10101 width=566) > (actual time=123.25..14396.31 rows=10101 loops=1) > -> Index Scan using shr_objects_pk on shr_objects obj > (cost=0.00..37386.55 rows=1418686 width=544) (actual time=6.19..11769.85 > rows=1418686 loops=1) > -> Sort (cost=853.84..853.84 rows=10101 width=22) > (actual time=117.02..134.60 rows=10101 loops=1) > -> Seq Scan on smb_contacts cnt (cost=0.00..182.01 > rows=10101 width=22) (actual time=0.03..27.14 rows=10101 loops=1) Total > runtime: 14435.77 msec > > EXPLAIN > > == > intranet=# > intranet=# explain analyze SELECT obj.companyid, obj.name, > obj.description, intranet-# cnt.firstname, cnt.lastname intranet-# FROM > smb_contacts cnt JOIN shr_objects obj ON cnt.objectid = obj.objectid > intranet-# limit 90; > NOTICE: QUERY PLAN: > > Limit (cost=0.00..382.72 rows=90 width=566) (actual time=15.87..25.39 > rows=90 loops=1) > -> Merge Join (cost=0.00..42954.26 rows=10101 width=566) (actual > time=15.86..25.08 rows=91 loops=1) > -> Index Scan using objectid_fk on smb_contacts cnt > (cost=0.00..1869.48 rows=10101 width=22) (actual time=15.76..16.32 > rows=91 loops=1) > -> Index Scan using shr_objects_pk on shr_objects obj > (cost=0.00..37386.55 rows=1418686 width=544) (actual time=0.09..7.81 > rows=193 loops=1) Total runtime: 25.60 msec > > EXPLAIN > > == > It is obvious that in the order by query the company index is not used > and also it had to go thru all records in shr_objects. > Can someone please tell me how is this happening and how to fix it. Well - it's not an "order by" problem, but combination of "order by and limit" Look at your execution plan without order by. Postgres thinks it has to result 1 rows (cost 0.00..1869), but you have "limit 90" and it stops working after 90 rows. It doesn't have more than 200 rows to work. The case with order by is much more complicated. Postgres have to retrieve all 1 rows , sort all of them and after all give you first 90 rows. In this case there are up to 140 rows to work. Try to rewrite your query to return less rows (for example 1000) before sorting/limiting them. Taking 90 of 100 rows will be allways a performance leak. Regards, Tomasz Myrta ---(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] How to notice column changes in trigger
> > >So you want the trigger executed only if assignments to b and/or c do > >not appear within the update command. Right? > > > > Right, that's what I want. > I'm afraid I have no idea how to accomplish that. Regards, Christoph ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] Help on indexing timestamps
Andre Schubert <[EMAIL PROTECTED]> writes: > Tom Lane <[EMAIL PROTECTED]> wrote: >> Update to 7.3, and that problem will go away. >> > What is the difference between 7.2 and 7.3 related to my problem ? 7.3 is willing to index comparisons to now(), that's what ;-) You may care to study the difference between cachable/noncachable functions in 7.2, versus the difference between immutable/stable/volatile functions in 7.3. 7.2 has to treat now() as noncachable and therefore unsafe to index. 7.3 treats it as stable and therefore safe to index. You can work around this in 7.2 by cheating with subselects or wrapper functions, but that's a poor substitute for a real solution. regards, tom lane ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [SQL] [HACKERS] Cursors and backwards scans and SCROLL
"Zeugswetter Andreas SB SD" <[EMAIL PROTECTED]> writes: >> but just give a warning and then run the underlying >> query _again_, this toime with materialize on top and also do a Move to >> reposition the cursor. This will probably not work correctly for all >> tranasaction isolation levels though but it will penalize only these >> cases that absolutely need it. The penalty will of course be >> heavier ;( > rescan can only work in serializable isolation, no? I had thought about this and concluded it was not worth the trouble. It could be made to work if we copy the snapshot data from old plan to new, but realistically there's no value in it. Existing applications that are successfully using backwards-fetch are using queries that don't need materialization; therefore there's no compatibility gain from adding this extra code. regards, tom lane ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [SQL] [HACKERS] Cursors and backwards scans and SCROLL
"Jeroen T. Vermeulen" <[EMAIL PROTECTED]> writes: >> There are cases where >> identical cursor definitions might allow or not allow backwards fetch >> depending on the planner's choices. > Would it be possible to give warnings in a narrow superset of the > problematic cases, something along the lines of "I'm scrolling backwards > for you now, but there's no reason why that should work on this same query > tomorrow"? I don't see a practical way to do that --- that little bit of warning code would have to embed a lot of fragile assumptions about the set of alternatives searched by the planner. It would probably break every time we improved the planner. And the breakage would consist either of failing to give a warning when one is appropriate, or giving a warning when no other plan is really likely to be chosen; neither of which are going to be easily noticed or tested for. Seems like a losing game :-( regards, tom lane ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] [HACKERS] Cursors and backwards scans and SCROLL
Just a reminder that we could use cursors that exist after transaction commit (WITH HOLD) and updatable cursors (WHERE CURRENT OF cursorname). :-) --- Tom Lane wrote: > "Jeroen T. Vermeulen" <[EMAIL PROTECTED]> writes: > >> There are cases where > >> identical cursor definitions might allow or not allow backwards fetch > >> depending on the planner's choices. > > > Would it be possible to give warnings in a narrow superset of the > > problematic cases, something along the lines of "I'm scrolling backwards > > for you now, but there's no reason why that should work on this same query > > tomorrow"? > > I don't see a practical way to do that --- that little bit of warning > code would have to embed a lot of fragile assumptions about the set of > alternatives searched by the planner. It would probably break every > time we improved the planner. And the breakage would consist either of > failing to give a warning when one is appropriate, or giving a warning > when no other plan is really likely to be chosen; neither of which are > going to be easily noticed or tested for. Seems like a losing game :-( > > regards, tom lane > > ---(end of broadcast)--- > TIP 6: Have you searched our list archives? > > http://archives.postgresql.org > -- 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: [SQL] [HACKERS] Cursors and backwards scans and SCROLL
Tom Lane kirjutas P, 09.03.2003 kell 22:35: > However, this is going to create backwards-compatibility issues. > We have a few options for what to do: > > 1. Enforce the SQL spec requirement: error out if backwards fetch is > done when SCROLL wasn't given. But this will surely break a lot > of existing applications that work perfectly well. We could start by issuing a NOTICE/WARNING as a step towards the strict compliance and provide a GUC variable for those who are unable to cope even with the warning. The warning could be issued at two places - 1. ( more annoying ) issue it at cursor creation time when a plan is chosen that can't be fetched backwards. 2. like your #2, but just give a warning and then run the underlying query _again_, this toime with materialize on top and also do a Move to reposition the cursor. This will probably not work correctly for all tranasaction isolation levels though but it will penalize only these cases that absolutely need it. The penalty will of course be heavier ;( - Hannu ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
