[GENERAL] visibility map - what do i miss?
hi,
i tried to test new "visibility map" feature.
to do so i:
1. fetched postgresql sources from cvs
2. compiled
3. turned autovacuum off
4. started pg
5. ran this queries:
- CREATE TABLE test_1 (i INT4);
- CREATE TABLE test_2 (i INT4);
- CREATE TABLE test_3 (i INT4);
- CREATE TABLE test_4 (i INT4);
- INSERT INTO test_1 SELECT generate_series(1, 1);
- INSERT INTO test_2 SELECT generate_series(1, 1);
- INSERT INTO test_3 SELECT generate_series(1, 1);
- INSERT INTO test_4 SELECT generate_series(1, 1);
- UPDATE test_2 SET i = i + 1 WHERE i < 1000;
- UPDATE test_3 SET i = i + 1 WHERE i < 5000;
- UPDATE test_4 SET i = i + 1 WHERE i < 9000;
- VACUUM test_1;
- VACUUM test_2;
- VACUUM test_3;
- VACUUM test_4;
I did it 2 times, first with sources of pg from 1st of november, and
second - with head from yesterday evening (warsaw, poland time).
results puzzled me.
First run - without visibility maps, timing of vacuums:
Time: 267844.822 ms
Time: 138854.592 ms
Time: 305467.950 ms
Time: 487133.179 ms
Second run - on head:
Time: 252218.609 ms
Time: 234388.763 ms
Time: 334016.413 ms
Time: 575698.750 ms
Now - as I understand the change - visilibity maps should make second run much
faster?
Tests were performed on laptop. During first test I used it to browse the web,
read mail. During second test - nobody used the laptop.
Relation forms seem to exist:
# select oid from pg_database where datname = 'depesz';
oid
---
16389
(1 row)
# select relfilenode from pg_class where relname ~ 'test_';
relfilenode
-
26756
26759
26762
26765
(4 rows)
=> ls -l {26756,26759,26762,26765}*
-rw--- 1 pgdba pgdba 1073741824 2008-12-06 01:31 26756
-rw--- 1 pgdba pgdba 1073741824 2008-12-06 01:33 26756.1
-rw--- 1 pgdba pgdba 1065066496 2008-12-06 01:34 26756.2
-rw--- 1 pgdba pgdba 811008 2008-12-06 01:34 26756_fsm
-rw--- 1 pgdba pgdba 57344 2008-12-06 01:34 26756_vm
-rw--- 1 pgdba pgdba 1073741824 2008-12-06 01:35 26759
-rw--- 1 pgdba pgdba 1073741824 2008-12-06 01:36 26759.1
-rw--- 1 pgdba pgdba 1073741824 2008-12-06 01:37 26759.2
-rw--- 1 pgdba pgdba 312582144 2008-12-06 01:39 26759.3
-rw--- 1 pgdba pgdba 892928 2008-12-06 01:39 26759_fsm
-rw--- 1 pgdba pgdba 57344 2008-12-06 01:39 26759_vm
-rw--- 1 pgdba pgdba 1073741824 2008-12-06 01:39 26762
-rw--- 1 pgdba pgdba 1073741824 2008-12-06 01:49 26762.1
-rw--- 1 pgdba pgdba 1073741824 2008-12-06 01:41 26762.2
-rw--- 1 pgdba pgdba 1073741824 2008-12-06 01:42 26762.3
-rw--- 1 pgdba pgdba 523862016 2008-12-06 01:43 26762.4
-rw--- 1 pgdba pgdba1204224 2008-12-06 01:43 26762_fsm
-rw--- 1 pgdba pgdba 81920 2008-12-06 01:53 26762_vm
-rw--- 1 pgdba pgdba 1073741824 2008-12-06 02:01 26765
-rw--- 1 pgdba pgdba 1073741824 2008-12-06 02:08 26765.1
-rw--- 1 pgdba pgdba 1073741824 2008-12-06 02:18 26765.2
-rw--- 1 pgdba pgdba 1073741824 2008-12-06 01:50 26765.3
-rw--- 1 pgdba pgdba 1073741824 2008-12-06 01:51 26765.4
-rw--- 1 pgdba pgdba 735141888 2008-12-06 02:00 26765.5
-rw--- 1 pgdba pgdba1523712 2008-12-06 02:00 26765_fsm
-rw--- 1 pgdba pgdba 98304 2008-12-06 02:18 26765_vm
What do I miss?
Best regards,
depesz
--
Linkedin: http://www.linkedin.com/in/depesz / blog: http://www.depesz.com/
jid/gtalk: [EMAIL PROTECTED] / aim:depeszhdl / skype:depesz_hdl / gg:6749007
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] posible BUG on psql... or maybe worst
I was making some table creation on one of our development DB and found
that psql's \dt has problems showing all tables available. Basically, if
you have to tables with the same name in different schemas, only one
will be listed (the one on the schema that is first in the search_path).
IMHO, \dt should show all the tables per-schema.
Now what I can't find is where the problem is. \dt executes this query:
SELECT n.nspname as "Schema",
c.relname as "Name",
CASE c.relkind WHEN 'r' THEN 'table' WHEN 'v' THEN 'view' WHEN 'i'
THEN 'index' WHEN 'S' THEN 'sequence' WHEN 's' THEN 'special' END as "Type",
r.rolname as "Owner"
FROM pg_catalog.pg_class c
JOIN pg_catalog.pg_roles r ON r.oid = c.relowner
LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
WHERE c.relkind IN ('r','')
AND n.nspname <> 'pg_catalog'
AND n.nspname !~ '^pg_toast'
AND pg_catalog.pg_table_is_visible(c.oid)
ORDER BY 1,2;
The query looks ok, but it doesn't bring the 2 tables in the list.
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] posible BUG on psql... or maybe worst
On Sat, Dec 6, 2008 at 8:50 AM, Martin Marques
<[EMAIL PROTECTED]>wrote:
> I was making some table creation on one of our development DB and found
> that psql's \dt has problems showing all tables available. Basically, if you
> have to tables with the same name in different schemas, only one will be
> listed (the one on the schema that is first in the search_path).
>
> IMHO, \dt should show all the tables per-schema.
>
> Now what I can't find is where the problem is. \dt executes this query:
>
> SELECT n.nspname as "Schema",
> c.relname as "Name",
> CASE c.relkind WHEN 'r' THEN 'table' WHEN 'v' THEN 'view' WHEN 'i' THEN
> 'index' WHEN 'S' THEN 'sequence' WHEN 's' THEN 'special' END as "Type",
> r.rolname as "Owner"
> FROM pg_catalog.pg_class c
> JOIN pg_catalog.pg_roles r ON r.oid = c.relowner
> LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
> WHERE c.relkind IN ('r','')
> AND n.nspname <> 'pg_catalog'
> AND n.nspname !~ '^pg_toast'
> AND pg_catalog.pg_table_is_visible(c.oid)
> ORDER BY 1,2;
>
> The query looks ok, but it doesn't bring the 2 tables in the list.
>
>
Hi,
Schemas are a lot like directories at operating system level (except that
can't be nested).
When you ls (or dir) in /home/martin/ , normally you don't expect to see
/home/johnny/ listed as well.
But if you really want to see all tables, try adjusting search_path like
this:
SET search_path to myschema1,myschema2,public;
Then it should list all relations as you expect.
Re: [GENERAL] posible BUG on psql... or maybe worst
Diego Schulz escribió: Hi, Schemas are a lot like directories at operating system level (except that can't be nested). When you ls (or dir) in /home/martin/ , normally you don't expect to see /home/johnny/ listed as well. But if you really want to see all tables, try adjusting search_path like this: SET search_path to myschema1,myschema2,public; Then it should list all relations as you expect. Sorry, forgot to say that I SET search_path acordinlly to see relations from both schemas. But whan the table has the same name I only get the one from the first schema in the search_path. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] posible BUG on psql... or maybe worst
On Sat, Dec 6, 2008 at 10:00 AM, Martin Marques <[EMAIL PROTECTED]>wrote: > Diego Schulz escribió: > >> >> >> Hi, >> >> >> Schemas are a lot like directories at operating system level (except that >> can't be nested). >> When you ls (or dir) in /home/martin/ , normally you don't expect to see >> /home/johnny/ listed as well. >> >> But if you really want to see all tables, try adjusting search_path like >> this: >> >> SET search_path to myschema1,myschema2,public; >> >> Then it should list all relations as you expect. >> >> > Sorry, forgot to say that I SET search_path acordinlly to see relations > from both schemas. But whan the table has the same name I only get the one > from the first schema in the search_path. > I can confirm the behaviour you described. \dt+ *.contactos List of relations Schema | Name| Type | Owner | Description +---+---+-+- prueba | contactos | table | dschulz | public | contactos | table | dschulz | dschulz=# \dt+ List of relations Schema | Name | Type | Owner | Description ---++---++- prueba | contactos | table | dschulz | public | bitacora | table | dschulz | public | documentos | table | dschulz | public | documentos_tipos | table | dschulz | ... (snip) ... (no table public.contactos listed here) dschulz=# select version(); version - PostgreSQL 8.3.5 on i386-portbld-freebsd7.1, compiled by GCC cc (GCC) 4.2.1 20070719 [FreeBSD] (1 row) But you can always use \dt+ *. to list all relations in all schemas. cheers
Re: [GENERAL] visibility map - what do i miss?
On Sat, Dec 6, 2008 at 6:46 AM, hubert depesz lubaczewski <[EMAIL PROTECTED]> wrote: > hi, > i tried to test new "visibility map" feature. here's the test again in a more illustrative way: postgres=# INSERT INTO test_1 SELECT generate_series(1, 1); INSERT 0 1 Time: 136229.455 ms postgres=# VACUUM test_1; VACUUM Time: 40643.705 ms <-- setting hint bits postgres=# VACUUM test_1; VACUUM Time: 6112.946 ms <-- fast now! postgres=# VACUUM test_1; VACUUM Time: 5906.454 ms <-- just to be sure!! postgres=# update test_1 set i = i where i = ; UPDATE 1 Time: 10201.296 ms postgres=# VACUUM test_1; VACUUM Time: 5896.648 ms <-- still fast postgres=# UPDATE test_1 SET i = i + 1 WHERE i < 9000; UPDATE 8999 Time: 352955.281 ms <--uggh! postgres=# VACUUM test_1; VACUUM Time: 200082.556 ms <-- not bad postgres=# VACUUM test_1; VACUUM Time: 17313.576 ms <-- faster now! postgres=# UPDATE test_1 SET i = i + 1 WHERE i < 1000; UPDATE 998 Time: 55188.942 ms postgres=# VACUUM test_1; VACUUM Time: 21353.182 ms < -- fast! So what do we gather from this? Well, the feature works as advertised. I think that as long as your updates are not uniformly distributed across pages, vismap is a huge performance win for many workloads. I think the benefit will increase as the feature is tweaked in future versions. vacuum times are one of the things that make dealing with large tables difficult, and force us to use partitioning (which is, frankly, a hack). Why are new pages initialized dirty? Do inserts on pages set the dirty bit? merlin -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Unique constaint violated without being violated
Hi all,
Here's an odd one:
itidb=> \d joblist;
Table "public.joblist"
Column | Type | Modifiers
---+--+---
full_name | character varying(64)| not null
email_address | character varying(64)| not null
username | character varying(12)|
password | character varying(12)|
recruiter | boolean | not null
subscribed| boolean | not null
verified | boolean | not null
created_at| timestamp with time zone | not null
updated_at| timestamp with time zone | not null
verification_code | character varying(24)|
alumni| boolean |
Indexes:
"joblist_pkey" PRIMARY KEY, btree (email_address)
"joblist_username_key" UNIQUE, btree (username)
itidb=> update joblist set (full_name, email_address, recruiter,
itidb(> subscribed, verified, created_at, updated_at) =
itidb-> ('[name hidden]', '[email address hidden]', false, true
itidb(> true, current_timestamp(0), current_timestamp(0));
ERROR: duplicate key value violates unique constraint "joblist_pkey"
itidb=> select * from joblist where
itidb-> email_address='[email address hidden]';
(No rows)
email_address is the primary key of this table (because the manual says
every table should have one :-) and the unique aspect of this primary
key is being violated when I try to enter the (hidden) email address
above.
But the email address hasn't already been entered into this table, as
shown by the output of the select command... so why the error?
Is my database corrupted somehow, or am I just losing my mind?
What course of action do you suggest I follow?
Sebastian
P.S. I've checked three times now, and I'm definitely using the same
email address in the update command and the select command, i.e., a
typo is not what's causing this.
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Unique constaint violated without being violated
On Sat, Dec 6, 2008 at 10:27 AM, Sebastian Tennant
<[EMAIL PROTECTED]> wrote:
> itidb=> update joblist set (full_name, email_address, recruiter,
> itidb(> subscribed, verified, created_at, updated_at) =
> itidb-> ('[name hidden]', '[email address hidden]', false, true
> itidb(> true, current_timestamp(0), current_timestamp(0));
> ERROR: duplicate key value violates unique constraint "joblist_pkey"
>
> itidb=> select * from joblist where
> itidb-> email_address='[email address hidden]';
> (No rows)
>
> email_address is the primary key of this table (because the manual says
> every table should have one :-) and the unique aspect of this primary
> key is being violated when I try to enter the (hidden) email address
> above.
>
> But the email address hasn't already been entered into this table, as
> shown by the output of the select command... so why the error?
It looks to me like you are setting the whole table to the same
address in the update statement (no where clause)...so of course you'd
get the error. Maybe you want to do an insert statement?
merlin
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] visibility map - what do i miss?
On Sat, Dec 6, 2008 at 8:38 AM, Merlin Moncure <[EMAIL PROTECTED]> wrote: > So what do we gather from this? Well, the feature works as > advertised. I think that as long as your updates are not uniformly > distributed across pages, vismap is a huge performance win for many > workloads. I think the benefit will increase as the feature is > tweaked in future versions. vacuum times are one of the things that > make dealing with large tables difficult, and force us to use > partitioning (which is, frankly, a hack). > > Why are new pages initialized dirty? Do inserts on pages set the dirty bit? dumb question...there is no guarantee the transaction will be committed. merlin -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Resp.: Automatic insert statement generator?
Hi Osvaldo,
Your list_fields function looked interesting to me so I tried it out and
it only worked for one of the five or so tables in the database I was
connected to at the time.
More concerning is the fact that I can't seem to drop it. I'm told it
doesn't exist, and then I use it to prove (to myself) that it does.
Here's it not working:
itidb=> select list_fields('joblistings');
-[ RECORD 1 ]-
list_fields |
Here's it working:
itidb=> select list_fields('joblist');
-[ RECORD 1 ]--
list_fields | full_name,username,password,recruiter,subscribed,...
Here's me trying to drop it, only to be told it doesn't exist:
itidb=> drop function list_fields();
ERROR: function list_fields() does not exist
And here's it working again!
itidb=> select list_fields('joblist');
-[ RECORD 1 ]--
list_fields | full_name,username,password,recruiter,subscribed,...
I'm noticing some very strange behaviour this evening (see thread
'Unique constaint violated without being violated').
Is my database corrupted or are there some vital database maintenance
tasks I've neglected to do?
I'm starting to get worried now.
Sebastian
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Unique constaint violated without being violated
Quoth "Merlin Moncure" <[EMAIL PROTECTED]>: > It looks to me like you are setting the whole table to the same > address in the update statement (no where clause)...so of course you'd > get the error. Maybe you want to do an insert statement? > > merlin Doh! Thanks Merlin. I'm so glad it's just my mind that's going! :-) Sebastian -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Resp.: Automatic insert statement generator?
On 06/12/2008 16:02, Sebastian Tennant wrote:
> Here's it working:
>
> itidb=> select list_fields('joblist');
> Here's me trying to drop it, only to be told it doesn't exist:
>
> itidb=> drop function list_fields();
> ERROR: function list_fields() does not exist
You need to specify the argument types as well, so this -
drop function list_fields(varchar); -- or whatever it is
- ought to work.
Ray.
--
Raymond O'Donnell, Director of Music, Galway Cathedral, Ireland
[EMAIL PROTECTED]
Galway Cathedral Recitals: http://www.galwaycathedral.org/recitals
--
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Resp.: Automatic insert statement generator?
Quoth Raymond O'Donnell <[EMAIL PROTECTED]>:
> On 06/12/2008 16:02, Sebastian Tennant wrote:
>> Here's it working:
>>
>> itidb=> select list_fields('joblist');
>
>
>
>> Here's me trying to drop it, only to be told it doesn't exist:
>>
>> itidb=> drop function list_fields();
>> ERROR: function list_fields() does not exist
>
> You need to specify the argument types as well, so this -
>
> drop function list_fields(varchar); -- or whatever it is
>
> - ought to work.
>
> Ray.
Man, am I'm feeling geriatric tonight! Thanks for clearing that up for
me Ray.
I guess this is what comes of working under pressure on a Saturday night
when I should be out having a quiet drink with a few friends.
Sebastian
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] posible BUG on psql... or maybe worst
Diego Schulz wrote: > > Sorry, forgot to say that I SET search_path acordinlly to see relations > > from both schemas. But whan the table has the same name I only get the one > > from the first schema in the search_path. > > > > > I can confirm the behaviour you described. Yes, \dt was designed that way, and *.name is the proper way to show tables in all schemas. -- Bruce Momjian <[EMAIL PROTECTED]>http://momjian.us EnterpriseDB http://enterprisedb.com + If your life is a hard drive, Christ can be your backup. + -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Planner picking topsey turvey plan?
Anyone? --- On Fri, 5/12/08, Glyn Astill <[EMAIL PROTECTED]> wrote: > From: Glyn Astill <[EMAIL PROTECTED]> > Subject: [GENERAL] Planner picking topsey turvey plan? > To: pgsql-general@postgresql.org > Date: Friday, 5 December, 2008, 2:23 PM > Hi people, > > Does anyone know how I can change what I'm doing to get > pgsql to pick a better plan? > > I'll explain what I've done below but please > forgive me if I interpret the plans wrong as I try to > describe, I've split it into 4 points to try and ease > the mess of pasting in the plans.. > > > 1) I've created a view "orders" that joins > two tables "credit" and "mult_ord" > together as below: > > CREATE VIEW orders AS > SELECT b.mult_ref, a.show, MIN(a.transno) AS > "lead_transno", COUNT(a.transno) AS > "parts", SUM(a.tickets) AS "items", > SUM(a.value) AS "value" > FROM (credit a LEFT OUTER JOIN mult_ord b ON a.transno = > b.transno) > GROUP BY b.mult_ref, a.show; > > > > 2) And an explain on that view comes out as below, it's > using the correct index for the field show on > "credit" which doesn't look too bad to me: > > DB=# explain select a.artist, a.date, b.mult_ref, b.items, > b.parts from (show a inner join orders b on a.code = b.show) > where b.show = 357600; >QUERY PLAN > > Nested Loop (cost=15050.79..15099.68 rows=1013 width=70) >-> Index Scan using show_index01 on show a > (cost=0.00..8.37 rows=1 width=26) > Index Cond: (code = 357600::numeric) >-> HashAggregate (cost=15050.79..15071.05 rows=1013 > width=39) > -> Nested Loop Left Join (cost=0.00..15035.60 > rows=1013 width=39) >-> Index Scan using credit_index04 on > credit a (cost=0.00..4027.30 rows=1013 width=31) > Index Cond: (show = 357600::numeric) >-> Index Scan using mult_ord_index02 on > mult_ord b (cost=0.00..10.85 rows=1 width=17) > Index Cond: (a.transno = b.transno) > (9 rows) > > > > 3) Then I have a table called "show" that is > indexed on the artist field, and a plan for listing the > shows for an artist is as below, again this doesn't look > too bad to me, as it's using the index on artist. > > DB=# explain select * from show where artist = > 'ALKALINE TRIO'; > QUERY PLAN > - > Bitmap Heap Scan on show (cost=9.59..582.41 rows=153 > width=348) >Recheck Cond: ((artist)::text = 'ALKALINE > TRIO'::text) >-> Bitmap Index Scan on show_index07 > (cost=0.00..9.56 rows=153 width=0) > Index Cond: ((artist)::text = 'ALKALINE > TRIO'::text) > (4 rows) > > > > 4) So.. I guess I can join "show" -> > "orders", expecting an index scan on > "show" for the artist, then an index scan on > "orders" for each show. > > However it seems the planner has other ideas, it just looks > backwards to me: > > DB=# explain select a.artist, a.date, b.mult_ref, b.items, > b.parts from (show a inner join orders b on a.code = b.show) > where artist = 'ALKALINE TRIO'; > QUERY PLAN > > Hash Join (cost=1576872.96..1786175.37 rows=1689 > width=70) >Hash Cond: (a.show = a.code) >-> GroupAggregate (cost=1576288.64..1729424.39 > rows=4083620 width=39) > -> Sort (cost=1576288.64..1586497.69 > rows=4083620 width=39) >Sort Key: b.mult_ref, a.show >-> Hash Left Join > (cost=321406.05..792886.22 rows=4083620 width=39) > Hash Cond: (a.transno = b.transno) > -> Seq Scan on credit a > (cost=0.00..267337.20 rows=4083620 width=31) > -> Hash > (cost=160588.80..160588.80 rows=8759380 width=17) >-> Seq Scan on mult_ord b > (cost=0.00..160588.80 rows=8759380 width=17) >-> Hash (cost=582.41..582.41 rows=153 width=26) > -> Bitmap Heap Scan on show a > (cost=9.59..582.41 rows=153 width=26) >Recheck Cond: ((artist)::text = > 'ALKALINE TRIO'::text) >-> Bitmap Index Scan on show_index07 > (cost=0.00..9.56 rows=153 width=0) > Index Cond: ((artist)::text = > 'ALKALINE TRIO'::text) > (15 rows) > > Any idea if I can get around this? > > > > > > > -- > Sent via pgsql-general mailing list > (pgsql-general@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-general -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [ADMIN] [GENERAL] Planner picking topsey turvey plan?
what does explain analyze yourqueryhere say? On Sat, Dec 6, 2008 at 1:33 PM, Glyn Astill <[EMAIL PROTECTED]> wrote: > Anyone? > > > --- On Fri, 5/12/08, Glyn Astill <[EMAIL PROTECTED]> wrote: > >> From: Glyn Astill <[EMAIL PROTECTED]> >> Subject: [GENERAL] Planner picking topsey turvey plan? >> To: pgsql-general@postgresql.org >> Date: Friday, 5 December, 2008, 2:23 PM >> Hi people, >> >> Does anyone know how I can change what I'm doing to get >> pgsql to pick a better plan? >> >> I'll explain what I've done below but please >> forgive me if I interpret the plans wrong as I try to >> describe, I've split it into 4 points to try and ease >> the mess of pasting in the plans.. >> >> >> 1) I've created a view "orders" that joins >> two tables "credit" and "mult_ord" >> together as below: >> >> CREATE VIEW orders AS >> SELECT b.mult_ref, a.show, MIN(a.transno) AS >> "lead_transno", COUNT(a.transno) AS >> "parts", SUM(a.tickets) AS "items", >> SUM(a.value) AS "value" >> FROM (credit a LEFT OUTER JOIN mult_ord b ON a.transno = >> b.transno) >> GROUP BY b.mult_ref, a.show; >> >> >> >> 2) And an explain on that view comes out as below, it's >> using the correct index for the field show on >> "credit" which doesn't look too bad to me: >> >> DB=# explain select a.artist, a.date, b.mult_ref, b.items, >> b.parts from (show a inner join orders b on a.code = b.show) >> where b.show = 357600; >>QUERY PLAN >> >> Nested Loop (cost=15050.79..15099.68 rows=1013 width=70) >>-> Index Scan using show_index01 on show a >> (cost=0.00..8.37 rows=1 width=26) >> Index Cond: (code = 357600::numeric) >>-> HashAggregate (cost=15050.79..15071.05 rows=1013 >> width=39) >> -> Nested Loop Left Join (cost=0.00..15035.60 >> rows=1013 width=39) >>-> Index Scan using credit_index04 on >> credit a (cost=0.00..4027.30 rows=1013 width=31) >> Index Cond: (show = 357600::numeric) >>-> Index Scan using mult_ord_index02 on >> mult_ord b (cost=0.00..10.85 rows=1 width=17) >> Index Cond: (a.transno = b.transno) >> (9 rows) >> >> >> >> 3) Then I have a table called "show" that is >> indexed on the artist field, and a plan for listing the >> shows for an artist is as below, again this doesn't look >> too bad to me, as it's using the index on artist. >> >> DB=# explain select * from show where artist = >> 'ALKALINE TRIO'; >> QUERY PLAN >> - >> Bitmap Heap Scan on show (cost=9.59..582.41 rows=153 >> width=348) >>Recheck Cond: ((artist)::text = 'ALKALINE >> TRIO'::text) >>-> Bitmap Index Scan on show_index07 >> (cost=0.00..9.56 rows=153 width=0) >> Index Cond: ((artist)::text = 'ALKALINE >> TRIO'::text) >> (4 rows) >> >> >> >> 4) So.. I guess I can join "show" -> >> "orders", expecting an index scan on >> "show" for the artist, then an index scan on >> "orders" for each show. >> >> However it seems the planner has other ideas, it just looks >> backwards to me: >> >> DB=# explain select a.artist, a.date, b.mult_ref, b.items, >> b.parts from (show a inner join orders b on a.code = b.show) >> where artist = 'ALKALINE TRIO'; >> QUERY PLAN >> >> Hash Join (cost=1576872.96..1786175.37 rows=1689 >> width=70) >>Hash Cond: (a.show = a.code) >>-> GroupAggregate (cost=1576288.64..1729424.39 >> rows=4083620 width=39) >> -> Sort (cost=1576288.64..1586497.69 >> rows=4083620 width=39) >>Sort Key: b.mult_ref, a.show >>-> Hash Left Join >> (cost=321406.05..792886.22 rows=4083620 width=39) >> Hash Cond: (a.transno = b.transno) >> -> Seq Scan on credit a >> (cost=0.00..267337.20 rows=4083620 width=31) >> -> Hash >> (cost=160588.80..160588.80 rows=8759380 width=17) >>-> Seq Scan on mult_ord b >> (cost=0.00..160588.80 rows=8759380 width=17) >>-> Hash (cost=582.41..582.41 rows=153 width=26) >> -> Bitmap Heap Scan on show a >> (cost=9.59..582.41 rows=153 width=26) >>Recheck Cond: ((artist)::text = >> 'ALKALINE TRIO'::text) >>-> Bitmap Index Scan on show_index07 >> (cost=0.00..9.56 rows=153 width=0) >> Index Cond: ((artist)::text = >> 'ALKALINE TRIO'::text) >> (15 rows) >> >> Any idea if I can get around this? >> >> >> >> >> >> >> -- >> Sent via pgsql-general mailing list >> (pgsql-general@postgresql.org) >> To make changes to your subscription: >> http://www.postgresql.org/mailpref/pgsql-general > > > > > -- > Sent via pgsql-admi
Re: [ADMIN] [GENERAL] Planner picking topsey turvey plan?
Explain analyze below, DB=# explain analyze select a.artist, a.date, b.mult_ref, b.items, b.parts from (show a inner join orders b on a.code = b.show) where artist = 'ALKALINE TRIO'; QUERY PLAN --- Hash Join (cost=1583955.94..1794350.36 rows=1552 width=70) (actual time=231496.678..243243.711 rows=892 loops=1) Hash Cond: (a.show = a.code) -> GroupAggregate (cost=1583418.91..1737354.68 rows=4104954 width=40) (actual time=223204.620..241813.067 rows=2856379 loops=1) -> Sort (cost=1583418.91..1593681.29 rows=4104954 width=40) (actual time=223204.567..231296.896 rows=4104964 loops=1) Sort Key: b.mult_ref, a.show Sort Method: external merge Disk: 224328kB -> Hash Left Join (cost=321999.38..795776.58 rows=4104954 width=40) (actual time=14850.320..165804.778 rows=4104964 loops=1) Hash Cond: (a.transno = b.transno) -> Seq Scan on credit a (cost=0.00..268740.54 rows=4104954 width=31) (actual time=7.563..76901.901 rows=4104954 loops=1) -> Hash (cost=160885.28..160885.28 rows=8775528 width=18) (actual time=14831.810..14831.810 rows=8775528 loops=1) -> Seq Scan on mult_ord b (cost=0.00..160885.28 rows=8775528 width=18) (actual time=4.716..4952.254 rows=8775528 loops=1) -> Hash (cost=535.28..535.28 rows=140 width=26) (actual time=228.599..228.599 rows=54 loops=1) -> Bitmap Heap Scan on show a (cost=9.49..535.28 rows=140 width=26) (actual time=77.723..228.488 rows=54 loops=1) Recheck Cond: ((artist)::text = 'ALKALINE TRIO'::text) -> Bitmap Index Scan on show_index07 (cost=0.00..9.46 rows=140 width=0) (actual time=62.228..62.228 rows=54 loops=1) Index Cond: ((artist)::text = 'ALKALINE TRIO'::text) Total runtime: 243367.640 ms --- On Sat, 6/12/08, Scott Marlowe <[EMAIL PROTECTED]> wrote: > From: Scott Marlowe <[EMAIL PROTECTED]> > Subject: Re: [ADMIN] [GENERAL] Planner picking topsey turvey plan? > To: [EMAIL PROTECTED] > Cc: pgsql-general@postgresql.org, [EMAIL PROTECTED] > Date: Saturday, 6 December, 2008, 8:35 PM > what does explain analyze yourqueryhere say? > > On Sat, Dec 6, 2008 at 1:33 PM, Glyn Astill > <[EMAIL PROTECTED]> wrote: > > Anyone? > > > > > > --- On Fri, 5/12/08, Glyn Astill > <[EMAIL PROTECTED]> wrote: > > > >> From: Glyn Astill <[EMAIL PROTECTED]> > >> Subject: [GENERAL] Planner picking topsey turvey > plan? > >> To: pgsql-general@postgresql.org > >> Date: Friday, 5 December, 2008, 2:23 PM > >> Hi people, > >> > >> Does anyone know how I can change what I'm > doing to get > >> pgsql to pick a better plan? > >> > >> I'll explain what I've done below but > please > >> forgive me if I interpret the plans wrong as I try > to > >> describe, I've split it into 4 points to try > and ease > >> the mess of pasting in the plans.. > >> > >> > >> 1) I've created a view "orders" that > joins > >> two tables "credit" and > "mult_ord" > >> together as below: > >> > >> CREATE VIEW orders AS > >> SELECT b.mult_ref, a.show, MIN(a.transno) AS > >> "lead_transno", COUNT(a.transno) AS > >> "parts", SUM(a.tickets) AS > "items", > >> SUM(a.value) AS "value" > >> FROM (credit a LEFT OUTER JOIN mult_ord b ON > a.transno = > >> b.transno) > >> GROUP BY b.mult_ref, a.show; > >> > >> > >> > >> 2) And an explain on that view comes out as below, > it's > >> using the correct index for the field show on > >> "credit" which doesn't look too bad > to me: > >> > >> DB=# explain select a.artist, a.date, b.mult_ref, > b.items, > >> b.parts from (show a inner join orders b on a.code > = b.show) > >> where b.show = 357600; > >> > QUERY PLAN > >> > > >> Nested Loop (cost=15050.79..15099.68 rows=1013 > width=70) > >>-> Index Scan using show_index01 on show a > >> (cost=0.00..8.37 rows=1 width=26) > >> Index Cond: (code = 357600::numeric) > >>-> HashAggregate (cost=15050.79..15071.05 > rows=1013 > >> width=39) > >> -> Nested Loop Left Join > (cost=0.00..15035.60 > >> rows=1013 width=39) > >>-> Index Scan using > credit_index04 on > >> credit a (cost=0.00..4027.30 rows=1013 width=31) > >> Index Cond: (show = > 357600::numeric) > >>-> Index Scan using > mult_ord_index02 on > >> mult_ord b (cost=0.00..10.85 rows=1 width=17) > >> Index Cond: (a.transno = > b.transno) > >> (9 rows) > >> > >> > >> > >> 3) Then I have a table called "show" > that is > >> indexed on the artist field, and a plan for > listing the > >> shows
