Why is one of these queries so slow?

Question

When I run the following 4 queries in Postgres 9.1, I get very cryptic results. Queries 1, 2, and 4 behave as expected, quickly returning results, but query 3 takes significantly longer.

plant_data is a table with ~ 67 million records and is indexed by btree indices in all expected ways.

-- Query 1
SELECT COUNT(*) 
FROM plant_data 
WHERE sensor_id IN (
    SELECT name FROM nomenclature 
    WHERE resolution = '1DAY' LIMIT 1 OFFSET 0)

Uses an index, works at <14ms (returns 0) http://explain.depesz.com/s/dx9

-- Query 2
SELECT COUNT(*) 
FROM plant_data 
WHERE sensor_id IN (
    SELECT name FROM nomenclature 
    WHERE resolution = '1DAY' LIMIT 1 OFFSET 1)

Also uses an index, works in <14 ms (returns 29) http://explain.depesz.com/s/Zfl

-- Query 3
SELECT COUNT(*) 
FROM plant_data 
WHERE sensor_id IN (
    SELECT name FROM nomenclature 
    WHERE resolution = '1DAY' LIMIT 2)

Is there any after. scanning, performed in 261 seconds (returns 29) http://explain.depesz.com/s/Xui

-- Query 4
select count(*) 
FROM plant_data 
WHERE sensor_id IN (
   'BUR_PCLo_SAMPww_C_COD_1DAY',   -- First 2 results in
   'BUR_ANDIi1_FLOraw_I_VOL_1DAY') -- nomenclature table

, < 14 , ( 29) http://explain.depesz.com/s/iQc

: 3 ?

---

. , 4 ... , , , 2800 sensor_id. - 96 sensor_id. 12 sensor_id, - 1. ~ 50 1, ~ 50 2, ~ 50 3, 4, . 4 ( , ).

, , , , 67 .

---

:

CREATE TABLE plant_data
(
  id serial NOT NULL,
  date timestamp without time zone,
  sensor_id text,
  value double precision,
  day_id integer,
  week_id integer,
  month_id integer,
  year_id integer,
resolution integer 
)
WITH (
  OIDS=FALSE
);
ALTER TABLE plant_data
  OWNER TO inners_data;

CREATE INDEX plant_data_date_index
  ON plant_data
  USING btree
  (date);

CREATE INDEX plant_data_day_id
  ON plant_data
  USING btree
  (day_id);

CREATE INDEX plant_data_month_id
  ON plant_data
  USING btree
  (month_id);

CREATE INDEX plant_data_resolution_idx
  ON plant_data
  USING btree
  (resolution);

CREATE INDEX plant_data_sensor_id_date_index
  ON plant_data
  USING btree
  (date, sensor_id COLLATE pg_catalog."default");

CREATE INDEX plant_data_sensor_id_index
  ON plant_data
  USING btree
  (sensor_id COLLATE pg_catalog."default");

CREATE INDEX plant_data_week_id
  ON plant_data
  USING btree
  (week_id);

CREATE INDEX plant_data_year_id
  ON plant_data
  USING btree
  (year_id);

: , , ,

Answer 1

.

?

-- Query 1
SELECT COUNT(*) 
FROM plant_data 
WHERE exists (SELECT 1 FROM nomenclature 
              WHERE resolution = '1DAY' and sensor_id = name LIMIT 2
             );

-, Postgres in . , . (, , ), Postgres , , , . , :

SELECT COUNT(*) 
FROM plant_data 
WHERE sensor_id = (
    SELECT name FROM nomenclature 
    WHERE resolution = '1DAY' LIMIT 1 OFFSET 0)

, , Postgres , , , . ?

-- Query 2
with list as (
    SELECT name FROM nomenclature 
    WHERE resolution = '1DAY' LIMIT 2
   )
SELECT COUNT(*) 
FROM plant_data 
WHERE sensor_id IN (select name from list);

EDIT:

, . limit 1 Postgres , , . . , resolution.

limit 2 Postgres "--". , . , , . Arrrg!

, Postgres , .

, Postgres .

Answer 2

, . , . ( )

, 700 000 , 0 29. , postgres , , , .

sensor_id ? , . , , seq- LIMIT 1.

sensor_id ( ?), , postgresql .

default_statistics_target , . 9.2 9.3 , " ".

, , , 4 .

Answer 3

, PostgreSQL :

WHERE sensor_id IN (
    SELECT name FROM nomenclature 
    WHERE resolution = '1DAY' LIMIT 1 OFFSET 0)

OFFSET , PostgreSQL . ORDER BY, .

, EXISTS:

WHERE EXISTS (
    SELECT 1
    FROM nomenclature n
    WHERE n.resolution = '1DAY'
    AND sensor_id = n.name)

A detailed commentary on the reasons for the behavior, I think you will need to post the pgsql-performance mailing list. I'm currently working on query planner code, but not with optimization logic, and only someone who really knows the optimizer can probably really answer that well.