Re: [PERFORM] One long transaction or multiple short transactions?
Le 08/10/2015 01:40, Carlo a écrit : >> How many cores do you have on that machine? Test if limiting number of simultaneous feeds, like bringing their number down to half of your normal connections has the same positive effect. << I am told 32 cores on a LINUX VM. The operators have tried limiting the number of threads. They feel that the number of connections is optimal. However, under the same conditions they noticed a sizable boost in performance if the same import was split into two successive imports which had shorter transactions. I am just looking to see if there is any reason to think that lock contention (or anything else) over longer vs. shorter single-row-write transactions under the same conditions might explain this. I don't think inserts can cause contention on the server. Insert do not lock tables during the transaction. You may have contention on sequence but it won't vary with transaction size. Have you checked the resource usage (CPU,memory) on the client side ? How do you insert rows ? Do you use plain postgres API ? Regards, Laurent
Re: [PERFORM] IS NOT NULL and LEFT JOIN
Le Mardi 21 Octobre 2014 10:44 CEST, David Rowley dgrowle...@gmail.com a écrit: For what it's worth I'd say they are identical, at least, if you discount deferring foreign key constraints or also executing the query from within a volatile function which was called by a query which just updated the user_info table to break referential integrity. I must say I had not thought of that. The presence of the foreign key on contract_contract.user_info which references user_user_info.id means that any non-null contract_contract.user_info record must reference a valid user_user_info record, therefore the join is not required to prove that a non nulled user_info contract records match a user info record, therefore the join to check it exists is pretty much pointless in just about all cases that you're likely to care about. Although, saying that I'm still a bit confused about the question. Are you asking if there's some way to get PostgreSQL to run the 1st query faster? Or are you asking if both queries are equivalent? I was asking for a way to make it run faster. Given that it returns at most a few rows found by an index, I was thinking it could be made to run faster. But I agree that the query is not well written (well generated by hibernate) considering the result I want. Regards, Laurent -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] IS NOT NULL and LEFT JOIN
Hi David, Do we agree that both queries are identical ? Since we join on c.user_info=u.id http://u.id having u.id http://u.id is not null or c.user_info is not null in the where clause is the same, isn't it ? Since c.user_info=u.id http://u.id the condition onu.id is not null does not use any *new* information from user_user_info. Regards, Laurent Le 19/10/2014 10:41, David Rowley a écrit : On Sun, Oct 19, 2014 at 5:10 PM, Laurent Martelli laurent.marte...@enercoop.org mailto:laurent.marte...@enercoop.org wrote: Hello there, I have a strange query plan involving an IS NOT NULL and a LEFT JOIN. I grant you that the query can be written without the JOIN on user_user_info, but it is generated like this by hibernate. Just changing the IS NOT NULL condition to the other side of useless JOIN makes a big difference in the query plan : -- THE BAD ONE : given the selectivity on c.name http://c.name and c.email, barely more than one row will ever be returned But it looks like you're ignoring the fact that the OR condition would force the query to match not only the user and the email, but also any row that finds a match in the user_user_info table, which going by the planner's estimates, that's every row in the contract_contract table. This is why the planner chooses a seqscan on the contract_contract table instead of using the index on lower(name). Is it really your intention to get all rows that find a this martelli contract that has this email, and along with that, get every contract that has a not null user_info record? I see that you have a foreign key on c.user_info to reference the user, so this should be matching everything with a non null user_info record. explain analyze select c.* from contact_contact c left outer join user_user_info u on c.user_info=u.id http://u.id left outer join contact_address a on c.address=a.id http://a.id where lower(c.name http://c.name)='martelli' and c.email='ds...@ezrfz.com mailto:ds...@ezrfz.com' or u.id http://u.id is not null; QUERY PLAN Hash Left Join (cost=1.83..2246.76 rows=59412 width=4012) (actual time=53.645..53.645 rows=0 loops=1) Hash Cond: (c.user_info = u.id http://u.id) Filter: (((lower((c.name http://c.name)::text) = 'martelli'::text) AND ((c.email)::text = 'ds...@ezrfz.com mailto:ds...@ezrfz.com'::text)) OR (u.id http://u.id IS NOT NULL)) Rows Removed by Filter: 58247 - Seq Scan on contact_contact c (cost=0.00..2022.12 rows=59412 width=4012) (actual time=0.007..6.892 rows=58247 loops=1) - Hash (cost=1.37..1.37 rows=37 width=8) (actual time=0.029..0.029 rows=37 loops=1) Buckets: 1024 Batches: 1 Memory Usage: 2kB - Seq Scan on user_user_info u (cost=0.00..1.37 rows=37 width=8) (actual time=0.004..0.015 rows=37 loops=1) Planning time: 0.790 ms Execution time: 53.712 ms -- THE GOOD ONE (test IS NOT NULL on contact0_.user_info instead of userinfo1_.id) explain analyze select c.* from contact_contact c left outer join user_user_info u on c.user_info=u.id http://u.id left outer join contact_address a on c.address=a.id http://a.id where lower(c.name http://c.name)='martelli' and c.email='ds...@ezrfz.com mailto:ds...@ezrfz.com' or c.user_info is not null; QUERY PLAN Bitmap Heap Scan on contact_contact c (cost=8.60..16.41 rows=1 width=4012) (actual time=0.037..0.037 rows=0 loops=1) Recheck Cond: (((email)::text = 'ds...@ezrfz.com mailto:ds...@ezrfz.com'::text) OR (user_info IS NOT NULL)) Filter: (((lower((name)::text) = 'martelli'::text) AND ((email)::text = 'ds...@ezrfz.com mailto:ds...@ezrfz.com'::text)) OR (user_info IS NOT NULL)) - BitmapOr (cost=8.60..8.60 rows=2 width=0) (actual time=0.034..0.034 rows=0 loops=1) - Bitmap Index Scan on idx_contact_email (cost=0.00..4.30 rows=2 width=0) (actual time=0.027..0.027 rows=0 loops=1) Index Cond: ((email)::text = 'ds...@ezrfz.com mailto:ds...@ezrfz.com'::text) - Bitmap Index Scan on contact_contact_user_info_idx (cost=0.00..4.30 rows=1 width=0) (actual time=0.007..0.007 rows=0 loops=1) Index Cond: (user_info IS NOT NULL) Planning time: 0.602 ms Execution time: 0.118 ms If you look closely at the 2nd query plan, you'll see that no joins are performed, and it's only the contract_contract table that's looked at. This is because
Re: [PERFORM] IS NOT NULL and LEFT JOIN
Le 20/10/2014 15:58, Tom Lane a écrit : Laurent Martelli laurent.marte...@enercoop.org writes: Do we agree that both queries are identical ? No, they *aren't* identical. Go consult any SQL reference. Left join conditions don't work the way you seem to be thinking: after the join, the RHS column might be null, rather than equal to the LHS column. Yes, I was wrong to assume that c.user_info=u.id because of the LEFT JOIN. But since I only want rows where u.id IS NOT NULL, in any case I will also have c.user_info IS NOT NULL. Also, having a foreign key, if c.user_info is not null, it will have a match in u. So in that case, either both c.user_info and c.id are null in the result rows, or they are equal. Regards, Laurent -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
[PERFORM] IS NOT NULL and LEFT JOIN
Hello there, I have a strange query plan involving an IS NOT NULL and a LEFT JOIN. I grant you that the query can be written without the JOIN on user_user_info, but it is generated like this by hibernate. Just changing the IS NOT NULL condition to the other side of useless JOIN makes a big difference in the query plan : -- THE BAD ONE : given the selectivity on c.name and c.email, barely more than one row will ever be returned explain analyze select c.* from contact_contact c left outer join user_user_info u on c.user_info=u.id left outer join contact_address a on c.address=a.id where lower(c.name)='martelli' and c.email='ds...@ezrfz.com' or u.id is not null; QUERY PLAN Hash Left Join (cost=1.83..2246.76 rows=59412 width=4012) (actual time=53.645..53.645 rows=0 loops=1) Hash Cond: (c.user_info = u.id) Filter: (((lower((c.name)::text) = 'martelli'::text) AND ((c.email)::text = 'ds...@ezrfz.com'::text)) OR (u.id IS NOT NULL)) Rows Removed by Filter: 58247 - Seq Scan on contact_contact c (cost=0.00..2022.12 rows=59412 width=4012) (actual time=0.007..6.892 rows=58247 loops=1) - Hash (cost=1.37..1.37 rows=37 width=8) (actual time=0.029..0.029 rows=37 loops=1) Buckets: 1024 Batches: 1 Memory Usage: 2kB - Seq Scan on user_user_info u (cost=0.00..1.37 rows=37 width=8) (actual time=0.004..0.015 rows=37 loops=1) Planning time: 0.790 ms Execution time: 53.712 ms -- THE GOOD ONE (test IS NOT NULL on contact0_.user_info instead of userinfo1_.id) explain analyze select c.* from contact_contact c left outer join user_user_info u on c.user_info=u.id left outer join contact_address a on c.address=a.id where lower(c.name)='martelli' and c.email='ds...@ezrfz.com' or c.user_info is not null; QUERY PLAN Bitmap Heap Scan on contact_contact c (cost=8.60..16.41 rows=1 width=4012) (actual time=0.037..0.037 rows=0 loops=1) Recheck Cond: (((email)::text = 'ds...@ezrfz.com'::text) OR (user_info IS NOT NULL)) Filter: (((lower((name)::text) = 'martelli'::text) AND ((email)::text = 'ds...@ezrfz.com'::text)) OR (user_info IS NOT NULL)) - BitmapOr (cost=8.60..8.60 rows=2 width=0) (actual time=0.034..0.034 rows=0 loops=1) - Bitmap Index Scan on idx_contact_email (cost=0.00..4.30 rows=2 width=0) (actual time=0.027..0.027 rows=0 loops=1) Index Cond: ((email)::text = 'ds...@ezrfz.com'::text) - Bitmap Index Scan on contact_contact_user_info_idx (cost=0.00..4.30 rows=1 width=0) (actual time=0.007..0.007 rows=0 loops=1) Index Cond: (user_info IS NOT NULL) Planning time: 0.602 ms Execution time: 0.118 ms My tables are as follow, and I use postgres 9.4 : Table « public.contact_contact » Colonne |Type | Modificateurs | Stockage | Cible de statistiques | Description +-+---+--+---+- id | bigint | non NULL | plain | | archived | boolean | | plain | | version| integer | | plain | | created_on | timestamp without time zone | | plain | | updated_on | timestamp without time zone | | plain | | actor_ref | character varying(255) | | extended | | addressl1 | character varying(255) | | extended | | comment| text| | extended | | contact_partner_ok | boolean | | plain | | date_of_birth | date| | plain | | email | character varying(255) | | extended | | email_pro | character varying(255) | | extended | | fax| character varying(255) | | extended | | first_name | character varying(255) | | extended | | fixed_phone1 | character varying(255) |
[PERFORM] Speeding up select distinct
Consider this query: SELECT distinct owner from pictures; Unique (cost=361.18..382.53 rows=21 width=4) (actual time=14.197..17.639 rows=21 loops=1) - Sort (cost=361.18..371.86 rows=4270 width=4) (actual time=14.188..15.450 rows=4270 loops=1) Sort Key: owner - Seq Scan on pictures (cost=0.00..103.70 rows=4270 width=4) (actual time=0.012..5.795 rows=4270 loops=1) Total runtime: 19.147 ms I thought that 19ms to return 20 rows out of a 4000 rows table so I added an index: CREATE INDEX pictures_owner ON pictures (owner); It gives a slight improvement: Unique (cost=0.00..243.95 rows=21 width=4) (actual time=0.024..10.293 rows=21 loops=1) - Index Scan using pictures_owner on pictures (cost=0.00..233.27 rows=4270 width=4) (actual time=0.022..8.227 rows=4270 loops=1) Total runtime: 10.369 ms But still, it's a lot for 20 rows. I looked at other type of indexes, but they seem to either not give beter perfs or be irrelevant. Any ideas, apart from more or less manually maintaining a list of distinct owners in another table ? -- Laurent Martelli [EMAIL PROTECTED]Java Aspect Components http://www.aopsys.com/ http://jac.objectweb.org ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [PERFORM] Speeding up select distinct
Rod == Rod Taylor [EMAIL PROTECTED] writes: Rod On Wed, 2005-03-16 at 18:58 +0100, Laurent Martelli wrote: Consider this query: SELECT distinct owner from pictures; Rod The performance has nothing to do with the number of rows Rod returned, but rather the complexity of calculations and amount Rod of data to sift through in order to find it. Yes, but I thought that an index might be able to know what distinct values there are and help optime that query very much. -- Laurent Martelli [EMAIL PROTECTED]Java Aspect Components http://www.aopsys.com/ http://jac.objectweb.org ---(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] Speeding up select distinct
Wow, what a fast response !!! PFC == PFC [EMAIL PROTECTED] writes: PFC Try : PFC SELECT owner from pictures group by owner; That's a slight improvement, but there's still a seq scan on pictures: HashAggregate (cost=114.38..114.38 rows=21 width=4) (actual time=7.585..7.605 rows=21 loops=1) - Seq Scan on pictures (cost=0.00..103.70 rows=4270 width=4) (actual time=0.015..3.272 rows=4270 loops=1) Total runtime: 7.719 ms -- Laurent Martelli [EMAIL PROTECTED]Java Aspect Components http://www.aopsys.com/ http://jac.objectweb.org ---(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] Fw: invitation au Village du Logiciel Libre de la
alix == alix [EMAIL PROTECTED] writes: alix Le Mon, 12 Jul 2004 12:37:47 +0200 (CEST) Jean-Luc Ancey alix [EMAIL PROTECTED] stef écrivit: Pour moi la même chose que l'année dernière : on s'abstient. alix Pour moi la même chose que l'année dernière : on y va. Idem pour moi. -- Laurent Martellivice-président de Parinux http://www.bearteam.org/~laurent/ http://www.parinux.org/ [EMAIL PROTECTED] ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [PERFORM] achat borne wifi
Matthieu == Matthieu Compin [EMAIL PROTECTED] writes: Matthieu bonjour à tous! J'avais émis l'idée d'acheteer une borne Matthieu wifi pour nos futurs manifestation public. Je viens donc Matthieu de discuter pour avoir qq infos et des prix. Je pense que c'est un bon investissement. +1 donc -- Laurent Martellivice-président de Parinux http://www.bearteam.org/~laurent/ http://www.parinux.org/ [EMAIL PROTECTED] ---(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] VidéoProj - RMLL
Laurent == Laurent Rathle [EMAIL PROTECTED] writes: Laurent Le lundi 28 Juin 2004 10:14, Sylvain Lhullier a écrit : Pour info, le vidéo-proj de Parinux devrait rejoindre les RMLL (sauf opposition du CA). Nous sommes en train de trouver un moyen de l'y apporter (sachant que je détiens ce précieux vidéo-proj et que je ne vais pas aux RMLL). Laurent Je n'y vois pas d'opposition, sauf quà mon avis, il ne sera Laurent pas assuré. Il a été acheté d'occasion sans facture et je Laurent vois mal une assurance accepter de rembourser un appareil Laurent sans cet élément. De mémoire, le gars nous avait filé la facture lorsqu'il nous l'a vendu. -- Laurent Martellivice-président de Parinux http://www.bearteam.org/~laurent/ http://www.parinux.org/ [EMAIL PROTECTED] ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [PERFORM] Pas la samedi
Laurent == Laurent Rathle [EMAIL PROTECTED] writes: Laurent Le mardi 22 Juin 2004 19:59, Giancarlo a écrit : 1ere règle: il n'y a pas de CVS chez Parinux 2eme règle: il n'y a pas de CVS chez Parinux, et... 3eme règle: il n'y a pas de CVS chez Parinux ;-) Bon en fait c plus compliqué que ca, pour résumer: c'était trop le foutoir dans l'arbo du site, il faudrait faire du ménage (afin notement de pouvoir faire des modules) Laurent Et subversion ? Puisqu'on a pas d'existant sous CVS à migrer, je suis aussi plutôt commencer directement avec subversion, qui est un CVS en mieux. -- Laurent Martellivice-président de Parinux http://www.bearteam.org/~laurent/ http://www.parinux.org/ [EMAIL PROTECTED] ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [PERFORM] Traduc Party
Matthieu == Matthieu Compin [EMAIL PROTECTED] writes: [...] mais je réitère ma proposition de m'occuper de la partie qui a posé problème cette fois-ci, c'est à dire la prise de contact avec les différentes personnes qui pourraient être intéressées. Matthieu La balle est dans ton camps. Prend contact avec Les Matthieu Projets Importants, fixe moi une date et je te trouve des Matthieu salles et du réseau. Matthieu On a donc la possibilité de faire une belle grossse Matthieu manifestation maitenant et tu peux pas dire non ;) Ouf! Je suis soulagé de la tournure que ça prends. -- Laurent Martellivice-président de Parinux http://www.bearteam.org/~laurent/ http://www.parinux.org/ [EMAIL PROTECTED] ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [PERFORM] Traduc Party
How in hell did could this mail be sent to pgsql-performance ??? I must have inadvertently hit a fatal and obscure keystroke in Emacs/Gnus. Sorry for the noise. -- Laurent Martelli [EMAIL PROTECTED]Java Aspect Components http://www.aopsys.com/ http://jac.objectweb.org ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
[PERFORM] Query involving views
..11.319 rows=0 loops=1) - Hash Join (cost=110.26..291.43 rows=285 width=101) (actual time=5.919..10.961 rows=274 loops=1) Hash Cond: (outer.pictureid = inner.pictureid) - Seq Scan on pictures (cost=0.00..68.33 rows=2933 width=97) (actual time=0.004..2.258 rows=2933 loops=1) - Hash (cost=109.55..109.55 rows=285 width=4) (actual time=5.700..5.700 rows=0 loops=1) - Seq Scan on keywords k (cost=0.00..109.55 rows=285 width=4) (actual time=0.029..5.471 rows=274 loops=1) Filter: ((value)::text = 'laurent'::text) - Hash (cost=5.19..5.19 rows=12 width=8) (actual time=0.198..0.198 rows=0 loops=1) - Seq Scan on groupsdef (cost=0.00..5.19 rows=12 width=8) (actual time=0.031..0.178 rows=11 loops=1) Filter: (userid = 2) Total runtime: 35.657 ms -- Laurent Martelli [EMAIL PROTECTED]Java Aspect Components http://www.aopsys.com/ http://jac.objectweb.org ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
[PERFORM] Unused table of view
) Merge Cond: (outer.groupid = inner.groupid) - Sort (cost=10.16..10.19 rows=12 width=8) (actual time=0.080..0.088 rows=11 loops=1) Sort Key: groupsdef.groupid - Index Scan using groupsdef_userid_key on groupsdef (cost=0.00..9.94 rows=12 width=8) (actual time=0.038..0.056 rows=11 loops=1) Index Cond: (userid = 2) - Index Scan using permissions_groupid_key on permissions (cost=0.00..279.63 rows=8305 width=8) (actual time=0.015..9.801 rows=7633 loops=1) - Sort (cost=100.28..100.37 rows=38 width=4) (actual time=0.114..0.118 rows=8 loops=1) Sort Key: topicscontent.pictureid - Index Scan using topicscontent_topicid on topicscontent (cost=0.00..99.28 rows=38 width=4) (actual time=0.052..0.072 rows=8 loops=1) Index Cond: (topicid = 137) Total runtime: 91.096 ms QUERY PLAN for SELECT count(*) FROM userpictures JOIN topicscontent using(PictureID) WHERE TopicID=137 and UserID=2; Aggregate (cost=859.09..859.09 rows=1 width=4) (actual time=30.488..30.489 rows=1 loops=1) - Merge Join (cost=759.99..858.92 rows=66 width=4) (actual time=27.845..30.466 rows=8 loops=1) Merge Cond: (outer.pictureid = inner.pictureid) - Subquery Scan userpictures2 (cost=659.71..745.41 rows=4897 width=4) (actual time=27.707..29.853 rows=841 loops=1) - Unique (cost=659.71..696.44 rows=4897 width=8) (actual time=27.701..29.121 rows=841 loops=1) - Sort (cost=659.71..671.95 rows=4897 width=8) (actual time=27.696..28.153 rows=1433 loops=1) Sort Key: permissions.pictureid, groupsdef.userid - Merge Join (cost=10.16..359.58 rows=4897 width=8) (actual time=0.101..20.682 rows=5076 loops=1) Merge Cond: (outer.groupid = inner.groupid) - Sort (cost=10.16..10.19 rows=12 width=8) (actual time=0.074..0.078 rows=11 loops=1) Sort Key: groupsdef.groupid - Index Scan using groupsdef_userid_key on groupsdef (cost=0.00..9.94 rows=12 width=8) (actual time=0.035..0.055 rows=11 loops=1) Index Cond: (userid = 2) - Index Scan using permissions_groupid_key on permissions (cost=0.00..279.63 rows=8305 width=8) (actual time=0.014..10.093 rows=7633 loops=1) - Sort (cost=100.28..100.37 rows=38 width=4) (actual time=0.091..0.094 rows=8 loops=1) Sort Key: topicscontent.pictureid - Index Scan using topicscontent_topicid on topicscontent (cost=0.00..99.28 rows=38 width=4) (actual time=0.039..0.057 rows=8 loops=1) Index Cond: (topicid = 137) Total runtime: 31.376 ms -- Laurent Martelli [EMAIL PROTECTED]Java Aspect Components http://www.aopsys.com/ http://jac.objectweb.org ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [PERFORM] Unused table of view
Tom == Tom Lane [EMAIL PROTECTED] writes: Tom Laurent Martelli [EMAIL PROTECTED] writes: The pictures table is scanned, but it's not needed. Tom Yes it is. For example, if pictures is empty then the view Tom yields zero rows. Omitting the join to pictures could give a Tom different result. Since Permission is like this: CREATE TABLE permissions ( GroupID integer NOT NULL REFERENCES groups ON DELETE cascade, PictureID integer NOT NULL REFERENCES pictures ON DELETE cascade, UNIQUE (GroupID, PictureID)); if the pictures table is empty, so is permissions, because permissions.PictureID references pictures. -- Laurent Martelli [EMAIL PROTECTED]Java Aspect Components http://www.aopsys.com/ http://jac.objectweb.org ---(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] Join on incompatible types
Shridhar == Shridhar Daithankar [EMAIL PROTECTED] writes: Shridhar Laurent Martelli wrote: [...] Should I understand that a join on incompatible types (such as integer and varchar) may lead to bad performances ? Shridhar Conversely, you should enforce strict type compatibility Shridhar in comparisons for getting any good plans..:-) Ha ha, now I understand why a query of mine was so sluggish. Is there a chance I could achieve the good perfs without having he same types ? I've tried a CAST in the query, but it's even a little worse than without it. However, using a view to cast integers into varchar gives acceptable results (see at the end). I'm using Postgresql 7.3.4. iprofil-jac=# EXPLAIN ANALYZE SELECT * from classes where exists (select value from lists where lists.id='16' and lists.value=classes.id); QUERY PLAN Seq Scan on classes (cost=0.00..5480289.75 rows=9610 width=25) (actual time=31.68..7321.56 rows=146 loops=1) Filter: (subplan) SubPlan - Index Scan using lists_id on lists (cost=0.00..285.12 rows=1 width=8) (actual time=0.38..0.38 rows=0 loops=19220) Index Cond: (id = 16) Filter: ((value)::text = ($0)::text) Total runtime: 7321.72 msec iprofil-jac=# EXPLAIN ANALYZE SELECT * from classes2 where exists (select value from lists where lists.id='16' and lists.value=classes2.id); QUERY PLAN - Seq Scan on classes2 (cost=0.00..5923.87 rows=500 width=64) (actual time=0.76..148.20 rows=146 loops=1) Filter: (subplan) SubPlan - Index Scan using lists_value on lists (cost=0.00..5.90 rows=1 width=8) (actual time=0.01..0.01 rows=0 loops=19220) Index Cond: ((id = 16) AND (value = $0)) Total runtime: 148.34 msec -- -- Tables classes and classes2 are populated with the same data, they -- only differ on the type of the id column. -- iprofil-jac=# \d classes Table public.classes Colonne | Type| Modifications -+---+--- id | integer | not null classid | character varying | Index: classes_pkey primary key btree (id) iprofil-jac=# \d classes2 Table public.classes2 Colonne | Type| Modifications -+---+--- id | character varying | not null classid | character varying | Index: classes2_pkey primary key btree (id) iprofil-jac=# \d lists Table public.lists Colonne | Type| Modifications -+---+--- id | integer | not null index | integer | not null value | character varying | Index: lists_index unique btree (id, index), lists_id btree (id), lists_value btree (id, value) -- -- IT'S EVEN BETTER WITH A JOIN -- iprofil-jac=# EXPLAIN ANALYZE SELECT * from lists join classes on classes.id=lists.value where lists.id='16'; QUERY PLAN --- Nested Loop (cost=0.00..90905.88 rows=298 width=41) (actual time=53.93..9327.87 rows=146 loops=1) Join Filter: ((inner.id)::text = (outer.value)::text) - Seq Scan on lists (cost=0.00..263.43 rows=146 width=16) (actual time=8.38..9.70 rows=146 loops=1) Filter: (id = 16) - Seq Scan on classes (cost=0.00..333.20 rows=19220 width=25) (actual time=0.00..28.45 rows=19220 loops=146) Total runtime: 9328.35 msec iprofil-jac=# EXPLAIN ANALYZE SELECT * from lists join classes2 on classes2.id=lists.value where lists.id='16'; QUERY PLAN -- Merge Join (cost=268.67..324.09 rows=16 width=80) (actual time=9.59..65.55 rows=146 loops=1) Merge Cond: (outer.id = inner.value) - Index Scan using classes2_pkey on classes2 (cost=0.00..52.00 rows=1000 width=64) (actual time=0.03..40.83 rows=18778 loops=1) - Sort (cost=268.67..269.03 rows=146 width=16) (actual time=9.50..9.56 rows=146 loops=1) Sort Key: lists.value - Seq Scan on lists (cost=0.00..263.43 rows=146 width=16) (actual time=8.83..9.17 rows=146 loops=1) Filter: (id = 16) Total runtime
