Databases
StackOverflow2013
Postsdbo
POSelect records with IDs containing in another table
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POSelect records with IDs containing in another table
    primarykey
    data
    text
    <pre><code>vit=# select count(*) from evtags; count --------- 4496914 vit=# explain select tag from evtags where evid in (1002, 1023); QUERY PLAN --------------------------------------------------------------------------------- Index Only Scan using evtags_pkey on evtags (cost=0.00..15.64 rows=12 width=7) Index Cond: (evid = ANY ('{1002,1023}'::integer[])) </code></pre> <p>This seems completely ok so far. Next, I want to use IDs from another table instead of specifying them in the query.</p> <pre><code>vit=# select count(*) from zzz; count ------- 49738 </code></pre> <p>Here we go...</p> <pre><code>vit=# explain select tag from evtags where evid in (select evid from zzz); QUERY PLAN ----------------------------------------------------------------------- Hash Semi Join (cost=1535.11..142452.47 rows=291712 width=7) Hash Cond: (evtags.evid = zzz.evid) -&gt; Seq Scan on evtags (cost=0.00..69283.14 rows=4496914 width=11) -&gt; Hash (cost=718.38..718.38 rows=49738 width=4) -&gt; Seq Scan on zzz (cost=0.00..718.38 rows=49738 width=4) </code></pre> <p>Why index scan on the much more larger table and what's the correct way to do this?</p> <p><strong>EDIT</strong></p> <p>I recreated my <code>zzz</code> table and now it is better for some reason:</p> <pre><code>vit=# explain analyze select tag from evtags where evid in (select evid from zzz); QUERY PLAN ------------------------------------------------------------------------------------------------------------------------------------ Nested Loop (cost=708.00..2699.17 rows=2248457 width=7) (actual time=28.935..805.923 rows=244353 loops=1) -&gt; HashAggregate (cost=708.00..710.00 rows=200 width=4) (actual time=28.893..54.461 rows=38822 loops=1) -&gt; Seq Scan on zzz (cost=0.00..601.80 rows=42480 width=4) (actual time=0.032..10.985 rows=40000 loops=1) -&gt; Index Only Scan using evtags_pkey on evtags (cost=0.00..9.89 rows=6 width=11) (actual time=0.015..0.017 rows=6 loops=38822) Index Cond: (evid = zzz.evid) Heap Fetches: 0 Total runtime: 825.651 ms </code></pre> <p>But after several executions it changes to</p> <pre><code>vit=# explain analyze select tag from evtags where evid in (select evid from zzz); QUERY PLAN --------------------------------------------------------------------------------------------------------------------------------------------------- Merge Semi Join (cost=4184.11..127258.48 rows=235512 width=7) (actual time=38.269..1461.755 rows=244353 loops=1) Merge Cond: (evtags.evid = zzz.evid) -&gt; Index Only Scan using evtags_pkey on evtags (cost=0.00..136736.89 rows=4496914 width=11) (actual time=0.038..899.647 rows=3630070 loops=1) Heap Fetches: 0 -&gt; Materialize (cost=4184.04..4384.04 rows=40000 width=4) (actual time=38.212..61.038 rows=40000 loops=1) -&gt; Sort (cost=4184.04..4284.04 rows=40000 width=4) (actual time=38.208..51.104 rows=40000 loops=1) Sort Key: zzz.evid Sort Method: external sort Disk: 552kB -&gt; Seq Scan on zzz (cost=0.00..577.00 rows=40000 width=4) (actual time=0.018..8.833 rows=40000 loops=1) Total runtime: 1484.293 ms </code></pre> <p>...Which is actually slower. Is there any way to hint it a 'correct' execution plan?</p> <p>The point of these operations is that I want to perform number of queries on a subset of my data and wanted to use separate temporary table to hold IDs of records I want to process.</p>
    singulars
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PTQuestion
    1. USmifki
    1. USmifki
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PO
      singulars
      1. PTAnswer
    1. This table or related slice is empty.
    1. CODo your tables have primary/foreign keys (there is no index scan, there could be two) Do you have valid statistics ? Please add the table definitions, including PK/FK and/or indexes. BTW: this kind of plan is normal if the result set is small and most of the tuples are needed. BTW2: `explain analyze` will show you *both* the estimated and measured counts/numbers.
      singulars
      1. POSelect records with IDs containing in another table
      1. USwildplasser
    2. COUpdated my question.
      singulars
      1. POSelect records with IDs containing in another table
      1. USmifki
    3. CODid you run `vacuum analyze` after recreating the `zzz` table? (it will update the statistics) Also: the "outer" table needs about 50% of the rows, an index might not help in finding them (since basically every disk page will be needed anyway). You *could* try lowering random_page_cost to 1.5 (or work_mem to a smaller value) to force an index scan, but that will only make sense when fewer rows/pages need to be fetched. BTW: Does the zzz table have PK/FK/indices? **please add the table definitions** , including PK/FK/indexes.
      singulars
      1. POSelect records with IDs containing in another table
      1. USwildplasser
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload