Received: from maia.hub.org (unknown [200.46.204.183]) by mail.postgresql.org (Postfix) with ESMTP id 372A2635988 for ; Wed, 10 Mar 2010 05:07:54 -0400 (AST) Received: from mail.postgresql.org ([200.46.204.86]) by maia.hub.org (mx1.hub.org [200.46.204.183]) (amavisd-maia, port 10024) with ESMTP id 79913-03 for ; Wed, 10 Mar 2010 09:07:43 +0000 (UTC) X-Greylist: domain auto-whitelisted by SQLgrey-1.7.6 Received: from mail-bw0-f216.google.com (mail-bw0-f216.google.com [209.85.218.216]) by mail.postgresql.org (Postfix) with ESMTP id 28E816359AE for ; Wed, 10 Mar 2010 05:07:42 -0400 (AST) Received: by bwz8 with SMTP id 8so4045760bwz.3 for ; Wed, 10 Mar 2010 01:07:41 -0800 (PST) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=gamma; h=domainkey-signature:mime-version:received:date:message-id:subject :from:to:content-type; bh=uDCmtkN6qyDH9lHnXBJK4P4Ln4mtQkEyKE3g9M+BeRU=; b=XfgSDmmVVHSmKLxHhs6KPmJImDrJ55HEbTSDEMgX8EmvcPOSe8cZCIlWnI4y5SIeT5 DTqfCsMOYR9jNXHoLE//Xr/aW52I7mr2xA1rsE7NX/TrdyOorqNfLBKy2riDX85dzbQr 53MyCH1NwJrECpu1Ht3YWrYizs+b3GcJMD9bY= DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=gamma; h=mime-version:date:message-id:subject:from:to:content-type; b=l95dssQnneSbFRTDoYSJLnXzbbJn4B90qDC0Sx7m0vGUiHKe8Usf6LKINAB7hzeyTD rOSC64JZ/hzsi4fQMtgeGSLSsOhf8Cr6KcqG6PbHUkpMEs6fAdpkCYjROOXZbqHSuRAj Jv7o5TEGYlnehcBrm15gRSONacBC6xVusRFLw= MIME-Version: 1.0 Received: by 10.204.10.7 with SMTP id n7mr239813bkn.10.1268212061289; Wed, 10 Mar 2010 01:07:41 -0800 (PST) Date: Wed, 10 Mar 2010 10:07:41 +0100 Message-ID: <13bb64e71003100107w3249218q1b189ef7efbc841@mail.gmail.com> Subject: Strange workaround for slow query From: Kees van Dieren To: pgsql-performance@postgresql.org Content-Type: multipart/alternative; boundary=000325559fd269be4304816ea19d X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=-2.598 tagged_above=-10 required=5 tests=BAYES_00=-2.599, HTML_MESSAGE=0.001 X-Spam-Level: X-Archive-Number: 201003/102 X-Sequence-Number: 37927 --000325559fd269be4304816ea19d Content-Type: text/plain; charset=ISO-8859-1 Hi folks, We have two related tables with event types and events. We query for a join between these two tables and experience that, when there is an to-be-expected very small result set, this query performs particularly poor. Understanding in this matter would be appreciated. SELECT * from events_event_types WHERE id IN (71,999); id | name | severity ----+------------------------+---------- 71 | Xenteo Payment handled | 20 (1 row) Following original query returns zero rows (as to be expected on what I showed above) and takes (relatively) a lot of time doing so: SELECT * FROM events_events LEFT OUTER JOIN events_event_types ON eventType_id=events_event_types.id WHERE severity=70 AND (eventType_id IN (71)) ORDER BY datetime DESC LIMIT 50; id | carparkid | cleared | datetime | identity | generatedbystationid | eventtype_id | relatedstationid | processingstatus | id | name | severity ----+-----------+---------+--- -------+----------+----------------------+--------------+------------------+------------------+----+------+---------- (0 rows) Time: 397.564 ms Following query is much alike the original query, but I changed the "WHERE severity". It returns the number of rows are requested in LIMIT and takes only little time doing so: SELECT * FROM events_events LEFT OUTER JOIN events_event_types ON eventType_id=events_event_types.id WHERE severity=20 AND (eventType_id IN (71)) ORDER BY datetime DESC limit 50; ... (50 rows) Time: 1.604 ms The latter much to prove that this is a problem related to small result sets. Following query is much alike the original query, although I've added a dummy value (non-existent in event types table; "999") to the WHERE IN clause. It returns the same zero rows and takes only little time doing so: SELECT * FROM events_events LEFT OUTER JOIN events_event_types ON eventType_id=events_event_types.id WHERE severity=70 AND (eventType_id IN (71, 999)) ORDER BY datetime DESC LIMIT 50; id | carparkid | cleared | datetime | identity | generatedbystationid | eventtype_id | relatedstationid | processingstatus | id | name | severity ----+-----------+---------+----------+----------+----------------------+--------------+------------------+------------------+----+------+---------- (0 rows) Time: 1.340 ms Now I have at least two possibilities: - Implementing the dummy value as shown above in my source code to improve query performance (dirty but effective) - Further investigating what is going on, which at this point is something I need help with Thanks for your assistance in this matter! Following are a number of details to describe the environment that this is seen in. SELECT version(); PostgreSQL 8.3.7 on i486-pc-linux-gnu, compiled by GCC cc (GCC) 4.2.4 (Ubuntu 4.2.4-1ubuntu3) Postgres was installed as Debian package in Ubuntu 8.04 LTS. SELECT count(*) FROM events_events; 7619991 SELECT count(*) FROM events_events WHERE eventtype_id=71; 50348 SELECT count(*) FROM events_event_types; 82 \d events_event_types Table "public.events_event_types" Column | Type | Modifiers ----------+------------------------+----------------------------------------------------------------- id | bigint | not null default nextval ('events_event_types_id_seq'::regclass) name | character varying(255) | not null severity | bigint | not null Indexes: "events_event_types_pkey" PRIMARY KEY, btree (id) "events_event_types_name_key" UNIQUE, btree (name) "events_event_types_severity_ind" btree (severity) "test_events_eventtypes_id_severity_ind" btree (id, severity) "test_events_eventtypes_severity_id_ind" btree (severity, id) \d events_events Table "public.events_events" Column | Type | Modifiers ----------------------+--------------------------+------------------------------------------------------------ id | bigint | not null default nextval ('events_events_id_seq'::regclass) carparkid | bigint | cleared | boolean | not null datetime | timestamp with time zone | identity | character varying(255) | generatedbystationid | bigint | eventtype_id | bigint | not null relatedstationid | bigint | processingstatus | character varying(255) | not null Indexes: "events_events_pkey" PRIMARY KEY, btree (id) "events_events_cleared_ind" btree (cleared) "events_events_datetime_eventtype_id_ind" btree (datetime, eventtype_id) "events_events_datetime_ind" btree (datetime) "events_events_eventtype_id_datetime_ind" btree (eventtype_id, datetime) "events_events_eventtype_id_ind" btree (eventtype_id) "events_events_identity_ind" btree (identity) "events_events_not_cleared_ind" btree (cleared) WHERE NOT cleared "events_events_processingstatus_new" btree (processingstatus) WHERE processingstatus::text = 'NEW'::text "test2_events_events_eventtype_id_severity_ind" btree (datetime, eventtype_id, cleared) "test3_events_events_eventtype_id_severity_ind" btree (cleared, datetime, eventtype_id) "test4_events_events_eventtype_id_severity_ind" btree (datetime, cleared, eventtype_id) "test5_events_events_eventtype_id_severity_ind" btree (datetime, cleared) "test_events_events_eventtype_id_severity_ind" btree (eventtype_id, cleared) Foreign-key constraints: "fk88fe3effa0559276" FOREIGN KEY (eventtype_id) REFERENCES events_event_types(id) Can someone explain this behaviour? Thanks in advance! Best regards, -- Squins | IT, Honestly Oranjestraat 23 2983 HL Ridderkerk The Netherlands Phone: +31 (0)180 414520 Mobile: +31 (0)6 30413841 www.squins.com http://twitter.com/keesvandieren Chamber of commerce Rotterdam: 22048547 --000325559fd269be4304816ea19d Content-Type: text/html; charset=ISO-8859-1 Content-Transfer-Encoding: quoted-printable Hi folks,


We have two related tables with event types and events. We query for a join=
between these two tables and experience that, when there is an
to-be-expected very small result set, this query performs particularly
poor. Understanding in this matter would be appreciated.

SELECT * from events_event_types WHERE id IN (71,999);
=A0id | =A0 =A0 =A0 =A0 =A0name =A0 =A0 =A0 =A0 =A0| severity
----+------------------------+----------
=A071 | Xenteo Payment handled | =A0 =A0 =A0 20
(1 row)


Following original query returns zero rows (as to be expected on what I
showed above) and takes (relatively) a lot of time doing so:

SELECT * FROM events_events LEFT OUTER JOIN events_event_types ON
eventType_id=3D= events_event_types.id WHERE severity=3D70 AND (eventType_id IN
(71)) ORDER BY datetime DESC LIMIT 50;
=A0id | carparkid | cleared | datetime | identity | generatedbystationid |<= br> eventtype_id | relatedstationid | processingstatus | id | name | severity ----+-----------+---------+---
-------+----------+---------------------= -+--------------+------------------+------------------+----+------+--------= --
(0 rows)
Time: 397.564 ms

Following query is much alike the original query, but I changed the "W= HERE
severity". It returns the number of rows are requested in LIMIT and ta= kes
only little time doing so:

SELECT * FROM events_events LEFT OUTER JOIN events_event_types ON
eventType_id=3D= events_event_types.id WHERE severity=3D20 AND (eventType_id IN
(71)) ORDER BY datetime DESC limit 50;
...
(50 rows)
Time: 1.604 ms

The latter much to prove that this is a problem related to small result
sets.

Following query is much alike the original query, although I've added a=
dummy value (non-existent in event types table; "999") to the WHE= RE IN
clause. It returns the same zero rows and takes only little time doing so:<= br>
SELECT * FROM events_events LEFT OUTER JOIN events_event_types ON
eventType_id=3D= events_event_types.id WHERE severity=3D70 AND (eventType_id IN
(71, 999)) ORDER BY datetime DESC LIMIT 50;
=A0id | carparkid | cleared | datetime | identity | generatedbystationid |<= br> eventtype_id | relatedstationid | processingstatus | id | name | severity ----+-----------+---------+----------+----------+----------------------+---= -----------+------------------+------------------+----+------+---------- (0 rows)
Time: 1.340 ms

Now I have at least two possibilities:
- Implementing the dummy value as shown above in my source code to improve<= br> query performance (dirty but effective)
- Further investigating what is going on, which at this point is something<= br> I need help with
Thanks for your assistance in this matter!


Following are a number of details to describe the environment that this is<= br> seen in.

SELECT version();
PostgreSQL 8.3.7 on i486-pc-linux-gnu, compiled by GCC cc (GCC) 4.2.4
(Ubuntu 4.2.4-1ubuntu3)

Postgres was installed as Debian package in Ubuntu 8.04 LTS.

SELECT count(*) FROM events_events;
7619991
SELECT count(*) FROM events_events WHERE eventtype_id=3D71;
50348
SELECT count(*) FROM events_event_types;
82

\d events_event_types
=A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0Table &= quot;public.events_event_types"
=A0Column =A0| =A0 =A0 =A0 =A0 =A0Type =A0 =A0 =A0 =A0 =A0| =A0 =A0 =A0 = =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0Modifiers
----------+------------------------+---------------------------------------= --------------------------
=A0id =A0 =A0 =A0 | bigint =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 | not null defau= lt nextval
('events_event_types_id_seq'::regclass)
=A0name =A0 =A0 | character varying(255) | not null
=A0severity | bigint =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 | not null
Indexes:
=A0 =A0"events_event_types_pkey" PRIMARY KEY, btree (id)
=A0 =A0"events_event_types_name_key" UNIQUE, btree (name)
=A0 =A0"events_event_types_severity_ind" btree (severity)
=A0 =A0"test_events_eventtypes_id_severity_ind" btree (id, sever= ity)
=A0 =A0"test_events_eventtypes_severity_id_ind" btree (severity,= id)

\d events_events
=A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 = =A0 =A0 Table "public.events_events"
=A0 =A0 =A0 =A0Column =A0 =A0 =A0 =A0| =A0 =A0 =A0 =A0 =A0 Type =A0 =A0 = =A0 =A0 =A0 |
Modifiers
----------------------+--------------------------+-------------------------= -----------------------------------
=A0id =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 | bigint =A0 =A0 =A0 =A0 =A0 =A0 = =A0 =A0 =A0 | not null default nextval
('events_events_id_seq'::regclass)
=A0carparkid =A0 =A0 =A0 =A0 =A0 =A0| bigint =A0 =A0 =A0 =A0 =A0 =A0 =A0 = =A0 =A0 |
=A0cleared =A0 =A0 =A0 =A0 =A0 =A0 =A0| boolean =A0 =A0 =A0 =A0 =A0 =A0 =A0= =A0 =A0| not null
=A0datetime =A0 =A0 =A0 =A0 =A0 =A0 | timestamp with time zone |
=A0identity =A0 =A0 =A0 =A0 =A0 =A0 | character varying(255) =A0 |
=A0generatedbystationid | bigint =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 |
=A0eventtype_id =A0 =A0 =A0 =A0 | bigint =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 = =A0 | not null
=A0relatedstationid =A0 =A0 | bigint =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 |<= br> =A0processingstatus =A0 =A0 | character varying(255) =A0 | not null
Indexes:
=A0 =A0"events_events_pkey" PRIMARY KEY, btree (id)
=A0 =A0"events_events_cleared_ind" btree (cleared)
=A0 =A0"events_events_datetime_eventtype_id_ind" btree (datetime= ,
eventtype_id)
=A0 =A0"events_events_datetime_ind" btree (datetime)
=A0 =A0"events_events_eventtype_id_datetime_ind" btree (eventtyp= e_id,
datetime)
=A0 =A0"events_events_eventtype_id_ind" btree (eventtype_id)
=A0 =A0"events_events_identity_ind" btree (identity)
=A0 =A0"events_events_not_cleared_ind" btree (cleared) WHERE NOT= cleared
=A0 =A0"events_events_processingstatus_new" btree (processingsta= tus) WHERE
processingstatus::text =3D 'NEW'::text
=A0 =A0"test2_events_events_eventtype_id_severity_ind" btree (da= tetime,
eventtype_id, cleared)
=A0 =A0"test3_events_events_eventtype_id_severity_ind" btree (cl= eared,
datetime, eventtype_id)
=A0 =A0"test4_events_events_eventtype_id_severity_ind" btree (da= tetime,
cleared, eventtype_id)
=A0 =A0"test5_events_events_eventtype_id_severity_ind" btree (da= tetime,
cleared)
=A0 =A0"test_events_events_eventtype_id_severity_ind" btree (eve= nttype_id,
cleared)
Foreign-key constraints:
=A0 =A0"fk88fe3effa0559276" FOREIGN KEY (eventtype_id) REFERENCE= S
events_event_types(id)

Can someone explain this behaviour?

Th= anks in advance!

Best regards,


--
Squ= ins | IT, Honestly
Oranjestraat 23
2983 HL Ridderkerk
The Netherla= nds
Phone: +31 (0)180 414520
Mobile: +31 (0)6 30413841
www.squins.com
http://twitter.com/keesvandieren
Chamber of commerce Rotterd= am: 22048547
--000325559fd269be4304816ea19d--